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

Posted on 2004-11-28
Medium Priority
Last Modified: 2010-04-17

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.
Question by:ckchew666
LVL 24

Expert Comment

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

LVL 22

Accepted Solution

cookre earned 252 total points
ID: 12693758
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

Author Comment

ID: 12693822
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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Expert Comment

ID: 12693843
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. :)

Expert Comment

ID: 12693851
Yes, the ":" is not allowed, so try "-" or blank.

Author Comment

ID: 12693891
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?

Author Comment

ID: 12693915
BTW, what's the difference between format and format$ ?

Assisted Solution

Jenn3 earned 248 total points
ID: 12694350
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.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Make the most of your online learning experience.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question