[RESOLVED] (Excell) Adding +1 or time value to existing file names when saving in VB-VBForums
Results 1 to 9 of 9

Thread: [RESOLVED] (Excell) Adding +1 or time value to existing file names when saving in VB

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    4

    Resolved [RESOLVED] (Excell) Adding +1 or time value to existing file names when saving in VB

    HI
    I am new to Visual Basic and looking at upgrading some code (colleague who entered code has retired) that we use in Excel for system outages. It is Save/Save As code.
    It uses text from several cells to create a file name: Example, a number value from J3 and a Date value from I5. Then it creates a Directory from J3 and creates the file name we use.

    Example J3 = 4 & I5 = 29-May-2017 then the file name is saved in Dir. 4\4_29-May-2017.xlsx
    If a second outage occurs on the same day then it overwrites the file which we do not want. By adding the time value or +1 at the end we could have noumerous files created with the same name on the same day.

    What we need is one of 2 things, either a way of adding a time value (as text 12:00 converted to 1200)to the file name (say P5 is 12:00) or add a running counter to the file name if it detects an existing file name that is the same as the one that if being saved. Adding the time (as text to the file name would be the preferred method but either way would work for us.
    Any help from the professionals would be appreciated.

    The existing code is listed below:

    Sub SaveToDir2()
    '
    Dim wbk As Workbook
    '
    CDir = ActiveWorkbook.Path
    '
    SaveDir = CDir & "\" & ActiveSheet.Range("J3")
    '
    'check to see if Dir exists if not create it. Could also abort if the Dir should exist
    If Len(Dir(SaveDir, vbDirectory)) = 0 Then
    MkDir SaveDir
    End If
    '
    'Checks to see if the Date cell is in date format
    If IsDate(ActiveSheet.Range("I5")) Then
    SaveName = ActiveSheet.Range("J3") & "_" & Application.Text(ActiveSheet.Range("I5"), "DD-MMM-YYYY") & ".xlsx"
    Else
    SaveName = ActiveSheet.Range("J3") & "_" & Application.Text(ActiveSheet.Range("I5"), "DD-MMM-YYYY") & ".xlsx"
    End If
    '
    'Check to see if the file already exists
    If Len(Dir(SaveDir & "\" & SaveName, vbDirectory)) > 0 Then
    Resp = MsgBox("File name: " & SaveName & vbCrLf & vbCrLf & "already exists in: " & vbCrLf & vbCrLf & SaveDir & vbCrLf & vbCrLf & "Press Okay to continue, Cancel to abort", vbOKCancel)
    If Resp = vbCancel Then
    Exit Sub
    End If
    ' Check to see if the file is open
    For Each wbk In Workbooks
    If wbk.Name = SaveName Then
    Resp2 = MsgBox(SaveName & " is open. Press OK to close the file or Cancel to abort", vbOKCancel)
    If Resp2 = vbOK Then
    Application.DisplayAlerts = False
    Workbooks(SaveName).Close
    Else
    Exit Sub
    End If
    End If
    Next
    End If
    '
    Application.DisplayAlerts = False
    '
    Sheets("Sheet1").Copy 'Moves Sheet1 only to a new file
    ActiveWorkbook.SaveAs Filename:= _
    SaveDir & "\" & SaveName, FileFormat _
    :=xlOpenXMLWorkbook, CreateBackup:=False 'Saves the new file
    '
    ActiveWindow.Close
    '
    MsgBox ("File name: " & SaveName & vbCrLf & vbCrLf & "has been saved to " & vbCrLf & vbCrLf & SaveDir)
    '
    Application.DisplayAlerts = True

    End Sub

    Again Thanks if any can help, if not then I must try and learn VB to remedy the problem myself.
    It will take awhile but looks like VB can be lot of fun

  2. #2
    Fanatic Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    827

    Re: (Excell) Adding +1 or time value to existing file names when saving in VB


  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,646

    Re: (Excell) Adding +1 or time value to existing file names when saving in VB

    If IsDate(ActiveSheet.Range("I5")) Then
    SaveName = ActiveSheet.Range("J3") & "_" & Application.Text(ActiveSheet.Range("I5"), "DD-MMM-YYYY") & ".xlsx"
    Else
    SaveName = ActiveSheet.Range("J3") & "_" & Application.Text(ActiveSheet.Range("I5"), "DD-MMM-YYYY") & ".xlsx"
    End If
    this seems a bit redindant as it appears to make the same string regardless of the isdate criteria
    to add the time from computer time
    Code:
    SaveName = ActiveSheet.Range("J3") & "_" & Application.Text(ActiveSheet.Range("I5"), "DD-MMM-YYYY") & format(now, "hhmm") & ".xlsx"
    you can use application.text in place of format, both appear to do the same thing, but format is a documented VBA function, whereas application.text is using a worksheetfunction
    if the date in the cell i5 is always todays date you could just abbreviate the date and time to format(now, "dd-mm-yyyy hhmm")

    if you use an incrementing counter, you need to test the filename with each value of the counter until you find one that does not yet exist as a file
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    Fanatic Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    827

    Re: (Excell) Adding +1 or time value to existing file names when saving in VB

    When I create file names using the date and time, I usually like to use the format below, so that they appear in a more usable order:

    YYYY-MM-DD HHMM

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,646

    Re: (Excell) Adding +1 or time value to existing file names when saving in VB

    I usually like to use the format below
    i tend to agree, but it is up to the user
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    4

    Thumbs up Re: (Excell) Adding +1 or time value to existing file names when saving in VB

    I thank you very much for your help and this fix will work in the interim but I still need it to save using the value of Cell P5 (which is a time value) thus if anyone makes changes or adds to a remarks section on the spreadsheet it will always save to the same filename.
    I have to admit that you guys are good as I tried and played with the NOW function but could not make it work at all

    Quote Originally Posted by westconn1 View Post
    this seems a bit redindant as it appears to make the same string regardless of the isdate criteria
    to add the time from computer time
    Code:
    SaveName = ActiveSheet.Range("J3") & "_" & Application.Text(ActiveSheet.Range("I5"), "DD-MMM-YYYY") & format(now, "hhmm") & ".xlsx"
    you can use application.text in place of format, both appear to do the same thing, but format is a documented VBA function, whereas application.text is using a worksheetfunction
    if the date in the cell i5 is always todays date you could just abbreviate the date and time to format(now, "dd-mm-yyyy hhmm")

    if you use an incrementing counter, you need to test the filename with each value of the counter until you find one that does not yet exist as a file

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    4

    Re: (Excell) Adding +1 or time value to existing file names when saving in VB

    I would do this but the date and time cells are recording a specific action on the spreadsheet

    Example :
    Cell J3 is the date an incident occurred
    Cell P5 is the time the incident occurred
    J3 is the number of that represents the specific location of the incident

    Would like to have all 3 these constants added to the filename and remain the same if the spreadsheet is opened and saved again

    Thanks you very much for your input

    [QUOTE=jdc2000;5176727]When I create file names using the date and time, I usually like to use the format below, so that they appear in a more usable order:

    YYYY

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,646

    Re: (Excell) Adding +1 or time value to existing file names when saving in VB

    need it to save using the value of Cell P5
    change now to activesheet.range("p5"), similar to the date cell
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    New Member
    Join Date
    Jun 2017
    Posts
    4

    Re: (Excell) Adding +1 or time value to existing file names when saving in VB

    A huge thanks to Westconn1 and jdc2000 for all your help with this, I have learnt a lot about visual basic from you and want to let you know that it is running perfectly now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.