How to find Worksheet name using a DAO select statement?

[Rico]

Hello,

I have a function in Access that I'm using to open a DAO recordset based on
an Excel spreadsheet. My problem is, when I use the Select statement to get
the informaiton, I have to know what the worksheet name is. I have a couple
of questions here;

1. Is there any way to find out what the worksheet name is or any way to
reference the first worksheet without knowing it's name?
2. If I have to use an Excel object to open the spreadsheet first, is there
any library I can distribute to ensure that this will work on a machine that
does not contain excel?

Here is the code I'm working with...

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0;")
Set rs = dbtmp.OpenRecordset("select * from `owssvr(1)$`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally

End Function

Any help would be greatly appreciated.

Thanks!
Rick

 

[James A. Fortune]

Hello,

I have a function in Access that I'm using to open a DAO recordset based on
an Excel spreadsheet. My problem is, when I use the Select statement to get
the informaiton, I have to know what the worksheet name is. I have a couple
of questions here;

1. Is there any way to find out what the worksheet name is or any way to
reference the first worksheet without knowing it's name?
2. If I have to use an Excel object to open the spreadsheet first, is there
any library I can distribute to ensure that this will work on a machine that
does not contain excel?

Here is the code I'm working with...

Public Function OpenExcelDAO(ByVal strPath As String) As DAO.Recordset
On Error GoTo Catch
Dim dbtmp As DAO.Database
Dim tblObj As DAO.TableDef
Dim rs As DAO.Recordset

'If there is nothing to import, then exit
If Not FileExists(strPath) Then GoTo Finally

Set dbtmp = OpenDatabase(strPath, False, True, "Excel 8.0;")
Set rs = dbtmp.OpenRecordset("select * from `owssvr(1)$`")
Set OpenExcelDAO = rs
Set rs = Nothing
Set dbtmp = Nothing


Finally:
Exit Function

Catch:
LogError Err.Number, Err.Description, mstrModule, "OpenExcelDAO"
Resume Finally

End Function

Any help would be greatly appreciated.

Thanks!
Rick

Rico,

Start here (ignore the OT posts):

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/857be131ff8d925b

James A. Fortune
(e-mail address removed)

 

[none]

Start here (ignore the OT posts):

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/857be131ff8d925b

Did you confirm TableDefs(0).Name always returns the name of the first
worksheet?

Just curious,
Hans

 

[James A. Fortune]

Did you confirm TableDefs(0).Name always returns the name of the first
worksheet?

Just curious,
Hans

It worked for every case I tried. Plus, it makes sense. That makes it
even better.

James A. Fortune
(e-mail address removed)

My mechanic won over 700 stock car races during his racing career.

 

[Rico]

Thanks! I'll give that a try!

It worked for every case I tried. Plus, it makes sense. That makes it
even better.

James A. Fortune
(e-mail address removed)

My mechanic won over 700 stock car races during his racing career.