Help

[KirnehDoodle]

pls
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

[Aussiebear]

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
NextFile:
        strFile = Dir
    Loop
    GetNewSuffix = intMax + 1
    Exit Function
ErrorHandler:
    If Err Then
        Err.Clear
        Resume NextFile
    End If
End Function

[Paul_Hossler]

pls
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

Example:

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