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

Posted on 2004-11-28
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

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

    LVL 23

    Accepted Solution

    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

    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
    LVL 7

    Expert Comment

    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. :)
    LVL 7

    Expert Comment

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

    Author Comment

    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

    BTW, what's the difference between format and format$ ?
    LVL 7

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
    This is an explanation of a simple data model to help parse a JSON feed
    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…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now