VBA Excel Error Run-Time error '53'

Question

I hope you can help. I have a macro and it is extensive. Essentially what it does is it has a User form See Pic 1

This user form allows a user to navigate to a template where data is stored (see Pic 2) and select this completed template.

On the user form in Pic 1. The user then clicks the radio button DTOV(without meeting information) and then clicks the button Pre-Validate.

Once this Pre-Validate button is clicked the the data on the Excel Template gets copied from this Excel file transformed to text file with | (pipe) separators, and the user is asked to confirm the directory path to where this newly created text file will go. See Pic 3

what should happen is that this newly created text document gets placed into a folder where the text document is picked up again and sent to another database

The problem I am having is that the newly created text file is not making it to the folder to be picked up by the other database.

I am getting an error Run-Time error '53' this is happening on line

' create the file on the network folder Name DTOV_Directory & newfilename & "txt" As network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"

Which is located in the Private Sub Private Sub Pre_validate_Click()

There is plenty of other code in this macro as you can see in Pic 4 plenty of Modules. The other code can be supplied if necessary but the error is appearing on the EFPIA_Macro user form

So I have supplied this user forms code only in its entirety below see CODE

again the error is happening on ' create the file on the network folder Name DTOV_Directory & newfilename & "txt" As network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"

Which is located in the Private Sub Private Sub Pre_validate_Click()

I am really struggling to solve this one

So as always any and all help is greatly appreciated.

Thank you

Pic 1

Pic 2

Pic 3

Pic 4

CODE

Dim DTOV_Directory As String
Dim DTOV_fname As String
Dim ITOV_Directory As String
Dim ITOV_fname As String
Dim txtFileName As String


Private Sub Clear_form_Click()
Unload Me
EFPIA_Macro.Show
End Sub

Private Sub Close_form_Click()
Unload Me
ThisWorkbook.Close savechanges:=False
Application.Quit


End Sub

Private Sub DTOV_chkbox_Change()
    If txtFileName = "" Then
       DTOV_chkbox = False
       DTOV_filename = ""
       Call dtov_file_processing
    End If
    txtFileName = ""
End Sub

Private Sub DTOV_chkbox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call dtov_file_processing
End Sub

Private Sub dtov_file_processing()
    Dim fd As Office.FileDialog
    Dim varFile As Variant

    ' check if either of graphical/raw file is selected
    If Graphical_file.Value <> True And Raw_file.Value <> True Then
        MsgBox "Please select the type of file - Graphical/Raw"
        DTOV_chkbox = False
        DTOV_filename = ""
        txtFileName = ""
    ElseIf DTOV_filename <> "" Then
        DTOV_chkbox = False
        DTOV_filename = ""
        txtFileName = ""
    Else
        txtFileName = ""
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        With fd
            .AllowMultiSelect = False
            .Title = "Please select the file."

            ' Clear out the current filters, and add our own.
            .Filters.Clear
            .Filters.Add "All Files", "*.*"
            .Filters.Add "Excel 2003", "*.xls"

            If .Show = True Then
                txtFileName = .SelectedItems(1)
            End If
         End With

         If Graphical_file.Value = True And (InStr(txtFileName, "DTOV") = 0 Or InStr(txtFileName, ".xls") = 0 Or txtFileName = "") Then
            MsgBox "Incorrect DTOV file selected. Please re-select the file"
            DTOV_chkbox = False
            DTOV_filename = ""
         ElseIf Raw_file.Value = True And InStr(txtFileName, ".xls") = 0 Then
            MsgBox "Incorrect RAW file selected. Please re-select the file"
            DTOV_chkbox = False
            DTOV_filename = ""
         Else
            DTOV_filename = txtFileName
            DTOV_chkbox = True
         End If
    End If
End Sub

Private Sub Graphical_file_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    File_category_frame_1.Caption = "Choose DTOV File"
    DTOV_chkbox.Caption = "DTOV (Without Meeting Information)"
    File_category_frame_2.Visible = True
    ITOV_chkbox.Visible = True
    DTOV_chkbox = False
    DTOV_filename = ""
    ITOV_chkbox = False
    ITOV_filename = ""
    txtFileName = ""
