Save As, using a string variable in the file name

[lsteffen]

Hi again,

I am trying to save a txt file created from an excel file with a unique name each time the macro code is run. What I did was to make cell A1 on Sheet 2 = NOW() for a date/time stamp and formatted that cell so that there is no spaces between the date and time: mm/dd/yyyhh:mm:ss. However, my code will not allow me to rename the file created to have this unique filename. Here is that portion of my code.

'//Rename exported file with unique name for LT
Dim LTName As String
Workbooks("WagnerExport.xls").Activate
ActiveWorkbook.Sheets("Sheet2").Activate
Range("A1").Activate
LTName = ActiveCell.Value & "CON_608250_020911_154750.txt"
Workbooks.Open Filename:="C:\" & "CON_608250_020911_154750.txt"
ActiveWorkbook.SaveAs Filename:=LTName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="password", ReadOnlyRecommended:=True _
, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

Any help is greatly appreciated.

Thank you,
Liz

 

[patrickmuldoon99]

You cannot include a ':' in the filename! Excel should be throwing an error but as you are using a macro you may not notice it.

remove the :'s and it should work, though there may be further bugs. That is just the first thing that occurred to me

 

[Greg Truby]

You don't need a cell. And you can't use colons in a filename. Just do sumpin' like:

LTName = format(now,"yyyymmdd hhmmss ") & "CON_608250_020911_154750.txt"

 

[lsteffen]

I tried this: but it still doesn't rename the file or give me errors...

'//Rename exported file with unique name for LT
Dim LTName As String
LTName = Format(Now, "yyyymmddhhmmss") & "CON_608250_020911_154750.txt"
Workbooks.Open Filename:="C:\" & "CON_608250_020911_154750.txt"
Workbooks("CON_608250_020911_154750.txt").Activate
ActiveWorkbook.SaveAs Filename:=LTName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="password", ReadOnlyRecommended:=True _
, CreateBackup:=False
'Application.DisplayAlerts = False
ActiveWorkbook.Close
'Application.DisplayAlerts = True

Any other help???

Thanks again,
Liz

 

[lsteffen]

I got it, had to specify where the file should go.

Thank you everyone for your help!

Liz