Run Macro on CSV file that is stored in another file

Question

I have a CSV file that I receive daily that I format using a macro that is stored in my personal macro file. I am trying to create a vbs file that will run the macro on the CSV file via a scheduled task, but I am not having an luck. Here is the vbs file:

Option Explicit

ExcelMacroExample

Sub ExcelMacroExample() 
  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open("c:\users\dmcgettigan\desktop\test.csv", 0, True) 
  xlApp.Run "c:\users\dmcgettigan\desktop\Personal.xlsb!newsales"
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 
End Sub

This is the error that I am getting:

C:\Users\dmcgettigan\Desktop\test.vbs(13, 3) Microsoft Excel: Cannot run the macro 'c:\users\dmcgettigan\desktop\Personal.xlsb!new sales'. The macro may not be available in this workbook or all macros may be disabled.

Any ideas on what I am doing wrong?

Storax Storax · Accepted Answer · 2018-10-02T12:11:25

For me it's working like that

  Dim xlApp 

  Set xlApp = CreateObject("Excel.Application") 
  xlapp.workbooks.open("<Path to your PERSONAL.XLSB>")
  xlApp.Run "PERSONAL.XLSB!Test"  
  xlApp.Quit

The comment is also right, you can't have a blank in the name of the sub you want to run.

Update Without being able to test it your code should look like this

Option Explicit

ExcelMacroExample

Sub ExcelMacroExample() 
  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open("c:\users\dmcgettigan\desktop\test.csv", 0, True) 
  xlapp.workbooks.open("c:\users\dmcgettigan\desktop\Personal.xlsb")
  xlApp.Run "Personal.xlsb!newsales"
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 
End Sub

Run Macro on CSV file that is stored in another file

1 Answers