SaveCopyAs file with date and Time

[Drumaster]

how dow you save a file with date and time.
this is my code this work but if i add Time i get error

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

Here with Time in it and i get a error

ctiveWorkbook.SaveCopyAs "D:\Colpitt\Machine\Data\Alarms " & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & "-" & Time (Now) & ".xls"

 

[Andrew Poulsom]

Try using:

Format(Time, "hhmmss")

 

[Drumaster]

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

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]

Hi Carlo

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

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

for example.

 

[Drumaster]

Hi Carlo

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

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.

'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]

Hi

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

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:

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

 

[Drumaster]

Hi

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

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:

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]

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

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]

Sorry, my code omitted the directory path (which is required as Dir only returns the filename, not the path as well):

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

 

[Drumaster]

Sorry, my code omitted the directory path (which is required as Dir only returns the filename, not the path as well):

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

Thank you it works