Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get worksheet names
Hi,
I use the OleDb objects to connect to an excel file and retrieve data from a specified worsheet. What SELECT statement should I use (if there is an existing one) to retrieve the names of the worksheets in the file? Thanks, Mircea |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get worksheet names
Mircea,
JetSQL doesnt allow to read the table names, you can use ADOX or ADODB.OpenSchema(adSchemaTables) BUT you'll get a mix of named ranges and no way to get the sheets by their index... So: I've written following function a while back.. topic: Reading Sheet Names/Index from closed file using Biff8. date : Nov 4,2004 hth :) Function ReadSheets(sFileName As String, _ Optional bWorksheetsOnly As Boolean = True) As Collection 'Returns a collection of the sheets in a workbook .. 'Note: DialogSheets are returned as Worksheets! 'keepITcool 4 Nov 2004, excel.programming Dim lHnd&, lLen&, lPos&, iPos&, iErr%, bTyp As Byte, sTxt$, iIdx& Dim aByt() As Byte, cRes As Collection Const IDbof = &H809 Const IDbiff8 = &H10 Const IDboundsheet = &H85 Const BuffSize = &H400 If Dir(sFileName) = vbNullString Then iErr = 1: GoTo theExit ReDim aByt(0 To BuffSize) lLen = FileLen(sFileName) lHnd = FreeFile Open sFileName For Binary Access Read As #lHnd Len = BuffSize 'Is it OLE structured? If Input(6, #lHnd) < "ÐÏࡱ" Then iErr = 2: GoTo theExit 'Is it Biff8? Do lPos = lPos + BuffSize - 1 Get #lHnd, lPos, aByt iPos = InStrB(aByt, ChrW$(IDbof) & ChrW$(IDbiff8)) Loop While iPos = 0 And lPos < lLen If iPos = 0 Then iErr = 3: GoTo theExit 'Find the BoundSheets... lPos = 0: iPos = 0 Do lPos = lPos + BuffSize - 1 Get #lHnd, lPos, aByt iPos = InStrB(aByt, ChrW$(IDboundsheet)) Loop While iPos = 0 And lPos < lLen If iPos = 0 Then iErr = 4: GoTo theExit Set cRes = New Collection Do lPos = lPos + iPos - 1 Get #lHnd, lPos, aByt bTyp = aByt(9) 'types: 0WKS/1MACRO/2CHART Debug.Assert aByt(11) = 0 sTxt = Mid(StrConv(aByt, vbUnicode), 12 + 1, aByt(10)) If bWorksheetsOnly Imp bTyp = 0 Then iIdx = iIdx + 1 'Add an array index/name/type cRes.Add Array(iIdx, sTxt, bTyp), sTxt End If If aByt(aByt(2) + 4) < IDboundsheet Then iPos = 0 Else iPos = InStrB(4, aByt , ChrW$(IDboundsheet)) End If Loop While iPos 0 theExit: If lHnd Then Close #lHnd Set ReadSheets = cRes If iErr Then Select Case iErr Case 1: sTxt = "File not found." Case 2: sTxt = "Unrecognised file format." Case 3: sTxt = "Nof a Biff8 file format. Older Excel version?" Case Else: sTxt = "Error analysing file." End Select On Error Resume Next 'let Err indicate what went wrong Err.Raise vbObjectError + iErr, , sTxt End If End Function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Mircea Pleteriu wrote : Hi, I use the OleDb objects to connect to an excel file and retrieve data from a specified worsheet. What SELECT statement should I use (if there is an existing one) to retrieve the names of the worksheets in the file? Thanks, Mircea |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get worksheet names
I got it by means of OleDbConnectionGetOleDbSchemaTable method.
"Mircea Pleteriu" wrote in message ... Hi, I use the OleDb objects to connect to an excel file and retrieve data from a specified worsheet. What SELECT statement should I use (if there is an existing one) to retrieve the names of the worksheets in the file? Thanks, Mircea |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get worksheet names
Something along the lines of this article perhaps?
http://support.microsoft.com/default...b;en-us;318373 HOW TO: Retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method in Visual Basic .NET -- Regards, Tom Ogilvy "Mircea Pleteriu" wrote in message ... I got it by means of OleDbConnectionGetOleDbSchemaTable method. "Mircea Pleteriu" wrote in message ... Hi, I use the OleDb objects to connect to an excel file and retrieve data from a specified worsheet. What SELECT statement should I use (if there is an existing one) to retrieve the names of the worksheets in the file? Thanks, Mircea |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get worksheet names
AA2e72E wrote: Try this solution to enumerate Sheet names or defined names or ALL. Case "SHEETS" If 0 < InStr(Table.Name, "$") Then NamesinXLS = NamesinXLS & "," & Table.Name Case "NAMES" If 0 = InStr(Table.Name, "$") Then NamesinXLS = NamesinXLS & "," & Table.Name This code list worksheet-level defined Names as SHEETS :( Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
Changing VB Component Names to match Worksheet names using VBE | Excel Programming | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming |