Save as xls file with date and time end of same file name from different location

[m_vishal_c]

Hi i need vba code which can save as xls file with same name + date and time but from different location without opening file.

For example:
there is 1 file c:\abc\xyz.xls
i want to save as this xyz.xls file with date and time(like xyz_15_01_2018_15:45 PM) from different location (from different xls file) OR is it possible when i close xyz.xls file , it will save as automatically in abc folder

is there anyone can help me. many thanks in advance

 

[m_vishal_c]

"D:\D drive\temp\invoice format.xlsx" should be...

"D:\temp\invoice format.xlsx"

U can use the save as method...

Dim NowStr as String
NowStr = Now
ActiveWorkbook.SaveAs  "d:\abc.xls " & NowStr, fileformat:=52

Dave

hi mate, sorry still same error "Runtime error 52. Bad file name or number "

Even that second code does not work. I applied code in module but not working. if I am mistaken then please let me know where I should I apply this code

thanks

 

[NdNoviceHlp]

My apologies for all the frustration. It seemed fairly strait forward. To start with the Now string has unacceptable characters and needs to be formatted. The second problem was that the code was adding the Now string after the file extension... duh. Anyways, here's some code that has been tested and works. Dave

Sub copyfl2()
Dim TempObj As Object, FileStr As String, NowStr As String
'On Error Resume Next
FileStr = "D:\invoice format.xlsx"
Set xlobj = CreateObject("Scripting.FileSystemObject")
If xlobj.fileexists(FileStr) Then
MsgBox "File " & FileStr & " exists"
End If
Set TempObj = xlobj.getfile(FileStr)
If TempObj <> "" Then
MsgBox "File " & TempObj.Path & " is available"
End If

NowStr = Format(Now, "yyyy/mm/dd.Hh.Nn.ss ")
'source,destination,save
xlobj.CopyFile FileStr, Left(FileStr, Len(FileStr) - 5) & NowStr & ".xlsm", True
'xlobj.CopyFile "D:\invoice format.xlsx", "D:\invoice format" & NowStr & ".xlsx", True
'ActiveWorkbook.SaveAs "d:\abc" & NowStr & ".xls ", FileFormat:=52
Set TempObj = Nothing
Set xlobj = Nothing
End Sub

 

[m_vishal_c]

My apologies for all the frustration. It seemed fairly strait forward. To start with the Now string has unacceptable characters and needs to be formatted. The second problem was that the code was adding the Now string after the file extension... duh. Anyways, here's some code that has been tested and works. Dave

Sub copyfl2()
Dim TempObj As Object, FileStr As String, NowStr As String
'On Error Resume Next
FileStr = "D:\invoice format.xlsx"
Set xlobj = CreateObject("Scripting.FileSystemObject")
If xlobj.fileexists(FileStr) Then
MsgBox "File " & FileStr & " exists"
End If
Set TempObj = xlobj.getfile(FileStr)
If TempObj <> "" Then
MsgBox "File " & TempObj.Path & " is available"
End If

NowStr = Format(Now, "yyyy/mm/dd.Hh.Nn.ss ")
'source,destination,save
xlobj.CopyFile FileStr, Left(FileStr, Len(FileStr) - 5) & NowStr & ".xlsm", True
'xlobj.CopyFile "D:\invoice format.xlsx", "D:\invoice format" & NowStr & ".xlsx", True
'ActiveWorkbook.SaveAs "d:\abc" & NowStr & ".xls ", FileFormat:=52
Set TempObj = Nothing
Set xlobj = Nothing
End Sub

hi its okay. sometimes it happens. heaps thanks, now it works. but when I open that "saved as" file it shows me " The file format and extension of "invoice format2018-01-19.08.24.33.xls" don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway? then three button yes, No, Help. "
this is bit annoying. it opens when I press yes button. can you please fix this msg.

And

is it possible to save as automatically with file name with Date & time when I close that file
example:- I open d:\invoice format.xls file and want to save as "invoice format.xls & date & time" when I close this file by applying code in invoice format.xls file

please guide me

 

[NdNoviceHlp]

Unfortunately the user has to go to the excel options>trustcentre>macros>enable all macros in order to get rid of the warning. See the above code to save the open workbook...

ActiveWorkbook.SaveAs "d:\abc" & NowStr & ".xls ", FileFormat:=52

Adjust file path to suit. Dave

 

[Fluff]

@NdNoviceHlp
FileFormat 52 is an xlsm file format, to save as an xls file you need 56 if the code is running in xl 2007 or newer.

 

[m_vishal_c]

@NdNoviceHlp
FileFormat 52 is an xlsm file format, to save as an xls file you need 56 if the code is running in xl 2007 or newer.

Heaps thanks, all good for now

thanks