Results 1 to 18 of 18

Thread: VB-Excel SaveAs method, if file exists, rename without prompting

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Resolved VB-Excel SaveAs method, if file exists, rename without prompting

    When using the SaveAs method in VB to save my Excel sheet, if the user entered an existing filename, I want my code to just append a 1 to the end of the filename, save it and move on, no prompting, etc.

    Right now if the file exists, a msgbox pops up asking if i want to over-right, if you say no, it asks to save book1. I then want that saved as Filename-1 I want this to happen all behind the scenes.

    I know I could probably do some FSO stuff to check but wondering if there is a shorter way, like does the SaveAs throw an error that I can trap?
    Last edited by Kraig K; May 4th, 2005 at 02:31 PM.

  2. #2
    INXSIVE Bruce Fox's Avatar
    Join Date
    Sep 2001
    Location
    Melbourne, Australia
    Posts
    7,428

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    Personaly, I would use the FileExists API, then you will know ahead of time and prepare to save acordingly.


    Also, once you have appened 1 to the FileName; that file too may exists. Therefore, you can iterate with a counter past the similar named files (using the API) until there is no match and append the counter (as the next sequential number).




    Bruce.

  3. #3
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    This will work for files with a number from 1 to 9.
    VB Code:
    1. If ActiveWorkbook.Saved = True Then
    2.         Dim sName As String
    3.         Dim i As Integer
    4.         Dim bNum As Boolean
    5.         sName = ActiveWorkbook.Name
    6.         'look for the version number
    7.         i = 1
    8.         Do While bNum = False
    9.             If IsNumeric(Mid$(ActiveWorkbook.Name, i, 1)) = True Then
    10.                 bNum = True
    11.                 Exit Do
    12.             End If
    13.             i = i + 1
    14.         Loop
    15.         If bNum = True Then
    16.             'Save and increment the number by 1
    17.             ActiveWorkbook.SaveAs Replace(ActiveWorkbook.Name, Mid$(ActiveWorkbook.Name, i, 1), Mid$(ActiveWorkbook.Name, i, 1) + 1)
    18.         Else
    19.             'Add a 1 to the end of the name
    20.             ActiveWorkbook.SaveAs Replace(ActiveWorkbook.FullName, ".xls", "1.xls")
    21.         End If
    22.     Else 'New book so save with name and 1.xls
    23.         ActiveWorkbook.SaveAs "C:\SomeFileName1.xls"
    24.     End If
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    RobDog

    Seems like the ActiveWorkbook.Saved property is always false for me. Here's what happens, the users starts a new data collection run, enters some information, then the filename is auto generated based on that info. Then the CommonDialog comes up asking where to save the file, my app defaults to the C:\Data folder, so this dialog allows users to change directories. The generated filename is in the SaveAs box already. Then the app acquires data, then attempts to save. At this point a new Excel workbook is created. Here's a summary of my creation of the Excel object.

    VB Code:
    1. 'at top of code
    2. Dim objExcel As New Excel.Application
    3. Dim objBook As New Excel.Workbook
    4. 'then the savedata sub
    5. With objExcel
    6.       Set objBook = .Workbooks.Add
    7. 'write some stuff
    8. End With
    9. 'save the file
    10. objBook.SaveAs FileName
    11. 'destroy objects
    12. objExcel.Workbooks.Close
    13.    objExcel.Quit
    14.    Set objExcel = Nothing

    So what I tried with your code is I put it where my SaveAs is and commented that out.

  5. #5
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    If you always want a completely unique filename, append the datetime to it. I do this with log files that get generated from my programs. My customers can run the program 20 times in a row and 20 unique files will be created. Something like
    VB Code:
    1. FileName = FileName & "_" & Format(Now, "mmddyyyyhhmmss") & ".xls"

  6. #6
    Fanatic Member
    Join Date
    Mar 2002
    Location
    AUSTRALIA
    Posts
    603

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    If you append yyyymmddhhmmss instead of mmddyyyyhhmmss, then the user will be able to see (in Explorer), the list of files in chronological order.
    Rob C

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    That's an idea I didn't think of.

    Thanks Hack and Rob.

    If anyone has any other ideas, I'm listening...

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    Actually, just spoke to my coworker that I developed this for, appending all that to each filename may be a little much. I guess the chances of the same file are somewhat slim, the user SHOULD be changing the info that generates it but I wanted a failsafe in case they forget so they don't lose data.

    So I guess I'm back to my original thought. Looks like if I say NO to over-right the existing file, I get a 1004 error. Hmmm, this may be a way to do the check...

  9. #9
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    Quote Originally Posted by Kraig K
    Actually, just spoke to my coworker that I developed this for, appending all that to each filename may be a little much. I guess the chances of the same file are somewhat slim, the user SHOULD be changing the info that generates it but I wanted a failsafe in case they forget so they don't lose data.
    Well, if the changes are slim, you could drop the time, and just append the date. FileName_05042005.xls shouldn't be that difficult to deal with.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    I think I'd like to just append the 1 to it. Not that your suggestion isn't bad. Actually, just adding the date returns me to the original problem, how to determine if the file already exists. Some things I've discovered:

    The ActiveWorkbook.Name doesn't get set to my desired filename until AFTER the SaveAs method.
    SaveAs does throw error 1004 but before it does it asks if I want to over-right, turning DisplayAlerts off makes it select the default choise, which is yes.

    Looking at the SaveAs help, it has a conflict resolution option, but the way it reads, it won't do what I want.

    Sooo close.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Resolved Re: VB-Excel SaveAs method, if file exists, rename without prompting

    Did some more searching and found some nice, simple code that does exactly what I want.

    VB Code:
    1. Dim fName As String
    2. fName = "c:\test.xls"
    3. If Len(Dir(fName, vbNormal)) = 0 Then
    4.     'Add code to create file
    5. Else
    6.     'The file already exists, perform some operation
    7. End If

    Now I just need to make this robust to check the appended number and I'm good! Some of Robdog's code may help there.

    Thanks all.

  12. #12
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    What was wrong with "If ActiveWorkbook.Saved = True Then"?
    If the document has been saved then it exists.
    If its not saved then its new and doesnt exist.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    Quote Originally Posted by RobDog888
    What was wrong with "If ActiveWorkbook.Saved = True Then"?
    If the document has been saved then it exists.
    If its not saved then its new and doesnt exist.
    I believe that the Saved property gets "reset" upon starting a new run in my app. Once the file is closed and the object is destroyed, the active workbook then became "Book1". In other words, once the file is closed, there is no more active workbook. Am I making sense?

  14. #14
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    Thats correct, but I guess I thought that there would be a saved version open in Excel at some point that would be used to test
    against an existing file or instance.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    Quote Originally Posted by RobDog888
    Thats correct, but I guess I thought that there would be a saved version open in Excel at some point that would be used to test against an existing file or instance.
    I suppose it could if I didn't close the file after saving, but I like to.

    Anyway, here's what I came up with to append a sequential number to the end and if that new filename also exists, go to the next number, etc.

    VB Code:
    1. Dim k As Integer
    2. Dim fName As String
    3.       'check if file already exists
    4.       If Len(Dir(FileName, vbNormal)) = 0 Then 'file does not exist
    5.          objBook.SaveAs FileName
    6.       Else
    7.          fName = FileName
    8.          Do Until Len(Dir(FileName, vbNormal)) = 0
    9.             k = k + 1
    10.             FileName = Replace$(fName, ".xls", "-" & k & ".xls")
    11.          Loop
    12.          objBook.SaveAs FileName
    13.       End If

  16. #16
    Fanatic Member
    Join Date
    Mar 2002
    Location
    AUSTRALIA
    Posts
    603

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    It would be nice, if the list of files (viewed in explorer) were chronological.
    That can be achieved by padding in front with 0's
    But that then raises the 'over adaption' of limiting you to say 001 to 999

    I knocked this up.
    It is a black box that allows you to pass any file name, and let it increment it for you.
    You can optionally tell it what separator( _ ) to use, and what length you wish the incrementor to be ( 001 or 0001 or more)

    VB Code:
    1. Private Sub Form_Click()
    2.  Dim sFullFileName As String
    3.  Dim myStr As String
    4.  Dim cntr As Long
    5.   sFullFileName = "MyPicture.jpeg"
    6.   'sFullFileName = "MyPic.jpg"
    7.   myStr = sFullFileName
    8.   myStr = FileNameIncrement(myStr, "_") 'You could chg optional length from 3 to 4,  by passing 4
    9.   Print myStr
    10.   For cntr = 0 To 11
    11.     myStr = FileNameIncrement(myStr, "_")
    12.     Print myStr
    13.   Next cntr
    14. End Sub
    15.  
    16. 'This pads the suffix(Incr), so that viewing of file names will be chronological.
    17. 'This will append say "_001"
    18. 'If you pass one with say "_003", then you'll get "_004" back
    19. Public Function FileNameIncrement(sIN As String, Optional sSep As String = "_", Optional lenIncr As Long = 3) As String
    20. 'Author: Rob Crombie
    21.  Dim sFn As String            ' MyPic
    22.  Dim sFnIncr As String        ' 001  or 0001  or  00001
    23.  Dim sFnIncrMaybe As String
    24.  Dim lFnIncr As Long
    25.  Dim sFnExt As String         ' .jpg  or .jpeg
    26.  Dim sArr() As String
    27.  Dim sWork As String          ' Working pad
    28.  Dim sWork2 As String         ' Working pad
    29.  'Dim lLen As Long            ' Usually  3  may be  4  (mutter, mutter)
    30.  Dim bVirgin As Boolean       ' Lacks any previous incrementing
    31.  Dim sMsg As String
    32.  Dim sTitle As String
    33.  
    34.   If InStr(sIN, ".") = 0 Then
    35.     sMsg = ""
    36.     sMsg = sMsg & "       Please  give  full  file  name,  including  the  extension" & vbCrLf
    37.     sMsg = sMsg & "                 An  example  would  be     MyPic.jpg"
    38.     sTitle = " YOU  MUST  ALSO  INCLUDE  THE  FILE  EXTENSION"
    39.     MsgBox sMsg, , sTitle
    40.     Exit Function '<== Exit Function
    41.   End If
    42.  
    43.   sArr = Split(sIN, ".")
    44.   sWork = sArr(0)            ' sWork contains everything to the left of the .
    45.   sFnExt = sArr(1)           ' Contains say  jpg
    46.  
    47.   'Perhaps it hasn't been here before ?
    48.   If InStr(1, sWork, sSep) = 0 Then
    49.     'Definitely has never been appended
    50.     bVirgin = True
    51.   Else
    52.     'It contains sSep, but may just be part of file name ?
    53.     ' sWork contains everything to the left of the .
    54.     sArr = Split(sWork, sSep)
    55.     sFnIncrMaybe = sArr(UBound(sArr))
    56.     If Len(sFnIncrMaybe) <> lenIncr Then
    57.       bVirgin = True
    58.     Else
    59.       'The sSep was in the correct place, but it may not be our sSep ?
    60.       ' I'll assume that   sSep & nnn   is ours
    61.       If IsNumeric(sFnIncrMaybe) = True Then
    62.         'Lets convert it, and increment it, whilst we know what we are doing.
    63.         lFnIncr = CLng(sFnIncrMaybe) + 1
    64.       Else
    65.         bVirgin = True
    66.       End If
    67.     End If
    68.   End If
    69.  
    70.   If bVirgin Then
    71.     'If it is a virgin, our job is easy. Just append say  _001
    72.     FileNameIncrement = sWork & sSep & PadLeft("1", lenIncr, "0") & "." & sFnExt
    73.     Exit Function '<== Exit Function
    74.   Else
    75.     'It had a suffix like 006,  so make that 007 instead
    76.     'sWork = MyPic_001    we want  sFn = MyPic
    77.     sFn = Left(sWork, Len(sWork) - (lenIncr + 1))
    78.     'Convert the just incremented number to a string and pad it in front with 0
    79.     sWork2 = CStr(lFnIncr)  '<== This may contain say 6, if it was _006
    80.     '   Typically     MyPic    _             say 6        3    Pad chr
    81.     FileNameIncrement = sFn & sSep & PadLeft(sWork2, lenIncr, "0") & "." & sFnExt
    82.   End If
    83.  
    84. End Function
    85.  
    86. Public Function PadLeft(sIN As String, lngLen As Long, Optional sChar As String = " ") As String
    87. 'Author: Rob Crombie
    88.  Dim sPad As String
    89.  Dim i As Long
    90.   If Len(sIN) > lngLen Then
    91.     MsgBox "Passed string was too long. already", , "ERROR IN CODE THAT CALLED  PadLeft"
    92.     Exit Function    '<===== Exit Function
    93.   End If
    94.   'If "" was passed, then assume they meant " "
    95.   If Len(sChar) = 0 Then
    96.     sChar = " "
    97.   End If
    98.   For i = 1 To (lngLen - Len(sIN))
    99.     sPad = sPad & sChar
    100.   Next i
    101.   PadLeft = sPad & sIN
    102. End Function
    Last edited by RobCrombie; May 4th, 2005 at 11:59 PM.
    Rob C

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Aug 2001
    Location
    Minneapolis, MN
    Posts
    189

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    Cool Rob. A little more than what I need for the current project, but definitely looks useful! Maybe a batch file renamer??

    I did change my code to append -01 so I can get up to 100 files in order, plenty for my current needs.

  18. #18
    Fanatic Member
    Join Date
    Mar 2002
    Location
    AUSTRALIA
    Posts
    603

    Re: VB-Excel SaveAs method, if file exists, rename without prompting

    A little more than what I need for the current project
    Ah, such is life
    Rob C

Posting Permissions

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



Click Here to Expand Forum to Full Width