It happens that Andy formulated :
Hi Everyone
I have a macro in Excel 2003 that imports Excel data from a separate
Excel file. I can't seem to figure out how to do this with Excel
2007. Can you please help?
Andy
XL2003 and earlier use a different data provider than XL2007 and later.
The former uses JET; the latter uses ACE. What you need to do is make
your code version aware so that it knows which provider to use based on
the version of Excel that it's running in.
Example:
Construct a connection string something like this:
If Application.Version => 12 Then
'use ACE provider connection string
Else
'use JET provider connection string
End If
This precludes that you'll have to construct 2 separate connection
strings. You could use constants for this:
Const sProvider As String = "Microsoft.Jet.OLEDB.4.0;"
Const sExtProps As String = "Excel 8.0;"
Const sProvider12 As String = "Microsoft.ACE.OLEDB.12.0;"
Const sExtProps12 As String = "Excel 12.0 Xml;"
If you know the data source beforehand, you could configure your code
something like this:
<aircode>
' Use a var to hold data source
sDataSource = "<FullPathAndFilename>"
If Application.Version => 12 Then
'use ACE provider connection string
sConnect = "Provider=" & sProvider12 & _
"Data Source=" & sDataSource & _
"Extended Properties=" & sExtProps12
Else
'use JET provider connection string
sConnect = "Provider=" & sProvider & _
"Data Source=" & sDataSource & _
"Extended Properties=" & sExtProps
End If
</aircode>
' Construct your SQL statement
sSQL = "SELECT * FROM..."
' Grab the data into a recordset
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText
HTH
