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

 

[NdNoviceHlp]

HTH. Dave
https://www.mrexcel.com/forum/excel...but-permission-denied.html?highlight=copyfile

 

[m_vishal_c]

HTH. Dave
https://www.mrexcel.com/forum/excel...but-permission-denied.html?highlight=copyfile

hi Thanks for replying. but I need to save as file with current date and time. please let me know how can I do with above code

thanks

 

[NdNoviceHlp]

Your destination file is unclear. HTH. Dave

Sub copythatfile()
Dim xlobj As Object, NowStr As String
Set xlobj = CreateObject("Scripting.FileSystemObject")
'object.copyfile,source,destination,file overright(True is default)
'source c:\abc\xyz.xls
'destination save as this xyz.xls file with date and time(like xyz_15_01_2018_15:45 PM)
NowStr = Now
xlobj.CopyFile "c:\abc\xyz.xls", "c:\abc\xyz.xls" & NowStr, True
Set xlobj = Nothing
End Sub

 

[m_vishal_c]

Your destination file is unclear. HTH. Dave

Sub copythatfile()
Dim xlobj As Object, NowStr As String
Set xlobj = CreateObject("Scripting.FileSystemObject")
'object.copyfile,source,destination,file overright(True is default)
'source c:\abc\xyz.xls
'destination save as this xyz.xls file with date and time(like xyz_15_01_2018_15:45 PM)
NowStr = Now
xlobj.CopyFile "c:\abc\xyz.xls", "c:\abc\xyz.xls" & NowStr, True
Set xlobj = Nothing
End Sub

hi . thanks for above code. I am using

xlobj.CopyFile "D:\[invoice format.xlsx]", "D:\[invoice format.xlsx]" & NowStr, True. file is there but it shows "file not found"

can you please help. Thanks

 

[NdNoviceHlp]

Why are U using square brackets? Does the file "D:\invoice format.xlsx" exist? Is it in a folder ie. "D:\YourFolderName\invoice format.xlsx" Run this sub in your source workbook and place it's exact output as the source file. HTH. Dave

Sub test()
MsgBox ThisWorkbook.FullName
End Sub

 

[m_vishal_c]

Why are U using square brackets? Does the file "D:\invoice format.xlsx" exist? Is it in a folder ie. "D:\YourFolderName\invoice format.xlsx" Run this sub in your source workbook and place it's exact output as the source file. HTH. Dave

Sub test()
MsgBox ThisWorkbook.FullName
End Sub

hi that file is in D drive, not under any folder. if I don't use [] then it shows Runtime error 52. Bad file name or number. File name is "invoice format" and here is a space between two word so I have to use []. can you please guide me.

I applied above code " MsgBox ThisWorkbook.FullName " . it gives me D:\invoice format.xlsx as file name

please guide me thanks

 

[NdNoviceHlp]

Stop using []'s This should work. The source file can be open the destination file must be closed or forced closed before the transfer. Dave

xlobj.CopyFile "D:\invoice format.xlsx", "c:\abc\xyz.xls" & NowStr, True

 

[m_vishal_c]

Stop using []'s This should work. The source file can be open the destination file must be closed or forced closed before the transfer. Dave

xlobj.CopyFile "D:\invoice format.xlsx", "c:\abc\xyz.xls" & NowStr, True

sorry mate, still it shows Runtime error 52. Bad file name or number
I am using xlobj.CopyFile "D:\invoice format.xlsx", "D:\D drive\temp\invoice format.xlsx" & NowStr, True. there is not any file in Temp folder (destination folder)

OR

I just twist my query. is it possible to save as automatically with file name with Date & time when I close that file
example:- I open d:\abc.xls file and want to save as "abc.xls & date & time" when I close this file

please guide me
thanks

 

[NdNoviceHlp]

"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