将Excel VBA代码插入并执行从外部源接收的电子表格 [英] Inject and execute Excel VBA code into spreadsheet received from external source

问题描述

我想以编程方式（在vbscript或命令行或其他方面）将VBA代码注入到从FTP站点获取的工作簿中，然后在工作簿上执行宏。全自动化没有用户交互。

我有一个文本文件中定义的VBA代码。

解决方案

没有机会尝试这个，但我认为这将做我需要的。

 设置objExcel = CreateObject（Excel.Application）
 objExcel.Visible = True 
 objExcel。 DisplayAlerts = False 
设置objWorkbook = objExcel.Workbooks.Open（C：\scripts\test.xls）
设置xlmodule = objworkbook.VBProject.VBComponents.Add（1）
 strCode = _ 
sub test（）& vbCr& _ 
msgbox宏内& vbCr& _ 
end sub
 xlmodule.CodeModule.AddFromString strCode 
 objWorkbook.SaveAsc：\scripts\test.xls
 objExcel.Quit 
  

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.

解决方案

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 

这篇关于将Excel VBA代码插入并执行从外部源接收的电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！