Accessing Excel to import Data into Access via ADO

[Peter Neumaier]

Hi NG!

I am trying to import some Data from Excel-Sheets, the first step is to
import data from 12 Cellls, from A1 to D3:

12 34 56 77
33 44 5 1
33 21 34 12

to acces this data I use following code

Call GetDataFromWorksheet("F:\test.xls", "SELECT * FROM [Tabelle1$A13]")

'''''''''''''''''''''''''''''''
'------------------------------
''''''''''''''''''''''''''''''''

Sub GetDataFromWorksheet(SourceFile As String, strSQL As String)

Dim con As ADODB.Connection, rst As ADODB.Recordset, f As Integer, r As Long
Set con = New ADODB.Connection
On Error Resume Next
con.Open "DRIVER={Microsoft Excel Driver
(*.xls)};DriverId=790;ReadOnly=True;" & _
"DBQ=" & SourceFile & ";"
' DriverId=790: Excel 97/2000

If con Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If

' open a recordset
Set rst = New ADODB.Recordset

rst.Open strSQL, con , adOpenForwardOnly, adLockReadOnly , adCmdText

If rst Is Nothing Then
MsgBox "Can't open the file!", vbExclamation, ThisWorkbook.Name
con.Close
Set con = Nothing
Exit Sub
Else

Do While Not rst.EOF
For k = 0 To rst.Fields.Count - 1
MsgBox rst.Fields.Item(k).Value
Next k

rst.MoveNext
Loop

End If

If rst.State = adStateOpen Then
rst.Close
End If
Set rst = Nothing
con.Close
Set con = Nothing
End Sub

The problem is, that I cant access the first line of my
data(12,34,56,77), so I tried it for the first with
MsgBox rst.Fields.Item(k).Name
this works only if my first-line-values are non-numerical, otherwise
I receive values for the first line like: "F1" for the first column,
"F2" for the 2nd column etc. Are there any workarounds for this issue!?

After all, I have to select values from single cells from a Excel-Sheet,
how is this realisable!?

Thx in advance for any help/advice!

 

[Brendan Reynolds]

See the KB article at the following URL ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;257819#Connect

Apparently, the default is to assume that the first row contains field
names. The KB article has the details.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.