Visual Basic script to open Excel workbook to specific worksheet with add-ins enabled

2
votes

Working on a Windows 7 Enterprise x64 host with 64-bit Microsoft Office Professional Plus 2016 installed.

I'd like to create Visual Basic script (a file on my Windows desktop) that launches Microsoft Excel, opens the macro-enabled Excel workbook file "Workbook.xlsm", and selects/activates the worksheet "XYZ" within that workbook. So, I woite the following VB script:

' File: test.vbs
' Opens Excel workbook "Workbook.xlsm" to worksheet "XYZ"
Set objXl = CreateObject("Excel.Application")
Set ObjWb = objXl.Workbooks.Open("C:\Path\To\Workbook.xlsm")
objXl.Goto ObjWb.Sheets("XYZ").Range("A1")
objXl.Application.Visible = True

This script successfully launches excel, opens the specified workbook, and selects the specified worksheet.

However, all worksheet cells containing a formula that invokes one or more functions defined within an add-in module are all broken; these cells all contain the error value #NAME?.

If I manually close and reopen the workbook (not using the script), the worksheet cells containing a formula that invokes one or more functions defined within an add-in module all work correctly.

So I'm wondering how to modify my Visual Basic script so that after it launches Excel, and opens the workbook, and selects the desired worksheet, the workbook's worksheet cells can successfully invoke functions defined in add-in modules.

1
excelvbscript
In the second last line, shouldn't you write ObjWb.Sheets instead of ObjWbSheets? – Gurmanjot Singh
When you lauch Excel using CreateObject any add-ins are not loaded - see there for more information: support.microsoft.com/en-us/help/213489/… – Tim Williams
Thanks for pointing out that typo @Gurman. (FWIW, that typo isn't present in my actual VB script source file.) – Jim Fischer

1 Answers

3
votes

As per Tim Williams' comment, this article from Microsoft describes this issue and explains how to solve it:

Add-ins do not load when using the CreateObject command in Excel

' File: test.vbs
' Opens Excel workbook "Workbook.xlsm" to worksheet "XYZ"
Set objXL = CreateObject("Excel.Application")
Set ObjWB = objXL.Workbooks.Open("C:\Path\To\Workbook.xlsm")

' Open add-in file 'AddIn.xlam'
objXL.Workbooks.Open ("C:\Path\To\AddIn.xlam")

objXL.Goto ObjWB.Sheets("XYZ").Range("A1")
objXL.Application.Visible = True

' Maximize the window after it opens
objXL.Application.WindowState = xlMaximized

Set objWB = Nothing
Set objXL = Nothing