Re: Accessing Excel to import Data into Access via ADO

From: Brendan Reynolds (brenreyn)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 10:46:22 +0100

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.
"Peter Neumaier" <zelja1@hotmail.com> wrote in message 
news:2q4aciFqq5vrU1@uni-berlin.de...
> 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 GetDataFromWork***("F:\test.xls", "SELECT * FROM [Tabelle1$A1:D3]")
>
> '''''''''''''''''''''''''''''''
> '------------------------------
> ''''''''''''''''''''''''''''''''
>
> Sub GetDataFromWork***(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-***, 
> how is this realisable!?
>
> Thx in advance for any help/advice!
>