VBA Excel 错误运行时错误“53”

我希望你能帮忙。我有一个宏，它很广泛。本质上它的作用是它有一个用户表单参见图 1

此用户表单允许用户导航到存储数据的模板（参见图 2）并选择此已完成的模板。

在图 1 中的用户表单上。用户然后单击单选按钮 DTOV（无会议信息），然后单击按钮预验证。

单击此预验证按钮后，Excel 模板上的数据将从该 Excel 文件中复制，并转换为带有 | 的文本文件。（管道）分隔符，并要求用户确认这个新创建的文本文件所在的目录路径。见图3

应该发生的是，这个新创建的文本文档被放置到一个文件夹中，在该文件夹中再次提取文本文档并将其发送到另一个数据库

我遇到的问题是新创建的文本文件没有进入其他数据库要选取的文件夹。

我收到一个错误运行时错误“53”这是在线发生的

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

位于私人潜艇 Private Sub Pre_validate_Click()

这个宏中有很多其他代码，你可以在图 4 中看到很多模块。如有必要，可以提供其他代码，但错误出现在 EFPIA_Macro 用户表单上

所以我只在下面完整地提供了这个用户表单代码，请参阅代码

再次发生错误 ' create the file on the network folder Name DTOV_Directory & newfilename & "txt" As network_path & Replace(newfilename, "EFPIA", "EFPIA_PVLDTN") & "txt"

位于私人潜艇 Private Sub Pre_validate_Click()

我真的很难解决这个问题

因此，一如既往地非常感谢任何和所有帮助。

谢谢

图1

图2

图3

图4

代码

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

新的错误图片