Run excel macros (vba) from vbscript file

Question

I have a vbs file that pulls information from my database and exports the data to an Excel file (C:\file.csv). Once this is done, I run a series of macros (vba) to format this data. These macros are always the same, I normally copy/paste them over from a text file into the spreadsheet and then manually run them.

Is there a way I can include the macro vba to the vbs file so, after the data is exported to the spreadsheet, the macros run? Similar solutions show how to call a pre-defined macro in an Excel file, where I want the contents of the macro to be saved/executed in/from my vbs file. This will be helpful since the vbs is creating a brand new file, then would run formatting macros.

My thought is I'll need to use Set objWorkbook = objExcel.Workbooks.Open("C:\file.csv")

Answer 1

You are on the right track. What you need first is to set up a new variable for the Excel.Application using late binding. It's under that object where all the normal excel VBA goodies hide. Below is an example vbs script that will open a new workbook, add a new sheet, name it "TEST", and add some content into cell A1 of that new sheet. When it's done, vbs will echo "Finished"

Sub createWB()

  'Set the excel application variable
  set xlApp = CreateObject("Excel.Application")

  'Make it visible, or hide it in the background
  xlApp.Application.Visible = true

  'Open a new workbook, for instance
  set xlWB = xlApp.Workbooks.Add()
  xlWB.Activate

  'Add a sheet, name it test, bring it front and center
  set xlSheet = xlWB.Worksheets.Add()
  xlSheet.Name = "TEST"
  xlSheet.Activate

  'Put something in cell A1
  xlSheet.Cells(1,1).value = "TEST CELL CONTENT"

End Sub

'Call the subroutine above
call createWB

WScript.Echo "Finished."
WScript.Quit

You may have to monkey around a bit with your existing VBA code to make vbs happy, but you'll find that writing your macros in vbs, is just as functional as writing them in vba.

Answer 2

Some time ago I tried this for several other SU answers. The only way I've found is to use PowerShell instead of VBScript. PowerShell can execute every VBA command. Only the syntax can differ.

Your VBA example as PowerShell script

$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Open("C:\file.csv")
$excel.visible = $true

Answer 3

I would put the macros into an excel template. I would then create an autonew macro in the template to import the csv file and run the formatting macros.

Then I would get the vbs to create and save the csv file and open an excel file based on the template.