End Sub


Private Sub ITOV_chkbox_Change()
    If txtFileName = "" Then
       ITOV_chkbox = False
       ITOV_filename = ""
       Call itov_file_processing
    End If
    txtFileName = ""
End Sub

Private Sub ITOV_chkbox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call itov_file_processing
End Sub

Private Sub itov_file_processing()
    Dim fd As Office.FileDialog

    ' check if either of graphical/raw file is selected
    If Graphical_file.Value <> True And Raw_file.Value <> True Then
        MsgBox "Please select the type of file - Graphical/Raw"
        ITOV_chkbox = False
        ITOV_filename = ""
        txtFileName = ""
    ElseIf ITOV_filename <> "" Then
        ITOV_chkbox = False
        ITOV_filename = ""
        txtFileName = ""
    Else
        txtFileName = ""
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        With fd
            .AllowMultiSelect = False
            .Title = "Please select the file."

            ' Clear out the current filters, and add our own.
            .Filters.Clear
            .Filters.Add "All Files", "*.*"
            .Filters.Add "Excel 2003", "*.xls"

            If .Show = True Then
                txtFileName = .SelectedItems(1)
            End If
         End With

         If InStr(txtFileName, "ITOV") = 0 Or InStr(txtFileName, ".xls") = 0 Then
            MsgBox "Incorrect file selected. Please re-select the file"
            ITOV_chkbox = False
            ITOV_filename = ""
         Else
            ITOV_filename = txtFileName
            ITOV_chkbox = True
         End If
    End If
End Sub

