I'm using a program that saves output as an Excel file, and uses a
user-defined filename and location. Is there any way to use a default
filename that will add the date and time to the file name to make it unique?

The issue is that I want to save prior versions, but don't want to have to
constantly move them to new locations and/or change the default file name in
the program.

Thanks,

Chris

The difficulty here is the external program (the one that creates the
Excel file) apparently doesn't let you do this.

One solution I can think of would be to create a custom button that
resides permanently in Excel. Attach code to that button that saves
the current file to your desired file location with the time and
datestamp attached.

Thanks, Dave. It sounds like your solution would have to be run AFTER the
file was already saved, correct? That would not address my issue.

You are correct in that the third party program does not address the issue.

I was hoping for some filename convention that Excel could react to upon
saving from the third party program with a default filename, such as
"Filename[date][time].xls" as my default filename, but I don't think Excel
will convert that to actual date and time upon saving from this program.

Chris


"Dave O" wrote:

The difficulty here is the external program (the one that creates the
Excel file) apparently doesn't let you do this.

One solution I can think of would be to create a custom button that
resides permanently in Excel. Attach code to that button that saves
the current file to your desired file location with the time and
datestamp attached.

I'm not quite sure why running a program after the third party program finishes
doesn't work for you.

But if you want to reconsider, you could just use a macro that renames that
recently saved file. Your third party program saves the file in its standard
location. When it finishes, you execute a macro that renames that file using
the date/time.

Option Explicit
Sub testme()
Dim TestStr As String
Dim myFileName As String
Dim myExt As String

'don't include the extension here
myFileName = "C:\my documents\excel\book1"

'specify it here
myExt = ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName & myExt)
On Error GoTo 0

If TestStr = "" Then
MsgBox "File not found"
Exit Sub
End If

Name myFileName & myExt _
As myFileName & Format(Now, "yyyymmdd_hhmmss") & myExt

End Sub

You could put this into a workbook, then just open that workbook when you need
to do this. Then hit tools|macro and run the macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

opieandy wrote:

Thanks, Dave. It sounds like your solution would have to be run AFTER the
file was already saved, correct? That would not address my issue.

You are correct in that the third party program does not address the issue.

I was hoping for some filename convention that Excel could react to upon
saving from the third party program with a default filename, such as
"Filename[date][time].xls" as my default filename, but I don't think Excel
will convert that to actual date and time upon saving from this program.

Chris

"Dave O" wrote:

The difficulty here is the external program (the one that creates the
Excel file) apparently doesn't let you do this.

One solution I can think of would be to create a custom button that
resides permanently in Excel. Attach code to that button that saves
the current file to your desired file location with the time and
datestamp attached.



--

Dave Peterson

"I'm not quite sure why running a program after the third party program
finishes
doesn't work for you."

Thanks for trying to help. The whole issue is that I'm trying to avoid
taking an extra step when saving the file. As it is now, I can rename or
move the previously saved file before the new one overwrites it. Opening a
file and running a macro is as time-consuming, or moreso, than just renaming
or moving it myself in Explorer. I'm trying to automate the save process in
one step without having to also run a macro or move/rename an existing file.

Thanks,

Chris


"Dave Peterson" wrote:

I'm not quite sure why running a program after the third party program finishes
doesn't work for you.

But if you want to reconsider, you could just use a macro that renames that
recently saved file. Your third party program saves the file in its standard
location. When it finishes, you execute a macro that renames that file using
the date/time.

Option Explicit
Sub testme()
Dim TestStr As String
Dim myFileName As String
Dim myExt As String

'don't include the extension here
myFileName = "C:\my documents\excel\book1"

'specify it here
myExt = ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName & myExt)
On Error GoTo 0

If TestStr = "" Then
MsgBox "File not found"
Exit Sub
End If

Name myFileName & myExt _
As myFileName & Format(Now, "yyyymmdd_hhmmss") & myExt

End Sub

You could put this into a workbook, then just open that workbook when you need
to do this. Then hit tools|macro and run the macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

opieandy wrote:

Thanks, Dave. It sounds like your solution would have to be run AFTER the
file was already saved, correct? That would not address my issue.

You are correct in that the third party program does not address the issue.

I was hoping for some filename convention that Excel could react to upon
saving from the third party program with a default filename, such as
"Filename[date][time].xls" as my default filename, but I don't think Excel
will convert that to actual date and time upon saving from this program.

Chris

"Dave O" wrote:

The difficulty here is the external program (the one that creates the
Excel file) apparently doesn't let you do this.

One solution I can think of would be to create a custom button that
resides permanently in Excel. Attach code to that button that saves
the current file to your desired file location with the time and
datestamp attached.



--

Dave Peterson