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$A1:D3]")


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
Set con = Nothing
Exit Sub

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


End If

If rst.State = adStateOpen Then
End If
Set rst = Nothing
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 ...;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)

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question