Home > database >  Unable to get an error message to display if a folder exists
Unable to get an error message to display if a folder exists

Time:10-28

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.

  • Related