How does this work - How does the vbs script know it should open the file?

user_4884148 0
Viewed 55

I needed a vbs script that opens a .xlsm file, runs a macro in it and closes the file again.

Online I found a script that works perfectly. Trouble is, I don't understand how.

This is the script :

Sub MacroExcel() 

  Dim ExcelApp 
  Dim ExcelFile


  Set ExcelApp = CreateObject("Excel.Application") 
  Set ExcelFile = ExcelApp.Workbooks.Open("D:\File1.xlsm") 
  
  ExcelApp.Visible = True   
  ExcelApp.Run "Macro1" 
  ExcelApp.Quit 

  Set ExcelFile = Nothing 
  Set ExcelApp = Nothing 

End Sub

How does this open the file in the first place? As I understand, this line :

Set ExcelFile = ExcelApp.Workbooks.Open("D:\File1.xlsm")

assigns a method to the variable ExcelFile. But then, ExcelFile is not used in the code anymore.

How does the script know that the file should actually be opened ?

vbscript
1 Answers

As I understand, this line :

Set ExcelFile = ExcelApp.Workbooks.Open("D:\File1.xlsm")

assigns a method to the variable ExcelFile

That is where you understand it wrong. It doesn't assign a method to the variable ExcelFile, it assigns the result of executing the method ExcelApp.Workbooks.Open("D:\File1.xlsm")

You see, when you ask Excel to open a file, it not only opens the actual file in Excel, it also returns an object of type Workbook
It's this workbook object that is stored in the variable ExcelFile

Now you are entirely correct that in this snippet the actual workbook object is not used. So instead of assigning the result to a variable, they could have also executed the method and ignored the resulting object like this.

ExcelApp.Workbooks.Open "D:\File1.xlsm"
user_20181330
Related