摘要:VBS - 取得 Excel 檔中的 Sheet 清單
如標題所示，這個功能主要是擷取 Excel 檔的 Sheet 清單，會這樣做的原因很簡單，因為我們要產生一些檔案，來加快專案開發的速度，以下就來實做唄...
步驟一：建立一個 Excel 檔案，並且建立一些資料
步驟二：建立一個取得 Excel 檔中的 Sheet 清單的VBS
Code：
DIM OBJXL, OBJWB, OBJWS
DIM EXCELFILEPATH, INTROW, WORKSHEETNAME, STRMSG
EXCELFILEPATH = "D:\EXCEL_SAMPLE.XLS"
SET OBJXL = CREATEOBJECT("EXCEL.APPLICATION")
SET OBJWB = OBJXL.WORKBOOKS.OPEN(EXCELFILEPATH)
STRMSG = ""
INTROW = 1
FOR EACH I IN OBJXL.ACTIVEWORKBOOK.WORKSHEETS
WSCRIPT.ECHO I.NAME
NEXT
OBJWB.CLOSE
OBJXL.QUIT
結果：
步驟三：來加個工，除了讀取 Sheet 清單外，再來讀取各個 Sheet 中的內容
Code：
DIM OBJXL, OBJWB, OBJWS
DIM EXCELFILEPATH, INTROW, WORKSHEETNAME, STRMSG
EXCELFILEPATH = "D:\EXCEL_SAMPLE.XLS"
SET OBJXL = CREATEOBJECT("EXCEL.APPLICATION")
SET OBJWB = OBJXL.WORKBOOKS.OPEN(EXCELFILEPATH)
STRMSG = ""
INTROW = 1
FOR EACH I IN OBJXL.ACTIVEWORKBOOK.WORKSHEETS
WORKSHEETNAME = I.NAME
SET OBJWS = OBJXL.ACTIVEWORKBOOK.WORKSHEETS(I.NAME)
DO UNTIL OBJWS.CELLS(INTROW, 1).VALUE = ""
IF IsEmpty(STRMSG) THEN
STRMSG = OBJWS.CELLS(INTROW, 1).VALUE & "：" & OBJWS.CELLS(INTROW, 2).VALUE
ELSE
STRMSG = STRMSG & vbcrlf & OBJWS.CELLS(INTROW, 1).VALUE & "：" & OBJWS.CELLS(INTROW, 2).VALUE
END IF
INTROW = INTROW + 1
LOOP
WSCRIPT.ECHO I.NAME & vbcrlf & STRMSG
STRMSG = ""
INTROW = 1
NEXT
OBJWB.CLOSE
OBJXL.QUIT
結果：
