Save as New file each time macro is ran in VBA

[1229]

I'm working on a macro in VBA with Excel.
My objective is to have the macro automatically save the new file the macro just created. Each time the macro runs I want the filename to change.

For example,
I run the macro and it saves the file as C:\7-1-01.xls.
Tommorrow I run the macro and I want it to save to
C:\7-2-01.xls.
The next day I run the macro and I want it to save to
C:\7-3-01.xls.

How do I get the macro to save as and change the filename automatically?

 

[MichaelRed]

If the 'file names' are as simplistic as the examples, just use the format function to generate the variable portion and append the ".Xls" to the string and prepend the drive (optionally path info).

MyFilName = "C:\" & Format(Now, "m-d-yy&quot & ".Xls"
? MyFilName
C:\7-11-01.Xls
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[1229]

I tried
MyFilName = "C:\" & Format(Now, "m-d-yy&quot & ".Xls"
? MyFilName
C:\7-11-01.Xls

I'm still getting an error message that says "Wrong number of arguments or invalid property assignment"

Is there another way to do this?

 

[MichaelRed]

ther must be a problem with the macro code other than the actual file name. Post the part of the code which generates the file name and uses it to save the file.
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[1229]

Here is my code....PLEASE HELP (if possible)

Let me give you some background information.
1. I create a new blank workbook everytime this macro runs.
2. I do a SaveAs command (see below) in the beginning of my macro so I can identify this workbook throughout the macro. (Here's where I think the problem is...this is where I want the filename to change to the current date.)
3. After the SaveAs, I do more formatting.
4. At the end of the macro, I want to save the file again.

at the beginning of my macro......

ChDir "F:\rrk\Performances\Corp to ECW"
ActiveWorkbook.SaveAs FileName:="F:\rrk\Performances\Corp to ECW\7-10.xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

at the end of the code.........


ChDir "F:\rrk\Performances\Corp to ECW"
ActiveWorkbook.SaveAs

 

[MichaelRed]

the only issue i see is the last saveas should just be save as the current workbook (activeWorkbook) has already been named in the opening. Further, IF you intend to do the saveas, you need to supply the name argument. That will probably instasntiate the dialog re overwritting the existing.

I'm not a big Excel user, but it would apear that you can always refer to the activeworkbook object, thus needing no 'Name' until you are done. I would move the code at teh beginning where you save the workbook to the end as replacement for the save as stuff there. That probably requires some review and rework of the code to refer tot he activeworkbook, as opposed to the named object, but overall should be a 'cleaner' soloution.

This does NOT apprar to have any relevance to the original topic of the post. Further, you do not say what the problem / issue is, e.g. what error or anamolous result occur from the code as posted?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over