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

问题描述

VBScript 是否有可能在生成的 excel 文件中编写一个宏，该宏可以在打开文件时运行?

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?

我有一个 VBScript 来自动生成一个 excel 文件和其中的一些数据，它运行一次并生成包含所有数据/排序等的文件.

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.

如果是这样，这将如何实现，如何在 vbscript 中编写稍后运行的宏?

If so how would this be implemented, how can a macro to be run later be written in the vbscript?

推荐答案

很好的建议 div，是的，我可以从模板创建它，但在这种情况下，我需要更多的灵活性并与脚本中的变量集成.

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.

excel 对象模型对象 VBProject.VBComponents 的作用就像一个魅力，并且完全符合我的要求，为了其他人寻找类似答案的目的，我在查看 VBProject 对象时发现了这一点:

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	est.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	est.xls"
objExcel.Quit

来源:脚本专家将宏添加到 Excel 电子表格

创建excel宏的字符串

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