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"