Private Sub Pre_validate_Click()
 Dim newfilename As String
 Dim network_path As String
 Dim final_msg As String

 ' user interface to accept network folder path
 PreVal_Dir_Path.Show
 network_path = EFPIA_Macro.Pre_validate.ControlTipText
 EFPIA_Macro.Pre_validate.ControlTipText = ""
 final_msg = "The below files are submitted for the prevalidation:"

 If Not Dir(network_path, vbDirectory) = vbNullString Then
    DTOV_fname = ""
    ITOV_fname = ""

    ' call the procedure to create files.
    Call Process_template_Click

    ' note - we need to change the destination directory (DTOV_Directory, ITOV_Directory) to the place where pre-val tool n/w folder
    If DTOV_fname <> "" Then
       newfilename = Left(DTOV_fname, InStrRev(DTOV_fname, "."))

       If Dir(DTOV_Directory & newfilename & "txt") <> "" Then
          ' remove the file with same name if already exists on the network folder
          If Dir(network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt") <> "" Then
             Kill network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
          End If

          ' create the file on the network folder
          Name DTOV_Directory & newfilename & "txt" As network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
          final_msg = final_msg & " " & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
       End If

       If Dir(DTOV_Directory & Replace(newfilename, "DTOV", "CUST") & "txt") <> "" Then
          ' get the new generated txt TOV file name
          newfilename = Replace(newfilename, "DTOV", "CUST")
          ' remove the file with same name if already exists on the network folder
          If Dir(network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt") <> "" Then
             Kill network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
          End If

          ' create the file on the network folder
          Name DTOV_Directory & newfilename & "txt" As network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
          final_msg = final_msg & " " & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
       End If
     End If

     If ITOV_fname <> "" Then
        newfilename = Left(ITOV_fname, InStrRev(ITOV_fname, "."))

        If Dir(ITOV_Directory & newfilename & "txt") <> "" Then
           ' remove the file with same name if already exists on the network folder
           If Dir(network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt") <> "" Then
              Kill network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
           End If
           ' create the file on the network folder
           Name ITOV_Directory & newfilename & "txt" As network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
           final_msg = final_msg & " " & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
        End If

        If Dir(ITOV_Directory & Replace(newfilename, "ITOV", "CUST") & "txt") <> "" Then
           ' get the new generated txt TOV file name
           newfilename = Replace(newfilename, "ITOV", "CUST")
           ' remove the file with same name if already exists on the network folder
           If Dir(network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt") <> "" Then
              Kill network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
           End If
           ' create the file on the network folder
           Name ITOV_Directory & newfilename & "txt" As network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
           final_msg = final_msg & " " & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
        End If
     End If

     If DTOV_fname <> "" Or ITOV_fname <> "" Then
        final_msg = final_msg & vbNewLine & "The processing will take upto 10 minutes."
        final_msg = final_msg & vbNewLine & "You will receive email notification once validation process is complete."
        final_msg = final_msg & vbNewLine & "You can track the status of the file and review the error using the cognos browser - link to which will be present in the email to you"
        MsgBox final_msg
     End If

 Else
    MsgBox ("Network folder is un-accessible. Please verify your access or the network folder path")
 End If


End Sub


Private Sub Process_template_Click()

 If DTOV_filename <> "" Then
    DTOV_Directory = Left(DTOV_filename, InStrRev(DTOV_filename, "\"))
    DTOV_fname = Dir(DTOV_filename)
 End If

 If ITOV_filename <> "" Then
    ITOV_Directory = Left(ITOV_filename, InStrRev(ITOV_filename, "\"))
    ITOV_fname = Dir(ITOV_filename)
 End If

 If DTOV_chkbox.Value = True And ITOV_chkbox.Value = True And DTOV_filename <> "" And ITOV_filename <> "" Then
    Call Template_Process.Process_Templates(DTOV_Directory, DTOV_fname, ITOV_Directory, ITOV_fname)
 ElseIf DTOV_chkbox.Value = True And DTOV_filename <> "" And Raw_file.Value = False Then
    Call Template_Process.Process_template(DTOV_Directory, DTOV_fname, "D")
 ElseIf DTOV_chkbox.Value = True And DTOV_filename <> "" And Raw_file.Value = True Then
    Call Process_Raw(DTOV_Directory, DTOV_fname)
 ElseIf ITOV_chkbox.Value = True And ITOV_filename <> "" Then
    Call Template_Process.Process_template(ITOV_Directory, ITOV_fname, "I")
 Else
    MsgBox "No file selected. Please select a file to proceed"
 End If

End Sub


Private Sub Raw_file_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    File_category_frame_1.Caption = "Choose RAW File"
    DTOV_chkbox.Caption = "RAW (Without Graphical Information)"
    DTOV_chkbox = False
    DTOV_filename = ""
    File_category_frame_2.Visible = False
    ITOV_chkbox.Visible = False
    ITOV_filename.Visible = False
End Sub

NEW ERROR PIC

Answer 1

In general, 52 is a bad file name error - https://msdn.microsoft.com/en-us/library/aa231024(v=vs.60).aspx

Thus, probably if you put a dot . between the filename and txt, it could work:

    Name DTOV_Directory & newfilename & ".txt" As network_path & _
    Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & ".txt"

Answer 2

You did not provide exact file paths (one of them is invalid [=Error 52]), but you can help yourself by replacing failing statement

Name DTOV_Directory & newfilename & "txt" As network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt" 

with (temporarily) expanded equivalent:

' assign file names to variables and perform renaming
oldFileName = DTOV_Directory & newfilename & "txt" 
newFileName = network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"
Name oldFileName As NewFileName

When program is stopped (using Stop statement or (better) using a breakpoint – F9 key), hover the mouse over variable names to inspect their content (or use watches or print command in Immediate window, ...whatever).

Maybe you will find these potential issues:

• missing dot in file extension
• missing backslashes separating directory from the file
• other issues, like problem with network path.

---

Edit: Error handling

In sub Pre_validate_Click(), you have 3 Name commands. Surround each by On Error Goto:

On Error Goto RenameError ' error handling on
Name ... As ...
On Error Goto 0  ' error handling off

And just before End Sub at the end of the subroutine add the following:

Exit Sub

RenameError:
If Err.Number = 53 Then
    details = " when renaming from " & oldFileName & " to " & newFileName
Else
    details = ""
EndIf
answer = MsgBox("Error " & Err.Number & ": " & Err.Description & details, vbRetryCancel Or vbCritical)
If answer = vbRetry Then
    Resume  ' retry problematic line again
Else
    End ' terminate the program
End If