Determine Worksheet Names in Excel Workbook

 Keywords:  Excel Worksheets

Here are two different takes on enumerating the various Worksheets in a Worknbook

Via Excel

cXLS = AskFilename("Select Excel WorkBook",".\","Excel Files|*.xls|","*.xls",1)
DB             = ObjectOpen("Excel.Application")
DB.Visible     = @FALSE
DB.UserControl = @FALSE
oAPP = DB.Workbooks
oAPP.Open(cXLS)
oACT = DB.ActiveWorkBook
oSheets = oACT.Sheets
n = oSheets.Count
If n > 0 
   cSheets=""
   hEnum = ObjectCollectionOpen(oSheets)
   While 1
      oSheet = ObjectCollectionNext(hEnum)
      If oSheet == 0 Then Break
      cSheets = StrCat( cSheets,oSheet.Name,@TAB )
      ObjectClose(oSheet)
   EndWhile
   ObjectCollectionClose(hEnum)
   ObjectClose(oSheets)
   cSheet = AskItemlist("Select WorkSheet",cSheets,@TAB,@UNSORTED,@SINGLE)
   
   ; to go to active sheet, uncomment the following
   ;DB.Visible     = @TRUE
   ;oWS = DB.WorkSheets(cSheet)
   ;oWS.Activate
   ;...
   ;ObjectClose(oWS)
   
EndIf
ObjectClose(oACT)
oAPP.Close()
ObjectClose(oAPP)
DB.Quit()
ObjectClose(DB)
Exit

Via ADO

;/////////////////////////////////////////////////////////////////////////;
;//  WINBATCH   - Determine Worksheet Names in Excel Workbook that is    /;
;//  (U)ser     - closed.  Uses ADO rather than Access.Application.      /;
;//  (D)efined  - UDF could be easily formatted to (a) look for a        /;
;//  (F)unction - particular sheet in a subdir (b) create database of    /;
;//             - workbooks and named sheets                             /;
;//                                                                      /;
;//  RETURNS    - tab-delimited list of worksheets in selected workbook  /;
;//                                                                      /;
;//  Stan Littlefield   - June 23, 2002                                  /;
;/////////////////////////////////////////////////////////////////////////;
#DefineFunction GetSheets(cFile)
sheets    = ""
cat       = ObjectOpen( "ADOX.Catalog" )
cConn     = StrCat( "Provider=MSDASQL.1;Data Source=Excel Files;Initial Catalog=",cFile )
cat.ActiveConnection = cConn
tbls      = cat.Tables
n         = tbls.count()
For i     = 0 To ( n-1 )
   tbl    = cat.Tables(i)
   ; NOTE: ADO returns sheetnames with $ appended, i.e. Sheet1 is Sheet1$
   sheets = ItemInsert( StrReplace(tbl.Name,"$",""), -1, sheets, @TAB )
Next
ObjectClose( cat )
BoxShut()
Return( sheets )
#EndFunction

cFile     = AskFilename( "Select Excel File", DirGet(), "XLS|*.XLS","*.XLS",1)
BoxOpen( "Searching WorkBook...",cFile )
sheets    = AskItemlist( StrCat( "WorkSheets In ",cFile ), GetSheets( cFile ), @TAB, @UNSORTED, @SINGLE )

Exit

Article ID:   W15627

File Created: 2003:08:06:12:12:34

Last Updated: 2003:08:06:12:12:34

Database Search

TechHome

Determine Worksheet Names in Excel Workbook

Via Excel

Via ADO