VBScript在Excel文件中编写一个宏 [英] VBScript to Write a Macro within an Excel file

问题描述

如果生成的文件被打开，VBScript可以在Excel文件中写入一个宏？

Ive got一个VBScript自动生成一个excel文件和一些数据，它运行一次，并生成所有数据/排序等文件。

我需要的是拥有一些交互性，通常在程序运行时运行宏，有人查看数据，问题在于这是一个自动生成的文件，没有宏被保存。

如果是这样实现的话，稍后再运行一个宏如何写入vbscript？

解决方案

建议divo，是的，我可以从一个模板创建它，但它是一个情况下，我需要更多的灵活性和脚本中的变量集成。

excel对象模型对象VBProject.VBComponent的工作方式就像一个魅力，正是我正在寻找的，因为t他的任何人寻找类似答案的目的，我发现这一点，同时查看VBProject对象：

  Set 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 
 <来源：
 脚本专家将一个宏添加到Excel电子表格 
 
 
 
 创建excel宏的String  
 
Is it possible for the VBScript to write a Macro within the excel file when it is generated that would be able to run whenever the file is opened?

Ive got a VBScript to auto generate an excel file and some data within it, this runs once and generates the file with all the data/sorts etc.

What I need is to have some interactivity that would normally be run with a macro whilst the program is running and someone views the data, problem here being as it is an auto generated file, no macros are saved.

If so how would this be implemented, how can a macro to be run later be written in the vbscript?
解决方案
Great advice divo, yes I could create it from a template but it is a case where I would need more flexability and integration with variables in the script.

The excel object model object VBProject.VBComponents works like a charm and does exactly what I was looking for, for the purpose of anyone else looking for a similar answer I found this while looking into the VBProject object:
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
Sources: 
Scripting Guy Add a Macro to an Excel Spreadsheet

String for excel macro creation

                        
这篇关于VBScript在Excel文件中编写一个宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！