CODEWITHSUNDEEP
excelvba
Alok
Alok

Reputation: 509

Workbooks.Open refers to XLA instead of XLSM in VBScript

I am trying to open .xlsm file in VBScript. I also have one 3rd party add-in installed (i.e. Quality.xla file which is automatically loaded when I open my Sales file. I want that add-in (Quality.xla) and unloading the add-in is not a solution.

When I execute the below code

Dim xlApp, xlBook
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True
xlApp.DisplayAlerts = False
xlApp.AlertBeforeOverwriting = False
Set xlBook = xlApp.Workbooks.Open("D:\Sales.xlsm")
WScript.Echo xlBook.Name

and check for

xlBook.Name

it returns name of the Add-in instead of Sales.xlsm file. Is there anyway I can still get xlbook to refer to Sales.xlsm as I need to refer to other sheets in that Excel workbook.

Thanks

Upvotes: 0

Views: 124

Answers (1)

CLR
CLR

Reputation: 12279

I can't see anything wrong with your code. I can't recreate the issue either. There's no logic to the script returning the addin's name.

On the off-chance though that this is a real issue, I came up with the following workaround for it which should produce the right result :

Dim xlApp, xlBook, xlWb
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True
xlApp.DisplayAlerts = False
xlApp.AlertBeforeOverwriting = False
xlApp.Workbooks.Open ("D:\Sales.xlsm")
For Each xlWb In xlApp.Workbooks
  If xlWb.Name = "Sales.xlsm" Then Set xlBook = xlWb
Next
WScript.Echo xlBook.Name

That said, I really wouldn't recommend using it unless you've absolutely no other choice. Perhaps just use it to help you debug the issue you're seeing.

Upvotes: 1

Related Questions