detect the worksheet-names in an EXCEL 5.0 workbook
Quote:
>How can I detect the names on the tabs at the bottom of an EXCEL 5.0 or
>higher spreadsheet ?
BOY! I just went through all of that last week! I got no help here and the
books I had (VB5) led me all over the place with NO answer... finally after a
few hours of reading and searching Microsofts Knowledge Database, I came up
with 'my' solution.
What I'll show you will give you the 'number' of worksheets in the Excel file
and How you can access them (while not knowing the 'name') and How To access a
worksheet IF you do know the tab name!
'---------------------------------------------------------------------------------------
Dim Ws As Integer ' Excel Worksheet
Dim Rn As Integer ' Excel Worksheet Row Nbr
Dim ProjNbr As String ' Project Number
Dim XLSheet As Object
' Open Excel file
XLFilName = dirList.Path + "\" + XLFile
Set XLSheet = GetObject(XLFilName, "Excel.Sheet")
'<< this Opens the XLS file >>
' Find total number of Sheets
Ws = XLSheet.Sheets.Count
'<< self explainatory >>
' Open WorkSheet using Total number
For Ws = 1 To Ws
' Get Project Numbers from Worksheet, Rows 5 to 35
For Rn = 5 To 35
ProjNbr = XLSheet.Worksheets(Ws).Cells(Rn, 3).Value
'<< this gets a WS using it's Number location... >>
'<< OR you can use the following IF you know it's actual Tab name >>
' ProjNbr = XLSheet.Worksheets("MyTab").Cells(Rn, 3).Value
'<< assuming _MyTab_ is the actual Tab name >>
Next Rn ' get next Page Number info
Next Ws ' get next Worksheet
' Close Excel
XLSheet.Application.Quit
' Clear variable
Set XLSheet = Nothing
'---------------------------------------------------------------------------------------
I don't know IF this will be of any help to you... BUT IF you do figure out
how to find the actual Tab Name and can use it to access a WS, I WOULD
appreciate if if you let me know How you do it (I've given up as the above
suits my current needs)...
Regards,
Bruce
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
If brains were dynamite, you'd not have enough to blow your nose!
(live from the West End of Vancouver, B.C. Canada)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
NOTICE: Please remove the <**> from the begining of my Address before you
~~~~~~~ send me ANY Email replies. This was done to STOP all UnSolicted
~~~~~~~ JUNK E-Mailings. Such E-mailings is Not Welcomed nor Wanted!
