Obtaining worksheet name for DAO recordset (OT)

In:

http://groups.google.com/group/microsoft.public.access/msg/f 16be594f6541bc6

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
CDMAPoster@FortuneJames.com
MPAPoster@FortuneJames.com

Re: Obtaining worksheet name for DAO recordset (OT)

On Oct 24, 8:44 pm, CDMAPos...@FortuneJames.com wrote:
> In:
>
> http://groups.google.com/group/microsoft.public.access/msg/f 16be594f6...
>
> 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
> CDMAPos...@FortuneJames.com
> MPAPos...@FortuneJames.com

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.

Re: Obtaining worksheet name for DAO recordset (OT)

On Oct 24, 9:17 pm, lyle wrote:
> On Oct 24, 8:44 pm, CDMAPos...@FortuneJames.com wrote:
>
>
>
>
>
> > 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/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):
>
> >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
> > CDMAPos...@FortuneJames.com
> > MPAPos...@FortuneJames.com
>
> 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
CDMAPoster@FortuneJames.com
MPAPoster@FortuneJames.com

Re: Obtaining worksheet name for DAO recordset (OT)

wrote

> 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

Re: Obtaining worksheet name for DAO recordset (OT)

"Larry Linson" wrote:

> > 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." :-)

Undoubtedly when redheads were passing by.

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/

Re: Obtaining worksheet name for DAO recordset (OT)

"Tony Toews [MVP]" wrote in message
news:kb70i3lnlnasf9bkohg6pldo2cfl6acki5@4ax.com...
> "Larry Linson" wrote:
>
>> > 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." :-)
>
> Undoubtedly when redheads were passing by.

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

Re: Obtaining worksheet name for DAO recordset (OT)

On Oct 25, 2:48 pm, "Larry Linson" wrote:
> > "Larry Linson" wrote:

> Yes, I am sure I read that.

I red every chance I get.

Re: Obtaining worksheet name for DAO recordset (OT)

On Oct 24, 9:17 pm, lyle wrote:
> On Oct 24, 8:44 pm, CDMAPos...@FortuneJames.com wrote:
>
>
>
>
>
> > 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/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):
>
> >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
> > CDMAPos...@FortuneJames.com
> > MPAPos...@FortuneJames.com
>
> 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
CDMAPoster@FortuneJames.com
MPAPoster@FortuneJames.com