"File cannot be accessed" 1004 Export worksheets error VBA

Question

I couldn't solve the error. I searched SO but couldn't find the solution. The code works 98%. Just 2% needed to be solved.

Userform :

On click of Browse button, "Select a Folder" dialog box opens. The selected folder path is displayed in the textbox.

Code :

Dim sItem As String

Private Sub browse_Button_Click()
Dim fldr As FileDialog
Dim strPath As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then Exit Sub
    sItem = .SelectedItems(1)
End With
showFilePath.Text = sItem
End Sub

Private Sub cancel_button_Click()
Unload Me
End Sub

Private Sub export_button_Click()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim FolderName As String
Application.ScreenUpdating = False
Set xWb = Application.ThisWorkbook
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = sItem & "\" & xWb.Name & " " & DateString
MkDir FolderName

For Each xWs In xWb.Worksheets
    xWs.Copy
        If xlsx = True Then
            FileExtStr = ".xlsx": FileFormatNum = 51
            Unload Me
        ElseIf xlsm = True Then
            FileExtStr = ".xlsm": FileFormatNum = 52
            Unload Me
        ElseIf xls = True Then
            FileExtStr = ".xls": FileFormatNum = 56
            Unload Me
        ElseIf xlsb = True Then
            FileExtStr = ".xlsb": FileFormatNum = 50
            Unload Me
        ElseIf csv = True Then
            FileExtStr = ".csv": FileFormatNum = 6
            Unload Me
        ElseIf txt = True Then
            FileExtStr = ".txt": FileFormatNum = -4158
            Unload Me
        ElseIf html = True Then
            FileExtStr = ".html": FileFormatNum = 44
            Unload Me
        ElseIf prn = True Then
            FileExtStr = ".prn": FileFormatNum = 36
            Unload Me
        End If
    xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
    Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum   '<---ERROR HERE
    Application.ActiveWorkbook.Close False
Next

MsgBox "You can find the files in " & FolderName
Application.ScreenUpdating = True
End Sub

I get an error at the end, after selecting a folder and also the format to export the worksheet.

Error:

NOTE: The excel file is saved in E:/VBA/AJD/Export Worksheets.xlsm. And the worksheets are to be exported in other location(user selects a folder). Hence the excel file which runs the code and the export folder is in different location.

The path:

Silly question perhaps, but have you checked all the possible issues that are listed in the error pop-up? — CLR
@Vityata the error its giving me is in this line Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum — Aman Devrath
@AmanDevrath - the error seems to be because of the two `\\` in path, I guess. — Vityata

Vityata Vityata · Accepted Answer · 2018-06-25T11:54:33

The best solution, when you are getting an error in VBA is to read carefully the error message box. In this case, the first suggestion is the correct one - "Make sure the specified folder exists".

The easiest way to check, whether the folder exists is to see the path. Possible solution:

MsgBox xFile

Which prints the folder and lets you debug from then on. Usually the error is visible, as in this case, where two \\ are present:

Edit:

In this case OP could check first if showFilePath (Browse) has been used correctly:

If Dir(showFilePath.Text) = "" Then
    sItem = showFilePath.Text
Else
    MsgBox "No folder selected!"
    Exit Sub
End If

Second, OP could check which of the following situations applies:

If Right(sItem, 1) = "\" Then 'Backslash is yet present
    FolderName = sItem & xWb.Name & " " & DateString
Else
    FolderName = sItem & "\" & xWb.Name & " " & DateString 'Add a Backslash
End If

"File cannot be accessed" 1004 Export worksheets error VBA

1 Answers