3

I would like to programatically (in vbscript or command line or whatever) inject VBA code into a workbook I get from an FTP site and then execute the macro on the workbook. All automated. No user interaction.

I have the VBA code defined in a text file.

Community
  • 1
  • 1
Joe
  • 283
  • 4
  • 10
  • The question is a bit unclear. Do you want to have a macro (say, in another workbook or add-in) that inserts the code, or do you not know how to access the VBA editor (Alt-F11 on Windows). – Tim Mayes Feb 15 '12 at 21:25
  • I have a macro defined in a text file. I would like to progromatically (in vbscript or command line or whatever) inject the macro into a workbook i get from an FTP site and then execute the macro on the workbook. All automated. No user interaction – Joe Feb 15 '12 at 21:32
  • 1
    Do you need to execute the macro just once (then consider to automate the task using VBScript) or must it be a part of the .xls to be executed more than once later (then start your research from [Application.]VBE). – Ekkehard.Horner Feb 15 '12 at 22:12
  • Just once. Essentially it is a file transform then the results is uploaded to a system and it is done. – Joe Feb 15 '12 at 22:47
  • do you want to run the macro from within excel at all? Or do you want the final result of the transformed file? You could inject a module and procedure into an excel file, but if you don't want to open excel there is no reason. And if you don't mind being in excel, it is easier to just have a macro already available in your personal.xls. Which version of spreadsheet? – datatoo Feb 15 '12 at 23:22

1 Answers1

3

Haven't had a chance to try this yet but i think this will do what i need. 

Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 
objExcel.DisplayAlerts = False 
Set  objWorkbook = objExcel.Workbooks.Open("C:\scripts\test.xls") 
   Set xlmodule = objworkbook.VBProject.VBComponents.Add(1)  
   strCode = _ 
   "sub test()" & vbCr & _ 
   "   msgbox ""Inside the macro"" " & vbCr & _ 
   "end sub" 
   xlmodule.CodeModule.AddFromString strCode 
objWorkbook.SaveAs "c:\scripts\test.xls" 
objExcel.Quit
Joe
  • 283
  • 4
  • 10