It happens that Frederick formulated :
Can anyone give me some help to get data (one row at a time) into a recordset
within Visual Basic 6.
Regards
Fred
If you use ADODB you can get the data direct from the file (not open in
Excel), providing you use the JET or ACE provider. ACE works for Excel
2007 and later, JET works for the earlier versions.
In Excel files, the XLS or XLSX is the database file to open, and
worksheets are the tables to run your query on.
I use a version-aware function to set up my connection string according
to which provider is appropriate for the running instance of Excel.
(However, you don't need to have a running instance or have any files
open!) Otherwise, everything else is the same as far as building
queries/recordsets goes.
Example:
Construct a connection string something like this:
If appXL.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 VB6
code something like this:
<aircode>
' Use a var to hold data source
sDataSource = "<FullPathAndFilename>"
If appXL.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
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
