opening excel sheet in access VBA code

  • Thread starter Thread starter dave
  • Start date Start date
D

dave

Hi
I'm using office 2003 and building an application to import data into access
from excel.

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & gfilepathname & ";" & "Extended
Properties=Excel 8.0;"
.Open
End With


strQuery = "SELECT * FROM [M8SHP25022005$]" 'this works

'strQuery = "SELECT * FROM `Sheet1$`" 'Not working ' I got this line
code from msdn site...saying u can delimit the sheet name and open first
sheet without specifying name but some how its not working...and throws
error saying "Invalid Sheet1$ name"

I want to open first sheet of workbook without specifying sheet name.

Any help would be appreciated...
Thanx...
 
Hi Dave,

As far as I know you need to specify the worksheet name. Otherwise you'd
need a SQL construct equivalent to SELECT * FROM (FIRST TABLE IN
DATABASE), which doesn't seem to exist.

If you go to http://groups.google.com and search for
"drink soup audibly" group:microsoft.public.excel.programming
you'll find a function that returns a list of worksheet names from a
workbook without opening it.


Hi
I'm using office 2003 and building an application to import data into access
from excel.

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & gfilepathname & ";" & "Extended
Properties=Excel 8.0;"
.Open
End With


strQuery = "SELECT * FROM [M8SHP25022005$]" 'this works

'strQuery = "SELECT * FROM `Sheet1$`" 'Not working ' I got this line
code from msdn site...saying u can delimit the sheet name and open first
sheet without specifying name but some how its not working...and throws
error saying "Invalid Sheet1$ name"

I want to open first sheet of workbook without specifying sheet name.

Any help would be appreciated...
Thanx...
Click to expand...
 
Back
Top