# Problem converting Excel macro to .vbs script



## peridot (Jan 28, 2011)

Hi,

I need to write a .vbs script that will start Excel, open a workbook, make some changes to the specified worksheet, and then close the workbook and Excel. The .vbs script will be called from inside a SAS program.

I have this much of it working. I call the script from a SAS program with the line:

X "T:\Programs\Format_WS.vbs 
/DestFile:test.xls 
/DestPath:T:\Results
/DestSheet:Sales2010
";

The .vbs script successfully starts Excel, opens the workbook T:\Results\test.xls and then saves it to another worksheet called T:\Results\newname.xls. Here is the working code:

' Read arguments passed from calling program
Set colNamedArguments = WScript.Arguments.Named
strDestFile = colNamedArguments.Item("DestFile")
strDestPath = colNamedArguments.Item("DestPath")
strDestSheet = colNamedArguments.Item("DestSheet")

'Open Excel
Set objXL = CreateObject("Excel.Application") 

'Make Excel visible
objXL.Visible=True

'Open workbook 
strPathFile = strDestPath & "\" & StrDestFile
Set objWb = objXL.Workbooks.Open(strPathfile)

'THE NEXT 2 LINES COMMENTED OUT BECAUSE THEY DON'T WORK
'Sheets(strDestSheet).Select
'Sheets("Sales2010").Select

'Save workbook to new file
objXL.ActiveWorkbook.SaveAs "T:\SASUsers\barnesl\Projects\newname.xls" 

'Close Excel
objXL.ActiveWorkbook.Close
objXL.Application.Quit

'msgbox "script completed"

My problem is with the two lines that are commented out. I want to select the sheet Sales2010 and make some changes to it before saving. But I can't even select it. The error I get is

Error: Type mismatch: 'Sheets'
Code: 800A000D
Source: Microsoft VBScript runtime error

Any ideas? Even though I've already opened the workbook, do I have to make it active before I can reference specific sheets?

Thanks,
Lisa


----------



## Jimmy the Hand (Jul 28, 2006)

Hello Lisa,

welcome to TSG Forums 

You don't have to activate the workbook before referencing sheets. But you do must activate it before selecting sheets, because you can only select some part of the active workbook, active worksheet, etc. Also, in most cases you must use the workbook qualifier (objWb, in your case), which is a good practive anyway. Try this code:

```
objWb.Activate
objWb.Worksheets(strDestSheet).Select
```
Changing a cell should work without activation:

```
objWb.Worksheets(strDestSheet).Range("A1") = 1
```
This latter should work even with Excel being invisible.

JImmy


----------



## peridot (Jan 28, 2011)

Thank you, Jimmy, it worked like a charm.

I don't know what I'd do without all the knowledgeable people out there willing to share what they know.

Lisa


----------

