0

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")

  • Could you take the information from the web and place it directly in a copy of a template .xlsm that contains the macros? This would avoid creating the .csv and avoid making a duplicate copy. – Raystafarian Sep 9 '14 at 11:47
  • I'm not sure that I follow. Take the information from the web? – root Sep 9 '14 at 13:07
  • I meant your DB, sorry – Raystafarian Sep 9 '14 at 13:08
  • This sounds similar to what we've been mentioning in other comments - use another spreadsheet as a holder for the macros, then execute them on the sheet with the data. I'm fine with doing this, although its still unclear how to. – root Sep 9 '14 at 13:22
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.

0

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
  • I don't mind using PowerShell in this particular case, since the goal is to have the data automatically formatted, although I'm not sure I fully understand what this is doing and how it works. How I could tailor this to handle long Macros? – root Sep 8 '14 at 13:44
  • @root You cannot 1:1 convert every VBA command to PowerShell. Can we see the whole VBA macro? – nixda Sep 8 '14 at 15:32
  • Then my next thought would be to create a spreadsheet (C:\macro.xls) that contains the macro, have the vbs pull the data and save it to C:\file.csv, then have PowerShell open both worksheets and run macro.xls' macro on Sheet1 of file.csv – root Sep 8 '14 at 15:40
  • @root But in the comment above you stated that you want to avoid a template file. Isn't that similar to a macro.xls? – nixda Sep 8 '14 at 15:44
  • Not if, as you mentioned, the syntax may differ. This series of macros is hundreds of lines long. – root Sep 8 '14 at 15:53
-1

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.

  • That sounds very similar to what I'm doing now, and doesn't answer the question. I understand the approach, but I'd imagine there is a way to include the vba to the vbs to avoid needing a template or second file at all. – root Sep 5 '14 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.