如何使用 .vbs 文件以电子表格中指定的名称保存文件 [英] How to save a file with a name which is specified in a spreadsheet, using a .vbs file

问题描述

我正在执行一些 .vbs 编码，我想在其中打开现有模板文件，对其进行一些更改，然后使用单元格 B2 中指定的文件名另存为".

I'm doing some .vbs coding in which I want to open an existing template file, make some changes to it, and then 'save as' with the filename specified in cell B2.

Set objExcel = CreateObject("Excel.Application")

FileDirectory = "C:\Users\j128\Desktop\"
FileName = "TEMPLATE.xlsx"

Set objWorkbook = objExcel.Workbooks.Open(FileDirectory+FileName)

objExcel.Visible = True
objExcel.Cells(2,2).value = "Variable" 'Changes will occur in this section

ActiveWorkbook.SaveAs Filename = cells(2,2).value 'This is where problems occur

我意识到我已经可以使用宏来使用单元格中指定的值命名 Excel 文件.但是，我打算使用这个 .vbs 文件来使用电子表格中指定的值命名其他文档类型.

I realise I can already use Macros to name an Excel file with the value specified in a cell. However, I'm planning on using this .vbs file to name other document types using values specified in a spreadsheet.

感谢您的帮助！

显然这是不可能的:如何使用 VBScript 中的通用另存为对话框?

我也尝试过使用 sendkeys 来选择另存为"，尽管在命名新文件时它会崩溃.

I've also tried using sendkeys to select 'Save as', though it falls apart when coming to name the new file.

推荐答案

感谢您的回复.我会研究一下.

Thank you for the response. I'll take a look into that.

我尝试过的另一件事是简单地复制并重命名文件:

Another thing I have tried is to simply copy and rename the file:

Set WSHShell = CreateObject("Wscript.Shell")
CurrentDirectory = WSHShell.CurrentDirectory & "\"

Template = "TEMPLATE.xlsx"
NewFile = "New File.xlsx"

Set objFSO = CreateObject("Scripting.FileSystemObject")
' First parameter: original location&file
' Second parameter: new location&file
objFSO.CopyFile CurrentDirectory&Template, CurrentDirectory&NewFile

我现在需要的是能够动态设置NewFile"等于另一个电子表格中的单元格.

What I need now is to be able to dynamically set "NewFile" equal to a cell in another spreadsheet.

显然这样的事情将允许我在其他地方的单元格中指定NewFile"输入:

Apparently something like this will allow me to specify the 'NewFile' input in a cell somewhere else:

value1 = xlSht.Cells(2, 1) 'Example of how to specify a value as equal to the value in cell A2

请注意，'xlSht' 会在之前指定.

Note that 'xlSht' would have been specified earlier.

这篇关于如何使用 .vbs 文件以电子表格中指定的名称保存文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！