CODEWITHSUNDEEP
excelvbscriptvba
Danny R
Danny R

Reputation: 11

VBScript to Open an Excel File and then do a Save As

I am trying to write a vba script to automatically open an excel file increase the day by one in a certain cell and then wait 2 minutes for all other field to populate information from the current day. Once the other field are populated I want the script to close the file and save as the day it ran the script. I am going to add this script to windows task manager to run daily at 2 am. so far I have something like this. 

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\Users\dr\Desktop\test.xlsx")

objExcel.Application.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(3, 2).Value = "=today()"

WScript.Sleep 120000

wb.SaveAs Format( Now(), “DD-MMM-YY”)

Upvotes: 1

Views: 47455

Answers (2)

iDevlop
iDevlop

Reputation: 25252

Not very sure to fully understand your question, but I already see one or 2 fixes:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\Users\dr\Desktop\test.xlsx")

objExcel.Application.Visible = True
'objExcel.Workbooks.Add
objWorkbook.Sheets("sheetName").Cells(3, 2).Value = "=today()"

WScript.Sleep 120000

objWorkbook.SaveAs "F" & format(date(), "yyyymmdd") & ".xlsm", 52

Anyway, changing the formula in B3 to "=Today()" everyday is totally useless, since it was already that same formula the day before.

Edit: if you are running this from an Excel VBA procedure, you don't need to create a new instance of Excel:

dim sFolder as string
sFolder = "c:\Users\dr\Desktop\"
Set objWorkbook = Workbooks.Open(sFolder & "test.xlsx")
objWorkbook.Sheets("sheetName").recalc
objWorkbook.SaveAs sFolder & format(date(), "yyyymmdd") & ".xlsx"
objWorkbook.Close

Upvotes: 2

Rik Sportel
Rik Sportel

Reputation: 2679

Your script needs to open the Workbook, then update the day, calculate ("populate the other cells"), save and close. My advice is to not use Sleep for this, but just force the Workbook to calculate and then close it. Like @iDevlop says: Changing the formula in B3 is unneccessary. The same goes for actually showing the Excel application.

The following opens the workbook, recalculates (so the B3 value will be today's date and the rest of the workbook will calculate accordingly), and saves:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("c:\Users\dr\Desktop\test.xlsx")
objExcel.Calculate
objWorkbook.SaveAs Format( Now(), “DD-MMM-YY”)

For info on .calculate see https://msdn.microsoft.com/EN-US/library/office/ff195517.aspx

Upvotes: 3

Related Questions