• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 21
  • Last Modified:

Save As with a dynamic date parameter

Hi!  I'm trying to create a batch file that opens an Excel file (successfully created in an earlier section of the batch) and Saves As a new dynamic name with the current date so that it doesn't get overwritten the next day.

I can get the file to open and close, but it's not executing the Save As. Any feedback would be greatly appreciated

set ObjExcel = CreateObject("Excel.Application")
set ObjWorkbook = ObjExcel.Workbooks.Open("\\FILELOCATION\FILENAME_.xlsx")
objExcel.Application.Visible = True

objWorkbook.SaveAs "FILELOCATION\FILENAME_" & Format(Date, "yyyymmdd") & ".xlsx"

objWorkbook.Close
objExcel.Quit

set ObjExcel = nothing
set ObjWorkbook = nothing

WScript.Quit
0
Trinni Schreier
Asked:
Trinni Schreier
  • 2
  • 2
  • 2
2 Solutions
 
Rgonzo1971Commented:
Hi,
pls try
objWorkbook.SaveAs "FILELOCATION\FILENAME_" & Year(Date) & Right("0" & Month(Date),2)  & Right("0" & Day(Date),2)  & ".xlsx"

Open in new window

Regards
0
 
NorieVBA ExpertCommented:
You appear to be missing the \\ at the start of the path, try this.
objWorkbook.SaveAs "\\FILELOCATION\FILENAME_" & Format(Date, "yyyymmdd") & ".xlsx"

Open in new window


Or perhaps this which uses the path from the workbook being opened and appends the date to the filename.
With objWorkbook
    .SaveAs Replace(.FullName, ".xlsx", Format(Date, "yyyymmdd") & ".xlsx"), .FileFormat
End With

Open in new window

0
 
Rgonzo1971Commented:
AFAIK Format is not a VBS function
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NorieVBA ExpertCommented:
Rgonzo

I think you are right, suppose I'm just use to, incorrectly of course, using VBA functions in VBS.
0
 
Trinni SchreierAuthor Commented:
Thank you all for your feedback! @Rgonzo's syntax with plus @Norie's 2nd  recommendation works like a charm!
0
 
Trinni SchreierAuthor Commented:
Thank you again!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now