How to: Connect XLSM file to an XLSX file (1 Viewer)

[Bilbo_Baggins_Esq]

I have another thread here:
http://www.access-programmers.co.uk/forums/showthread.php?t=254429

Please excuse me if this seems to be a duplicate thread, but I fear the other one may be taken too literally as being specific to the connection method there described by the existing VBA provided.

The reality is, I really do not care how the two files get connected.
The other thread shows my attempt to set up an ACE.OLEDB connection, but again, I really do not care.

Scenario:
One XLSM open in Excel 2010 needs to connect to a different XLSX file (either open or closed).
The goal (perhaps a fantasy) is to be able to pull recordsets with SQL similar to what one would be able to accomplish with an Access data connection.


I've been told before JET only works for older versions of Access (and Excel) and I can find some sample conenction strings for ACE to use in Excel.
I have successfully used ACE.12.0 in Access, connecting to Access.
but I've never attempted connecting Excel to Excel.

So far, nothing seems to work.

Certainly, I would rather use Access, but unfortunately, that is not an option for this scenario.

So, is there anybody here that can help me out?

Thanks in advance

 

[pr2-eugin]

Okay so you are not doing this connection from Access? You are using from within Excel. Okay.

Try adding references,
Microsoft Access 14.0 Object Library
Microsoft ActiveX Data Objects 6.1 Library

 

[Bilbo_Baggins_Esq]

sigh...

Yep, already tried that too.
no joy.
same error...

(note: Microsoft ActiveX Data Objects 6.0 Library)

 

[pr2-eugin]

Bilbo_Baggins_Esq, I tried using XLSX files with the connection string bumped into the same error.. Did a bit of searching, Pat talks about this issue..

The ISAM drivers didn't get installed with Access and 64-bit Access may not even support them. I think O2013 is dropping support for dBase, which is an "Installable ISAM" file type. Perhaps, MS dropped it in O2010 for the 64-bit version.

Then this seems to be a possible solution : Resolving "Could not find installable ISAM" Error, although I have not tried it.

Hope this helps !

 

[Bilbo_Baggins_Esq]

Well, I tried it and again, no joy. Same exact error.

I'll find it hard to believe if this is not possible.

Is anybody willing to try and create a pair of files and see if they can make it work?

In the interest of full disclosure, I was able to record a macro in the XLSM that does seem to work.
It seems to use ODBC.
I have a ton of expereince in recording a macro and then parsing it out for real applicabilty to other tasks.
HOWEVER, the recorded code is a miss-mash of jumbled and broken string parts that I have not yet been able to make sense of.
Additionally, probably because of the miss-mash layout of the string parts, i have not yet been able to figure out a way to inject my variables into the SQL.

My expereince with ADO and OLEDB (albeit within Access) has shown me it is much easier and far cleaner than what I see in the recorded ODBC string.

PLEASE HELP ME!
I'm thinking this might be a great tread to have a solid ACE.OLEDB solution for.

 

[Bilbo_Baggins_Esq]

I was able to figure out how to inject a variable into the WHERE clause of this ODBC connection/query

This seems to build the connection and query all into the same string, but I stepped through it and it does work.

any ideas how to sort this mis-mash out into inteligable sections.
It almost looks as if the lines were broken alternating (notice the word "Driver" split in two).

ideally, I'd want to seperate the connection and open it.
Then be able to run different queries with the opened connection.

Sub Macro2()
'
' Macro2 Macro
'

'
Dim AgentInitials As String

AgentInitials = "BF"

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DBQ=C:\DataTesting\DataSource.xlsx;DefaultDir=C:\DataTesting;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dr" _
        ), Array( _
        "iverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=16;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;Us" _
        ), Array("erCommitSync=Yes;")), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT `Sheet1$`.Agent, `Sheet1$`.Column1, `Sheet1$`.Column2, `Sheet1$`.Column3, `Sheet1$`.Column4, `Sheet1$`.Column5, `Sheet1$`.Column6, `Sheet1$`.Column7, `Sheet1$`.Column8, `Sheet1$`.Column9, `Shee" _
        , _
        "t1$`.Column10" & Chr(13) & "" & Chr(10) & "FROM `C:\DataTesting\DataSource.xlsx`.`Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Sheet1$`.Agent='" & AgentInitials & "')" & Chr(13) & "" & Chr(10) & "ORDER BY `Sheet1$`.Column1" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_data2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Bilbo_Baggins_Esq]

Ok, well, I am growing dubious of the OCDB code shown above.
Yes, it works as recorded, however, after I have now parsed it out for modification and adaptation, itturns out there are some pretty problematic restrictions.
not the least of which is there apparently is a restriction on the length of text for the SQL (.CommandText).
That explains why the recorded code appears to be all jumbled up.

Please guys and gals, I am really in need of some help here with the original quest.

Using ACE.OLEDB to pull a recordset from another workbook.

I've seen on connectionstrings.com there are valid syntax for this, but I throw an error (described above).

Can anybody at least repro the error?
If the code works for you, then there may be a technical problem on my systems (it has been tested on more than one box on my side).

PLEASE?

 

[namliam]

I am just going to reply here instead of your new thread...
It is probably the length of the array that is limited not the number of arrays, I havent tested it but have you tried re-writing your code to something like:

... Source:= _
Array( _
      Array("ODBC;DBQ=C:\DataTesting\DataSource.xlsx;DefaultDir=C:\DataTesting;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};") _
    , Array("DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=16;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;") _
    , Array("UserCommitSync=Yes;")), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _ 
                     Array("SELECT `Sheet1$`.Agent, `Sheet1$`.Column1, `Sheet1$`.Column2, `Sheet1$`.Column3, `Sheet1$`.Column4, `Sheet1$`.Column5, `Sheet1$`.Column6, `Sheet1$`.Column7, `Sheet1$`.Column8, `Sheet1$`.Column9, " _
                         , "`Sheet1$`.Column10" & Chr(13) & "" & Chr(10) & "FROM `C:\DataTesting\DataSource.xlsx`.`Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & "WHERE (`Sheet1$`.Agent='" & AgentInitials & "')" 
               & Chr(13) & "" & Chr(10) & "ORDER BY `Sheet1$`.Column1" _
        )

atleast it solves your "Cutting off" problem, but I think you can just add an Array to add more stuff to your SQL, worse comes to worse you SHOULD be able to shorten your SQL by removing all the "'Sheet1$'. " in the select, where and order by part of the query

 

[Bilbo_Baggins_Esq]

well, thanks for the tips, but, for other reasons too, the ODBC doesn't look like it is going to work for this scenario.

the sample code and sheets I've been working with are just samples.
the actual workbooks are different and contain many other sheet names, most much longer and other items which I did not at first realize would be a problem for the ODBC method.

To be in my own favor, when i first started this quest, i was attempting to use the ACE.OLEDB method.
If I could make that work, the other "factors" would not be applicable and only came into play as I tried to find an alternate method.

What i REALLY NEED is some insights to fixing the problem with ACE.OLEDB.

I strongly beleive this should work, or can be made to work, if I can only just figure it out.

I am pretty frustrated and dismayed I can't find anybody that is even willing to repro it, much less to try and find a solution for it.

If it helps anybody willing, I've attached a pair of files ready to repro/debug.