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!

 

[MikeC]

Peter,

Something, perhaps the provider, seems to be assuming that
the first record contains column headers. Consequently,
the first row is skipped. I've previously had trouble
with this frustrating issue and could not find a
satisfactory solution, so I found a reliable alternative.

You can create a temporary linked table, connect to it
using ADO, and then delete it when done. Very simple. A
very similar technique, using TransferText, also works
nicely on text files. Below code has been successfully
tested using a spreadsheet containing data identical to
yours.


Sub GetDataFromWorksheet2(SourceFile As String)

Dim fs As Object
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim k As Integer

Set fs = CreateObject("Scripting.FileSystemObject")

'Verify that file exists at given path.
If fs.FileExists(SourceFile) Then
'Create a linked table. Worksheet/range can also
be optionally selected.
DoCmd.TransferSpreadsheet acLink,
acSpreadsheetTypeExcel7, "Temp", SourceFile

'Create the connection and recordset.
Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

With rst
.Open "Temp", con, adOpenStatic,
adLockReadOnly, adCmdTable
.MoveFirst
Do While Not rst.EOF
For k = 0 To .Fields.Count - 1
MsgBox .Fields.Item(k).Value
Next k
.MoveNext
Loop
If .State = adStateOpen Then
.Close
End If
End With

'Delete the linked table (not the file) when done.
DoCmd.DeleteObject acTable, "Temp"

Else
MsgBox "Can't find the file!", vbExclamation,
SourceFile
End If

Set fs = Nothing
Set rst = Nothing
con.Close
Set con = Nothing

-----Original Message-----
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.

 

[Peter Neumaier]

Peter,

Something, perhaps the provider, seems to be assuming that
the first record contains column headers. Consequently,
the first row is skipped. I've previously had trouble
with this frustrating issue and could not find a
satisfactory solution, so I found a reliable alternative.

You can create a temporary linked table, connect to it
using ADO, and then delete it when done. Very simple. A
very similar technique, using TransferText, also works
nicely on text files. Below code has been successfully
tested using a spreadsheet containing data identical to
yours.


Sub GetDataFromWorksheet2(SourceFile As String)

Dim fs As Object
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim k As Integer

Set fs = CreateObject("Scripting.FileSystemObject")

'Verify that file exists at given path.
If fs.FileExists(SourceFile) Then
'Create a linked table. Worksheet/range can also
be optionally selected.
DoCmd.TransferSpreadsheet acLink,
acSpreadsheetTypeExcel7, "Temp", SourceFile

'Create the connection and recordset.
Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

With rst
.Open "Temp", con, adOpenStatic,
adLockReadOnly, adCmdTable
.MoveFirst
Do While Not rst.EOF
For k = 0 To .Fields.Count - 1
MsgBox .Fields.Item(k).Value
Next k
.MoveNext
Loop
If .State = adStateOpen Then
.Close
End If
End With

'Delete the linked table (not the file) when done.
DoCmd.DeleteObject acTable, "Temp"

Else
MsgBox "Can't find the file!", vbExclamation,
SourceFile
End If

Set fs = Nothing
Set rst = Nothing
con.Close
Set con = Nothing

-----Original Message-----
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!

.

Thank you MikeC for your great solution.
However, the "strange" behavior when reading the first line is a
documented bug:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;288343

Thx!

 

[Brendan Reynolds]

Thank you MikeC for your great solution.
However, the "strange" behavior when reading the first line is a
documented bug:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;288343

Thx!

My reading of the KB article, Peter, is that the bug is in the ODBC
provider. If I read the article correctly, I believe you could avoid it by
using the Jet provider instead.

--
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.