How to save file using current date & time in VB codes?


I'm working on a VB6 codes as attached :

'(5) make a new copy of the target(.xls) report
  FileCopy "C:\nbutane\rsview\report\Proving Report.xls", "C:\nbutane\rsview\log reports\Proving Report" & Format(Date, "  dd-mm-yyyy") & "  " & Format(Time, "hh mm ss") & ".xls"

Thus, my outcome will be something like this = Proving Report  09-12-2001  084937

But, I'll like to change the time into 08:49:37 AM or at least 08:49:37 from just 084937!

Is that possible?

Please help. Thanks.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

have you tried
Format(Time, "hh:mm:ss")?

hh=Right("00" & Trim(Str(Hour(t))),2)
mm=Right("00" & Trim(Str(Minute(t))),2)
ss=Right("00" & Trim(Str(Second(t))),2)
if Hour(t)>11 then
if Hour(t)>12 then
   hh=Right("00" & Trim(Str(Hour(t)-12)),2)

Then instead of Format(Time...), use
hh & ":" & mm & ":" & ss & " " & ap

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ckchew666Author Commented:
fridom: I tried that, it won't work.

cookre: Thx for ur reply. If I understand u correctly, basically I just need to define ur codes and replace Format(Time, "hh mm ss") to Format(hh & ":" & mm & ":" & ss & " " & ap).

