Retrieving records from closed Excel workbook
 

I need a simple way of retrieving records from a worksheet - with headers - in a closed Excel workbook using SQL. But I also want VBA to detect the Excel version and establish the proper connection (2007 and later).

HÃ¥kan

Retrieving records from closed Excel workbook
 

I need a simple way of retrieving records from a worksheet - with headers -
in a closed Excel workbook using SQL. But I also want VBA to detect the Excel
version and establish the proper connection (2007 and later).

Håkan

This would use ADODB and it doesn't matter which provider you use because both
VBA6/VBA7 support the JET/ACE engines.

FYI:
VBA7 is used in Excel 2010 and later;
VBA6 is used in Excel 2007 and earlier;

Here's a good primer on how to get started using ADODB with Excel files...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Retrieving records from closed Excel workbook
 

"GS" wrote in message

This would use ADODB and it doesn't matter which provider you use because
both VBA6/VBA7 support the JET/ACE engines.

FYI:
VBA7 is used in Excel 2010 and later;
VBA6 is used in Excel 2007 and earlier;

Just to add, although VBA6 / VBA7 is not related to this, unless need to
support 2003 for later versions probably better to use the ACE engine which
is normally available in all 32/64 bit systems, unlike JET which is less
likely to be particularly x64.

If in doubt use something like

If Val(Application.Version) <= 11 Then ' 2003 or earlier
sPovider = "Provider=Microsoft.Jet.OLEDB.4.0;"
Else
sPovider = "Provider=Microsoft.ACE.OLEDB.12.0;"
End If


Peter T

Retrieving records from closed Excel workbook
 

"GS" wrote in message

This would use ADODB and it doesn't matter which provider you use because
both VBA6/VBA7 support the JET/ACE engines.

FYI:
VBA7 is used in Excel 2010 and later;
VBA6 is used in Excel 2007 and earlier;

Just to add, although VBA6 / VBA7 is not related to this, unless need to
support 2003 for later versions probably better to use the ACE engine which
is normally available in all 32/64 bit systems, unlike JET which is less
likely to be particularly x64.

If in doubt use something like

If Val(Application.Version) <= 11 Then ' 2003 or earlier
sPovider = "Provider=Microsoft.Jet.OLEDB.4.0;"
Else
sPovider = "Provider=Microsoft.ACE.OLEDB.12.0;"
End If


Peter T

Yes, that's how I have my wrapper coded. This is outlined in Rob's doc that
accompanies the samples in the link I provided.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion