excel - VBA 无法识别 Excel 文件

Question

该错误发生在Open.Workbooks MasterFile行上。我收到“找不到此文件。它是否已被删除、重命名或替换？”的错误消息。我使用 fso.FileExist 函数来确保文件确实存在，并且当我 debug.print 时，文件名与变量 MasterFile 中指定的完全相同。为什么 VBA 不能识别这个 .xlsm 文件？

Sub ProcessData()
Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim fil As Scripting.File

Dim MyDir As String
Dim FolderSource As Scripting.Folder
'FolderSource is the fso enabled directory folder
Dim FolderDataSource As Scripting.Folder
'FolderDest is the fso enabled folder where the MasterFile is located

MyDir = "C:\Users..."
'MyDir is where all part number files will be stored

Dim MasterFile As String
Dim MasterFilePath As String

MasterFilePath = "C:\Users..."
MasterFile = "Function Master File.xlsm"

Dim wbSource As Workbook, wsSource As Worksheet
'The workbook and worksheet source will be the Master FIle

Dim wbDest As Workbook, wsDest As Worksheet
'The destination workbook and worksheet corresponding to the part number file

Dim myArray As Variant
Dim myTable As ListObject
Dim x As Long

myArray = Range("D13:D17")
'Array will draw from part number values that are entered in the appropriate area on the template

'Creating files for all new part numbers
Set FolderSource = fso.GetFolder(MyDir)
    'For Each fil In FolderSource.Files
        For x = LBound(myArray) To UBound(myArray)
            If myArray(x, 1) <> "" Then
                PartNumFile = MyDir & "\" & myArray(x, 1) & ".xlsx"
                    If Not fso.FileExists(PartNumFile) Then
                    Set newbook = Workbooks.Add
                        With newbook
                        .SaveAs Filename:=PartNumFile
                        End With

                    End If
            End If
            'Searching for the Part Number Data in the Master File
            Set FolderDataSource = fso.GetFolder(MasterFilePath)
            For Each fil In FolderDataSource.Files
            Debug.Print fil.Name

                If fso.FileExists(MasterFilePath & "\" & MasterFile) Then
                Debug.Print fil.Name
                        Workbooks.Open MasterFile
                        Set wbSource = Workbooks(MasterFile)
                        Set wsSource = wbSource.Worksheets(1)
                            lrSource = wsSource.Range("A" & wsSource.Rows.Count).End(xlUp).Row
                        Set wbDest = Workbooks(myArray(x, 1) & ".xlsx")
                        Set wsDest = wbDest.Worksheets(1)
                            lrDest = wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Row + 1
                        wsSource.Range("A2:V" & lrSource).Copy Destination:=wsDest.Range("A" & lrDest)
                End If
            Next
        Next


End Sub

标签： excelvba