Obtaining worksheet name for DAO recordset (OT)

[CDMAPoster]

In:

http://groups.google.com/group/microsoft.public.access/msg/f16be594f6541bc6

Stefan Hoffmann said:

You can use Jet in a query to export data to a new file:

SELECT *
INTO [Excel 8.0;Database=YourPath/File.xls].[SheetName]
FROM table

I'm writing some VBA code with a reference to 'Microsoft DAO 3.6
Object Library' to import data from the first worksheet of whatever
Excel (2003) file the user selects. In order to get the name of the
first worksheet of the selected file, I also referenced 'Microsoft
ActiveX Data Objects 2.1 Library' and ran the following code, most of
which was from http://support.microsoft.com/kb/257819/EN-US/ under the
section entitled "ODBC Provider Using a DSN-Less Connection String":

'---Begin module code---
Private Function GetExcelFirstWorksheetName(strFileName As String) As
String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
"DBQ=" & strFileName & ";ReadOnly=True;"
.Open
End With
Set rs = cn.OpenSchema(adSchemaTables)
rs.MoveFirst
GetExcelFirstWorksheetName = rs(2)
rs.Close
cn.Close
End Function
'---End module code---

which I then use as part of my DAO SQL string, something like:

strSQL = "SELECT * FROM [" & strWorksheetName & "];"
Set DAOWS = DBEngine.Workspaces(0)
Set DAODB = DAOWS.OpenDatabase(strFileName, False, True, "Excel
8.0;HDR=No;")
Set DAORS = DAODB.OpenRecordset(strSQL, dbOpenForwardOnly)
Do While Not DAORS.BOF And Not DAORS.EOF
....
Loop

BTW, the file browser code I used (with slight modifications) was from
(Hasler Thomas):

http://www.codeproject.com/vbscript/filebrowse.asp

The code is being run in Catia 5 but these newsgroups seem like the
best place to ask.

It all works, but is there a simpler way to get the name of the first
worksheet of the selected Excel file?

James A. Fortune
(e-mail address removed)
(e-mail address removed)

 

[lyle]

In:

http://groups.google.com/group/microsoft.public.access/msg/f16be594f6...

Stefan Hoffmann said:

You can use Jet in a query to export data to a new file:

SELECT *
INTO [Excel 8.0;Database=YourPath/File.xls].[SheetName]
FROM table

I'm writing some VBA code with a reference to 'Microsoft DAO 3.6
Object Library' to import data from the first worksheet of whatever
Excel (2003) file the user selects. In order to get the name of the
first worksheet of the selected file, I also referenced 'Microsoft
ActiveX Data Objects 2.1 Library' and ran the following code, most of
which was fromhttp://support.microsoft.com/kb/257819/EN-US/under the
section entitled "ODBC Provider Using a DSN-Less Connection String":

'---Begin module code---
Private Function GetExcelFirstWorksheetName(strFileName As String) As
String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
"DBQ=" & strFileName & ";ReadOnly=True;"
.Open
End With
Set rs = cn.OpenSchema(adSchemaTables)
rs.MoveFirst
GetExcelFirstWorksheetName = rs(2)
rs.Close
cn.Close
End Function
'---End module code---

which I then use as part of my DAO SQL string, something like:

strSQL = "SELECT * FROM [" & strWorksheetName & "];"
Set DAOWS = DBEngine.Workspaces(0)
Set DAODB = DAOWS.OpenDatabase(strFileName, False, True, "Excel
8.0;HDR=No;")
Set DAORS = DAODB.OpenRecordset(strSQL, dbOpenForwardOnly)
Do While Not DAORS.BOF And Not DAORS.EOF
...
Loop

BTW, the file browser code I used (with slight modifications) was from
(Hasler Thomas):

http://www.codeproject.com/vbscript/filebrowse.asp

The code is being run in Catia 5 but these newsgroups seem like the
best place to ask.

It all works, but is there a simpler way to get the name of the first
worksheet of the selected Excel file?

James A. Fortune
(e-mail address removed)
(e-mail address removed)

maybe

Set DAOWS = DBEngine.Workspaces(0)
Set DAODB = DAOWS.OpenDatabase(strFileName, False, True, "Excel
8.0;HDR=No;")
strSQL = "SELECT * FROM [" & DAODB.TableDefs(0).Name & "];"
Set DAORS = DAODB.OpenRecordset(strSQL, dbOpenForwardOnly)

Is TableDefs(0).Name always the name of the first sheet? It is in my
experience but that experience is limited to just two files.

 

[CDMAPoster]

In:

Stefan Hoffmann said:

You can use Jet in a query to export data to a new file:

SELECT *
INTO [Excel 8.0;Database=YourPath/File.xls].[SheetName]
FROM table

I'm writing some VBA code with a reference to 'Microsoft DAO 3.6
Object Library' to import data from the first worksheet of whatever
Excel (2003) file the user selects. In order to get the name of the
first worksheet of the selected file, I also referenced 'Microsoft
ActiveX Data Objects 2.1 Library' and ran the following code, most of
which was fromhttp://support.microsoft.com/kb/257819/EN-US/underthe
section entitled "ODBC Provider Using a DSN-Less Connection String":

'---Begin module code---
Private Function GetExcelFirstWorksheetName(strFileName As String) As
String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
"DBQ=" & strFileName & ";ReadOnly=True;"
.Open
End With
Set rs = cn.OpenSchema(adSchemaTables)
rs.MoveFirst
GetExcelFirstWorksheetName = rs(2)
rs.Close
cn.Close
End Function
'---End module code---

which I then use as part of my DAO SQL string, something like:

strSQL = "SELECT * FROM [" & strWorksheetName & "];"
Set DAOWS = DBEngine.Workspaces(0)
Set DAODB = DAOWS.OpenDatabase(strFileName, False, True, "Excel
8.0;HDR=No;")
Set DAORS = DAODB.OpenRecordset(strSQL, dbOpenForwardOnly)
Do While Not DAORS.BOF And Not DAORS.EOF
...
Loop

BTW, the file browser code I used (with slight modifications) was from
(Hasler Thomas):

The code is being run in Catia 5 but these newsgroups seem like the
best place to ask.

It all works, but is there a simpler way to get the name of the first
worksheet of the selected Excel file?

James A. Fortune
(e-mail address removed)
(e-mail address removed)

maybe

Set DAOWS = DBEngine.Workspaces(0)
Set DAODB = DAOWS.OpenDatabase(strFileName, False, True, "Excel
8.0;HDR=No;")
strSQL = "SELECT * FROM [" & DAODB.TableDefs(0).Name & "];"
Set DAORS = DAODB.OpenRecordset(strSQL, dbOpenForwardOnly)

Is TableDefs(0).Name always the name of the first sheet? It is in my
experience but that experience is limited to just two files

Lyle,

You rock! And I don't mean in a rocking chair . I should be able
to try out your code tomorrow. It should have been obvious to me that
the worksheets correspond to TableDef's, but it wasn't.

Thanks,

James A. Fortune
(e-mail address removed)
(e-mail address removed)

 

[Larry Linson]

You rock! And I don't mean in a rocking chair .

He used to, but I heard they took his rocking chair license away for
"reckless rocking."

Larry

 

[Tony Toews [MVP]]

He used to, but I heard they took his rocking chair license away for
"reckless rocking."

Undoubtedly when redheads were passing by. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Larry Linson]

Undoubtedly when redheads were passing by. <smile>

I've read that some in Lyle's neighborhood might be sufficiently excited by
a passing redhead to rock recklessly. Yes, I am sure I read that.

Larry

 

[lyle]

Yes, I am sure I read that.

I red every chance I get.

 

[CDMAPoster]

In:

Stefan Hoffmann said:

You can use Jet in a query to export data to a new file:

SELECT *
INTO [Excel 8.0;Database=YourPath/File.xls].[SheetName]
FROM table

I'm writing some VBA code with a reference to 'Microsoft DAO 3.6
Object Library' to import data from the first worksheet of whatever
Excel (2003) file the user selects. In order to get the name of the
first worksheet of the selected file, I also referenced 'Microsoft
ActiveX Data Objects 2.1 Library' and ran the following code, most of
which was fromhttp://support.microsoft.com/kb/257819/EN-US/underthe
section entitled "ODBC Provider Using a DSN-Less Connection String":

'---Begin module code---
Private Function GetExcelFirstWorksheetName(strFileName As String) As
String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
"DBQ=" & strFileName & ";ReadOnly=True;"
.Open
End With
Set rs = cn.OpenSchema(adSchemaTables)
rs.MoveFirst
GetExcelFirstWorksheetName = rs(2)
rs.Close
cn.Close
End Function
'---End module code---

which I then use as part of my DAO SQL string, something like:

strSQL = "SELECT * FROM [" & strWorksheetName & "];"
Set DAOWS = DBEngine.Workspaces(0)
Set DAODB = DAOWS.OpenDatabase(strFileName, False, True, "Excel
8.0;HDR=No;")
Set DAORS = DAODB.OpenRecordset(strSQL, dbOpenForwardOnly)
Do While Not DAORS.BOF And Not DAORS.EOF
...
Loop

BTW, the file browser code I used (with slight modifications) was from
(Hasler Thomas):

The code is being run in Catia 5 but these newsgroups seem like the
best place to ask.

It all works, but is there a simpler way to get the name of the first
worksheet of the selected Excel file?

James A. Fortune
(e-mail address removed)
(e-mail address removed)

maybe

Set DAOWS = DBEngine.Workspaces(0)
Set DAODB = DAOWS.OpenDatabase(strFileName, False, True, "Excel
8.0;HDR=No;")
strSQL = "SELECT * FROM [" & DAODB.TableDefs(0).Name & "];"
Set DAORS = DAODB.OpenRecordset(strSQL, dbOpenForwardOnly)

Is TableDefs(0).Name always the name of the first sheet? It is in my
experience but that experience is limited to just two files

Lyle,

It worked great, as I expected, allowing me to remove the function and
the ADO reference.

James A. Fortune
(e-mail address removed)
(e-mail address removed)