I have been working to get my error messaging to function properly. When running the macro it should go out and check if the folder Import Templates is present in the folder the main workbook is saved in, if it does display a message advising it already exists and to rename or delete the folder as needed. If the folder does not exist there is code to create the folder and export the sheets in the workbook into that folder. If the folder doesn't exist the code runs perfectly, if it does exist the error message does not pop up and displays a run time error #75. If there is anything anyone could do to assist it would be greatly appreciated.
Private Sub ExportSheets() 'saves all visible sheets as new xlsx files
Dim ws As Worksheet, wbNew As Workbook
Dim sFolderPath As String
Dim fs As Object
Set wbNew = Application.ThisWorkbook
sFolderPath = wbNew.Path & "\" & "Import Templates"
If Dir(sFolderPath) <> "" Then
'If the folder does exist
MsgBox "The folder currently exists, please rename or delete the folder!", vbInformation, "Import Files"
Else
'If the folder does not exist available
MkDir sFolderPath
End If
For Each ws In ThisWorkbook.Sheets 'for each worksheet
If ws.visible Then 'if it's visible:
Debug.Print "Exporting: " & ws.Name
ws.Copy '(if no params specified, COPY creates activates a new wb)
Set wbNew = Application.ActiveWorkbook 'get new wb object
wbNew.SaveAs sFolderPath & "\" & ws.Name & ".csv", 23 'save new wb
wbNew.Close 'close new wb
Set wbNew = Nothing 'cleanup
End If
Next ws
Set ws = Nothing 'clean up
End Sub
CodePudding user response:
If checking for a folder you need to use vbDirectory
Dir(sFolderPath, vbDirectory)
The default is vbNormal
, which only returns files.
And presumably you should Exit Sub
after the messagebox if the folder already exists.