SaveCopyAs file with date and Time

Drumaster

New Member
Joined
Feb 28, 2011
Messages
25
how dow you save a file with date and time.
this is my code this work but if i add Time i get error

Code:
ActiveWorkbook.SaveCopyAs "D:\Colpitt\Machine\Data\Alarms " & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & ".xls"

Here with Time in it and i get a error
Code:
ctiveWorkbook.SaveCopyAs "D:\Colpitt\Machine\Data\Alarms " & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & "-" & Time (Now) & ".xls"
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Drumaster

New Member
Joined
Feb 28, 2011
Messages
25
Try using:

Format(Time, "hhmmss")

okay thank you, this work so you always have to place a format before it for the time ?. another question my file name is now 8-3-2011 -110683
is it also possible to save like 11:06:83. i try Format(Time, "hh:mm:ss") but that doenst work :)
i try
Rich (BB code):
Format(Time, "hh:mm:ss")
Format(Time, "hh:mm:ss;@")
Format(Time, "hh/mm/ss")
Format(Time, "hh;mm;ss")
Best regards
Carlo
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Carlo

You can't have colons in filenames I'm afraid, so you need another way of separating the time up:

Code:
Format(Now,"dd-mm-yyyy hh_mm_ss")

for example.
 

Drumaster

New Member
Joined
Feb 28, 2011
Messages
25

ADVERTISEMENT

Hi Carlo

You can't have colons in filenames I'm afraid, so you need another way of separating the time up:

Rich (BB code):
Format(Now,"dd-mm-yyyy hh_mm_ss")

for example.

aaah okay lol that i was using that long text instead you cloud use Format(Now,"dd-mm-yyyy hh_mm_ss") for the same result.

my last final question i put my code together and found something that will not work. because of the time is there an option like open last save file.

Rich (BB code):
'Save my data to D:\.......
    ActiveWorkbook.SaveCopyAs "D:\Colpitt machine data\Alarms " & Format(Now, "dd-mm-yyyy hh_mm_ss") & ".xls"
'Open my data so i can see my marco
    Workbooks.Open "D:\Colpitt machine data\Alarms " & Format(Now, "dd-mm-yyyy hh_mm_ss") & ".xls"'
' close file without changes in original file 
Workbooks("AlarmLog.XLS").Close SaveChanges:=False

I thought first to save it without the sec. but when you open a file with the time 11:59:59 i will not open because i will search voor the file name 12:00:00.
is there code that you can say open last save file in D:\......
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

The following function will return the latest file name in the supplied directory:

Code:
Function LatestFile(strDir As String) As String
Dim temp
Dim strfile As String
strfile = Dir(strDir)
If strfile <> "" Then
    temp = strfile
    strfile = Dir
    Do While strfile <> ""
        If FileDateTime(strDir & "\" & strfile) > FileDateTime(strDir & "\" & temp) Then temp = strfile
        strfile = Dir
    Loop
End If
LatestFile = temp
End Function

Use like:

Code:
Dim lastestFilename As String
 
latestFilename = LatestFile("C:\")
 
If latestFileName<>"" Then  'returns empty string if no file in directory!
  Workbooks.Open latestFilename
End If
 

Drumaster

New Member
Joined
Feb 28, 2011
Messages
25

ADVERTISEMENT

Hi

The following function will return the latest file name in the supplied directory:

Code:
Function LatestFile(strDir As String) As String
Dim temp
Dim strfile As String
strfile = Dir(strDir)
If strfile <> "" Then
    temp = strfile
    strfile = Dir
    Do While strfile <> ""
        If FileDateTime(strDir & "\" & strfile) > FileDateTime(strDir & "\" & temp) Then temp = strfile
        strfile = Dir
    Loop
End If
LatestFile = temp
End Function

Use like:

Code:
Dim lastestFilename As String
 
latestFilename = LatestFile("C:\")
 
If latestFileName<>"" Then  'returns empty string if no file in directory!
  Workbooks.Open latestFilename
End If

Hi Richard Schollar,

I never work with a function before i'm little bit new with VBA

Where do you place your function in you sub A () and end sub :S

Best regards

Carlo
 

Drumaster

New Member
Joined
Feb 28, 2011
Messages
25
Richard,

i try the code but i get error

The error
'Alarms 08-03-2011 144122.xls could not be found. check the spelling of the file name, verify that the file location is correct.

if you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted


This is the code

Code:
ActiveWorkbook.SaveCopyAs "D:\Colpitt machine data\Alarms " & Format(Now, "dd-mm-yyyy hhmmss") & ".xls"
 
Dim lastestFilename As String
 
latestFilename = LatestFile("D:\Colpitt machine data\")
 
If latestFilename <> "" Then 'returns empty string if no file in directory!
  Workbooks.Open latestFilename
End If

I run the Function :)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Sorry, my code omitted the directory path (which is required as Dir only returns the filename, not the path as well):

Rich (BB code):
If latestFilename <> "" Then 'returns empty string if no file in directory!
  Workbooks.Open "D:\Colpitt machine data\" & latestFilename
End If
 

Drumaster

New Member
Joined
Feb 28, 2011
Messages
25
Sorry, my code omitted the directory path (which is required as Dir only returns the filename, not the path as well):

Rich (BB code):
If latestFilename <> "" Then 'returns empty string if no file in directory!
  Workbooks.Open "D:\Colpitt machine data\" & latestFilename
End If

Thank you it works :) :cool:
 

Forum statistics

Threads
1,141,842
Messages
5,708,919
Members
421,598
Latest member
NewHere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top