How to connect Excel 2010 workbook to another XLSX file

Bilbo_Baggins_Esq · Oct 2, 2013

I am trying to get data from one XLSX to an XLSM file.

Running Excel 2010 (x86) on Windows 7 (x64)

I have this connection string:

Code:

Public adoConnection As ADODB.Connection
Sub GetAgentRS()
Set adoConnection = New ADODB.Connection
adoConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                    "data Source= C:\DataTesting\DataSource.xlsx;" & _
                                    "Extended Properties=\Excel 12.0;"
adoConnection.Open
End Sub

The trouble is, at run time when I execute the line "adoConnection.Open" it throws the following error:

Microsoft Visual Basic
Run-time error '-2147467259 (80004005)':
Could not find installable ISAM

When i search Bing for the error number I'm finding nothing applicable to Excel with Excel and when I search for the message all I find has to do with earlier versions of Excel and an older Jet error.

Is there something missing or incorrect about the connection string?

Can somebody please show a sample set of code for VBA to connect one Excel file to another Excel file?

pr2-eugin · Oct 2, 2013

Try..

Code:

    adoConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                        "data Source= C:\DataTesting\DataSource.xlsx;" & _
                                        "Extended Properties=\Excel[COLOR=Red][B] 12.0 Xml;[/B][/COLOR]"

Bilbo_Baggins_Esq · Oct 2, 2013

pr2-eugin,

thanks, but no joy.

Same exact error...

Bilbo_Baggins_Esq · Oct 2, 2013

I have tried that connection string already as the ACE Extended properties are not hard to find on the internet.

but I'm thinking it may not be the connection string (could be wrong though).

Are you able to create a set of files through which you can successfully open a connection?

I've found the error message in several searches that seem to relate to older Jet drivers designating the incorrect version of Jet, but notihng for ACE.

As further background, these are the selected references for the module:
Visual Basic for Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library

How to connect Excel 2010 workbook to another XLSX file

Bilbo_Baggins_Esq

Registered User.

pr2-eugin

Super Moderator

Bilbo_Baggins_Esq

Registered User.

Bilbo_Baggins_Esq

Registered User.

Similar threads

Users who are viewing this thread