0

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

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.

Thanks for any help!

Apparently it might not be possible: How can I use the common Save As dialog from VBScript?

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

Community
  • 1
  • 1
j128
  • 51
  • 6

2 Answers2

0

If you don't want to actually show the Save As dialog and just want to save the active file as a specific file name, look into the Workbook.SaveCopyAs() function. That might work for you.

NYCdotNet
  • 4,500
  • 1
  • 25
  • 27
0

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

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

EDIT: 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

Note that 'xlSht' would have been specified earlier.

j128
  • 51
  • 6