saving file names in VB
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: saving file names in VB

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am trying to write VB to allow me to Save As file names that are numerical in order (ex. name2630, name2631, name2632, etc.). Is there an easier way to do this than to manually change the file name each time? Any help is much appreciated.

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    using your example, you could set up a loop to create the filename, e.g.:


    Dim as integer
    dim sName as String
    Dim sFirstPart as String


    sFirstPart = "Name"
    for i = 2630 to 2631
    sName = sFirstPart & i
    'put your save as code here using sName as the filename.
    next


    HTH

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That'll do it. Many thanks!
    -KB

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Is this all done at once or is it going to be an ongoing tally?

    I'll assume it is ongoing unless you say otherwise...

    This will search the named folder and increment the value of the highest filename(value) found by one and then save the active workbook to the same named path with the new incremented values/filename...

    Tongue-twister!

    Anyway, try it out.

    Sub SaveIncrementNumFileName_xls()
    Dim SaveAsThisName As String
    Dim FolderToSearch As String
    Dim i As Integer
    Dim FoundFileNameValue(2) As Long

    With Application.FileSearch
    FolderToSearch = "C:TestNum" 'edit this to your path
    .LookIn = FolderToSearch
    If .Execute() > 0 Then
    For i = 1 To .FoundFiles.Count
    SaveAsThisName = .FoundFiles(i)
    FoundFileNameValue(1) = Val(Mid(SaveAsThisName, Len(FolderToSearch) + 2, _
    Len(SaveAsThisName) - Len(FolderToSearch) + 1))
    If FoundFileNameValue(1) > FoundFileNameValue(2) Then _
    FoundFileNameValue(2) = FoundFileNameValue(1)
    Next
    End If
    End With
    FoundFileNameValue(2) = FoundFileNameValue(2) + 1
    ActiveWorkbook.SaveAs FolderToSearch & "/" & FoundFileNameValue(2) & ".xls"
    End Sub

    Oh well, post already answered.
    Something new for me anyway.
    Tom



    [ This Message was edited by: TsTom on 2002-04-15 21:32 ]

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Kristabelle,

    Here is a macro that when you run it will add one to the file name of the active workbook and do a SaveAs. To install it, use Alt-TMV and paste the code into the macro module code pane that appears. To make its use easy you can assign this macro to a button or, using the instructions commented in the code, make it the workbook's default Save mode.

    Sub IncrementAndSaveAs()

    'This macro saves the active workbook, which is assumed to have
    'a filename composed of a name and a number, such as "Inventory060.xls".
    'This macro increments the filename to Inventory061.xls, and does a
    'SaveAs to this filename. To make this macro execute automatically
    'on each save, use the Workbook_Save event macro with the code:

    'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' If Not SaveAsUI then
    ' SaveAsUI = False
    ' Cancel = False
    ' IncrementAndSaveAs
    ' EndIf
    'End Sub

    'Note: The number of numeric digits you use in the file name determines
    ' how many saves can be done before the number wraps to zero. For
    ' example, after Inventory999.xls would be Inventory000.xls, so three
    ' digits allows for 999 saves before wrapping to zero.

    'find out how many digits in current filename

    Dim CurrentName As String
    Dim NewName As String
    Dim nLen As Integer
    Dim NewNumber As String

    CurrentName = ActiveWorkbook.Name
    If Right(CurrentName, 4) = ".xls" Then
    'strip off .xls
    CurrentName = Left(CurrentName, Len(CurrentName) - 4)
    End If

    For nLen = 6 To 1 Step -1
    NewNumber = Right(CurrentName, nLen)
    If IsNumeric(NewNumber) Then
    GoTo SetNewName
    End If
    Next nLen
    MsgBox "File name does not end with numeric string" & vbCrLf & _
    "New numeric version cannot be created.", _
    vbCritical, "Save Not Done"
    Exit Sub

    SetNewName:
    NewName = Left(CurrentName, Len(CurrentName) - nLen) & _
    Right(CStr(CLng(NewNumber) + 1), nLen)

    ActiveWorkbook.SaveAs Filename:=NewName

    End Sub

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com