Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Obtaining worksheet name for DAO recordset (OT)

16 views
Skip to first unread message

CDMAP...@fortunejames.com

unread,
Oct 24, 2007, 8:44:05 PM10/24/07
to
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
CDMAP...@FortuneJames.com
MPAP...@FortuneJames.com

lyle

unread,
Oct 24, 2007, 9:17:23 PM10/24/07
to
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/under the
> 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.

CDMAP...@fortunejames.com

unread,
Oct 24, 2007, 10:51:45 PM10/24/07
to

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,

Larry Linson

unread,
Oct 24, 2007, 11:23:41 PM10/24/07
to
<CDMAP...@FortuneJames.com> 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


Tony Toews [MVP]

unread,
Oct 25, 2007, 12:37:40 AM10/25/07
to
"Larry Linson" <bou...@localhost.not> 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. <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

unread,
Oct 25, 2007, 2:48:14 PM10/25/07
to

"Tony Toews [MVP]" <tto...@telusplanet.net> wrote in message
news:kb70i3lnlnasf9bko...@4ax.com...

> "Larry Linson" <bou...@localhost.not> 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. <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

unread,
Oct 25, 2007, 3:10:40 PM10/25/07
to
On Oct 25, 2:48 pm, "Larry Linson" <boun...@localhost.not> wrote:
> > "Larry Linson" <boun...@localhost.not> wrote:

> Yes, I am sure I read that.

I red every chance I get.

CDMAP...@fortunejames.com

unread,
Oct 25, 2007, 4:02:16 PM10/25/07
to
On Oct 24, 9:17 pm, lyle <lyle.fairfi...@gmail.com> wrote:

Lyle,

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

0 new messages