Is it possible to switch worksheets within a workbook in a WSH VBScript (referenced by name) and if so, how would I do it?
This will be used in a script that already opens an Excel file. I want to access a worksheets called "Version Control".
The following is how I am opening the workbook:
xlsFile = path & "\xml-sitemap.xls"
Set objExcel = WScript.CreateObject("Excel.Application")
objExcel.Workbooks.open(xlsFile)
This will do it (amended following Ekkehard.Horner's comment)
Dim ws
Dim wb
xlsFile = path & "\xml-sitemap.xls"
Set objExcel = WScript.CreateObject("Excel.Application")
objExcel.Workbooks.open(xlsFile)
Set wb = objExcel.ActiveWorkbook 'the ActiveWorkbook will refer to the opened workbook
Set ws = wb.Worksheets("Version Control") 'ws is a Worksheet object
When automating Excel from VBScript, the global objects provided by Excel to VBA code are missing. So a minimal "work with sheets" script should look like:
Option Explicit
Dim sFSpec : sFSpec = "P:\athto\your.xls"
Dim oExcel : Set oExcel = CreateObject("Excel.Application")
Dim oWB : Set oWB = oExcel.Workbooks.Open(sFSpec)
Dim oWS, sWSName
Set oWS = oWB.Worksheets(1)
sWSName = oWS.Name
WScript.Echo 0, sWSName
Set oWS = oWB.Worksheets(2)
WScript.Echo 1, oWS.Name
Set oWS = oWB.Worksheets(sWSName)
WScript.Echo 2, oWS.Name
oExcel.Quit
Using VBScript, you can return the name of the tab of an Excel spreadsheet with the ".name" extension.
Dim excelPath, objExcel, oData, Excel_tab
excelPath = "C:\Users\user.name\Desktop\file.xls"
Set objExcel = CreateObject("Excel.Application")
Set oData = objExcel.Workbooks.Open(excelPath)
Excel_tab = objExcel.ActiveWorkbook.Worksheets(1).name
msgbox(Excel_tab)
objExcel.quit
