Results 1 to 3 of 3

Thread: Help

  1. #1


    i want to help "batch file " to saveas opened excle files in other place like c: and flash disk drive and defult name to files "excle 1"
    thank you

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Dec 2005
    Welcome to VBAX KirnehDoodle. Just so we can clear up any confusion, you would like to use VBA to ;

    1. Close any open workbooks
    2. Rename the workbooks in an incremental manner

    However, I an currently unclear as to where the workbooks are meant to found. Workbooks should never reside in C: as this should be for system files only. In relation to flash drives these could also carry all sorts of drive classifications. Can you be a little more precise please?

    The below code already exists in the KB, but to determine if it would work for you we would need to understand the information I have asked for and then amend it further for you.

    Sub CreateNewFileName()
         'Produces an incremental FileName (if name is 'Data' it creates Data-1.xls)
         'Builds a suffix always one greater than the max suffix of any other potentially
         'existing files that have the same 'root' name, e.g. if 'Data.xls' and 'Data-2.xls'
         'exist, it creates Data-3.xls
         'Helps to avoid overwrite old files (among other uses)
        Dim newFileName As String, strPath As String
        Dim strFileName As String, strExt As String
        strPath = "C:\AAA\" 'Change to suit
        strFileName = "Data" 'Change to suit
        strExt = ".xls" 'Change to suit
        newFileName = strFileName & "-" & GetNewSuffix(strPath, strFileName, strExt) & strExt
        MsgBox "The new FileName is: " & newFileName
         'Save copy
        ActiveWorkbook.SaveCopyAs strPath & newFileName
    End Sub
    Function GetNewSuffix(ByVal strPath As String, ByVal strName As String, ByVal strExt As String) As Integer
        Dim strFile As String, strSuffix As String, intMax As Integer
        On Error GoTo ErrorHandler
         'File's name
        strFile = Dir(strPath & "\" & strName & "*")
        Do While strFile <> ""
             'File's suffix starts 2 chars after 'root' name (right after the "-")
            strSuffix = Mid(strFile, Len(strName) + 2, Len(strFile) - Len(strName) - Len(strExt) - 1)
             'FileName is valid if 1st char after name is "-" and suffix is numeric with no dec point
             'Skip file if "." or "," exists in suffix
            If Mid(strFile, Len(strName) + 1, 1) = "-" And CSng(strSuffix) >= 0 And _
            InStr(1, strSuffix, ",") = 0 And InStr(1, strSuffix, ".") = 0 Then
                 'Store the max suffix
                If CInt(strSuffix) >= intMax Then intMax = CInt(strSuffix)
            End If
            strFile = Dir
        GetNewSuffix = intMax + 1
        Exit Function
        If Err Then
            Resume NextFile
        End If
    End Function
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Sage
    Apr 2007
    United States
    Quote Originally Posted by KirnehDoodle View Post
    i want to help "batch file " to saveas opened excle files in other place like c: and flash disk drive and defult name to files "excle 1"
    thank you

    1. You said 'Please' and 'Thank You' -- that's alway nice

    2. 'Help' as a title is too brief since we're here to alway help - a more descriptive title is better, for example '"How do I save open Excel workbook to different folders?"

    3. Not sure what you mean by "Batch file" -- a macro?

    4. An example in words or workbook attached is sometimes helpful to others


    "A macro to choose a folder from a list and then save the active workbook to that folder"


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums

Posting Permissions

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