Sorry, I'm new to VB, I'm 'slow' with codes :(

A friend of mine told me that we can save a filename with ":" or "?", thus I can't save my file as what I want hh:mm:ss! I'm not sure how logic/true is this.

Basically this is the outcome from your sample codes:
Proving Report  09-12-2001  Proving Time at 0.291388888888889

Below is my entire codes:

Public Sub PbtnCreateExcel_Click()

  Dim PobjExcel As Object
  Dim PobjExcel_data As Object
  Dim PcurrentDate As String
  Dim PwbTarget As Object
  Dim PwbTargetFile As Object
  Dim PwbSource As Object
  Dim PfilePath As String
'(1) open Excel files
On Error Resume Next
Dim ln%
For p = 0 To 99

 currentDate = Format(Date, "YYYY") & " " & Format(Date, "MM") & " " & Format(Date, "DD") & " " & Format(p, "0000") & " " & "(WIDE)" 'notice the Format(p, "0000")
   filePath = "C:\nbutane\rsview\DLGLOG\PROVING\" & currentDate & ".dbf"
   ln = FileLen(filePath) 'a dummy function call to get the filesize, not that we are going to use it anywhere.. just to raise an error if the file does not exist
   If Err.Number <> 53 Then 'err.number is set to 53 if we get a file not found error
       GetFileName = filePath 'so the error was not 53 so we assume the file is present
       Exit For 'come out of the for loop
   End If
   Err.Clear '<--clear the previous error
On Error GoTo 0 '<-- this will disable the resume next error handling that we enabled earlier in the code

'(2) open the source workbook as read only and open target workbook with write
  Set PobjExcel_data = CreateObject("Excel.Application")
  Set PwbSource = PobjExcel_data.Workbooks.Open(filePath).Sheets(1)

  Set PobjExcel = CreateObject("Excel.Application")
  Set PwbTargetFile = PobjExcel.Workbooks.Open("C:\nbutane\rsview\report\" & "Proving Report" & ".xls")
  Set PwbTarget = PwbTargetFile.Sheets(1)

'(3) extract data from source and put into target
   With PwbSource
       '''''''''''(Rows, Column)''''''''''''''''''
       Dim j As Integer
       j = 2 'always start with 2 base on the source sheet
       Do While (PwbSource.cells(j, 2) <> "")               ' do below if something
 If CDate(.cells(j, 2).Value) >= CDate("6:50:00") And CDate(.cells(j, 2).Value) <= CDate("6:52:00") Then
              PwbTarget.cells(28, 3).Value = .cells(j, 5).Formula
              PwbTarget.cells(28, 4).Value = .cells(j, 7).Formula
              PwbTarget.cells(28, 5).Value = .cells(j, 9).Formula
              PwbTarget.cells(28, 6).Value = .cells(j, 11).Formula
              PwbTarget.cells(28, 7).Value = .cells(j, 13).Formula
              PwbTarget.cells(28, 8).Value = .cells(j, 15).Formula
              PwbTarget.cells(28, 9).Value = .cells(j, 17).Formula
 ElseIf CDate(.cells(j, 2).Value) >= CDate("5:00:00") And CDate(.cells(j, 2).Value) <= CDate("5:02:00") Then
              PwbTarget.cells(27, 3).Value = .cells(j, 5).Formula
              PwbTarget.cells(27, 4).Value = .cells(j, 7).Formula
              PwbTarget.cells(27, 5).Value = .cells(j, 9).Formula
              PwbTarget.cells(27, 6).Value = .cells(j, 11).Formula
              PwbTarget.cells(27, 7).Value = .cells(j, 13).Formula
              PwbTarget.cells(27, 8).Value = .cells(j, 15).Formula
              PwbTarget.cells(27, 9).Value = .cells(j, 17).Formula
          End If
          j = j + 1   'go to next row
      Loop            'stop when no more data
  End With

  '(4) save the target excel file and close both the target and source.
  PwbSource.Application.Windows(currentDate & ".dbf").Visible = True

  'set target path
  PwbTarget.Application.Windows("Proving Report.xls").Visible = True
  'to print out the target report
  PobjExcel.Workbooks(1).PrintOut Copies:=1, Collate:=True
  'after target is printed, save, close and exit Excel
  t = Now
hh = Right("00" & Trim(Str(Hour(t))), 2)
mm = Right("00" & Trim(Str(Minute(t))), 2)
ss = Right("00" & Trim(Str(Second(t))), 2)
ap = "AM"
If Hour(t) > 11 Then
   ap = "PM"
   End If
If Hour(t) > 12 Then
   hh = Right("00" & Trim(Str(Hour(t) - 12)), 2)
   End If

  '(5) make a new copy of the target(.xls) report
  FileCopy "C:\nbutane\rsview\report\Proving Report.xls", "C:\nbutane\rsview\log reports\Proving Report" & Format(Date, "  dd-mm-yyyy") & "  " & "Proving Time at " & Format(hh & ":" & mm & ":" & ss & " " & ap) & ".xls"
  '(6) open the target again (FC Daily Report.xls) to clear the data inside
  Set PobjExcel = CreateObject("Excel.Application")
  Set PwbTargetFile = PobjExcel.Workbooks.Open("C:\nbutane\rsview\report\" & "Proving Report" & ".xls")
  Set PwbTarget = PwbTargetFile.Sheets(1)
  'clear target file
  'after data is cleared, close the target and quit Excel
  Set PwbSource = Nothing  'free memory
  Set PwbTarget = Nothing
  'only display message when error encountered
  If Err.Number <> 0 Then
      MsgBox Err.Description
  End If

End Sub
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Just format your time like this...

Format$(Time, "hh:mm:ss AMPM")

another free advice on placing date in a file name. It is better to reverse the format to YYYY-MM-DD rather than DD-MM-YYY, the reason is that when the computer arrange files according to file names, it sorts through the YYYY and MM before DD. This way, you won't have messed-up files with same day but different months or years.

Format$(Date, "  yyyy-mm-dd")

Oh, Format$ is slightly faster than Format.

Good luck. :)
Yes, the ":" is not allowed, so try "-" or blank.
ckchew666Author Commented:
Jenn3: [qoute]"Yes, the ":" is not allowed, so try "-" or blank."[qoute] if this is true, then "Format$(Time, "hh:mm:ss AMPM")" this will not work, am I right? The reason I'm not using "-" seperator for Time because I've put "-" seperator for my date, so if both of them using the same "-" seperator then they'll look alike! Imagine date 04-04-2004 & time 04-04-04. I'm affraid the operator will mixed up!
i know hh-mm-ss can work easily.

If "Yes, the ":" is not allowed, so try "-" or blank." it's tru and no other option, then i'll have to settle with hhmm only (use 24-hour format), need not to use the am or pm also. What do you guys think?
ckchew666Author Commented:
BTW, what's the difference between format and format$ ?
Actually there's little difference, but Format$ takes and returns variables of type string while Format takes and returns variables of type variant. It's fine with either one, but generally folks have claimed the one with $ is so very slightly faster.

Avoid using $ for the DATE (i.e. DATE$) as it will have different effect on formating at times depending the OS.


FileCopy "C:\nbutane\rsview\report\Proving Report.xls", "C:\nbutane\rsview\log reports\Proving Report" & Format$(Date, "  yyyy-mm-dd") & "  " &Format$(Time, "hh-mm-ssAMPM") & ".xls"

the AM PM at the end of the time should indicate it's time. Another thing is that the Date will start wil 2004 or 2005, the normal reasonable user will beable to tell it's a date. There's no time with a format that has 4 Digit.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.