Excel VBA判断指定Excel文件是否存在并填充对应值的实现咨询

原需求说明 #

• 每4周为客户生成一次仓储费用账单，需调用对应4周的仓库数据文件
• 无货运业务的周次不会生成对应Excel文件
• 需新增逻辑：宏运行时先校验指定周次的文件是否存在，不存在则对应单元格填充–
• 以下为虚构客户的原有业务代码，实际场景可直接复用逻辑

原有代码 #

Windows("periode 1 factuur ABC123.xlsx").Activate
Range("B11").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R12C2"
Range("B14").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R14C2"
Range("B15").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R15C2"
Range("B17").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R17C2"
Range("B19").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R19C2"
Range("B20").Select
ActiveWindow.Close
Application.Left = -5
Application.Top = -5
Application.Width = 1452
Application.Height = 792
Sheets("factuur").Select
Windows("periode 1 factuur ABC123.xlsx").Activate
Range("C11").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R12C2"
Range("C14").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R14C2"
Range("C15").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R15C2"
Range("C17").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R17C2"
Range("C18").Select
ActiveCell.FormulaR1C1 = ""
Range("C19").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R19C2"
Range("C20").Select
Windows("periode 1 factuur ABC123.xlsx").Activate
Windows("ABC123 week 4-2021.xlsx").Activate
Sheets("factuur").Select
Windows("periode 1 factuur ABC123.xlsx").Activate
Range("E11").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R12C2"
Range("E14").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R14C2"
Range("E15").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R15C2"
Range("E17").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R17C2"
Range("E19").Select
ActiveCell.FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R19C2"
Range("E20").Select

优化后可直接使用的代码 #

' 辅助函数：判断指定路径的文件是否存在
Function FileExists(filePath As String) As Boolean
    FileExists = Dir(filePath) <> ""
End Function

' 主程序
Sub 生成账单数据()
    Dim targetWB As Workbook
    Dim dataPath As String
    ' 请修改为你的数据文件存放文件夹路径，注意末尾加\
    dataPath = "C:\你的仓储数据文件存放路径\"
    ' 绑定当前账单工作簿，避免反复Activate
    Set targetWB = Workbooks("periode 1 factuur ABC123.xlsx")
    Dim factuurSheet As Worksheet
    Set factuurSheet = targetWB.Sheets("factuur")
    
    ' 处理第1周数据，对应B列
    Dim week1File As String
    week1File = dataPath & "ABC123 week 1-2021.xlsx"
    If FileExists(week1File) Then
        factuurSheet.Range("B11").FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R12C2"
        factuurSheet.Range("B14").FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R14C2"
        factuurSheet.Range("B15").FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R15C2"
        factuurSheet.Range("B17").FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R17C2"
        factuurSheet.Range("B19").FormulaR1C1 = "='[ABC123 week 1-2021.xlsx]factuur'!R19C2"
    Else
        factuurSheet.Range("B11,B14,B15,B17,B19").Value = "–"
    End If
    
    ' 处理第2周数据，对应C列
    Dim week2File As String
    week2File = dataPath & "ABC123 week 2-2021.xlsx"
    If FileExists(week2File) Then
        factuurSheet.Range("C11").FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R12C2"
        factuurSheet.Range("C14").FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R14C2"
        factuurSheet.Range("C15").FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R15C2"
        factuurSheet.Range("C17").FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R17C2"
        factuurSheet.Range("C19").FormulaR1C1 = "='[ABC123 week 2-2021.xlsx]factuur'!R19C2"
        factuurSheet.Range("C18").Value = ""
    Else
        factuurSheet.Range("C11,C14,C15,C17,C18,C19").Value = "–"
    End If
    
    ' 处理第4周数据，对应E列
    Dim week4File As String
    week4File = dataPath & "ABC123 week 4-2021.xlsx"
    If FileExists(week4File) Then
        factuurSheet.Range("E11").FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R12C2"
        factuurSheet.Range("E14").FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R14C2"
        factuurSheet.Range("E15").FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R15C2"
        factuurSheet.Range("E17").FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R17C2"
        factuurSheet.Range("E19").FormulaR1C1 = "='[ABC123 week 4-2021.xlsx]factuur'!R19C2"
    Else
        factuurSheet.Range("E11,E14,E15,E17,E19").Value = "–"
    End If
    
    ' 保留原有窗口设置逻辑
    Application.Left = -5
    Application.Top = -5
    Application.Width = 1452
    Application.Height = 792
    targetWB.Activate
End Sub

使用说明 #

1. 把代码里的dataPath变量值替换为你实际存放周次数据Excel文件的文件夹路径，注意路径末尾要加反斜杠\
2. 如果后续需要修改客户编号、年份或者周次，直接替换对应位置的文件名即可，无需修改整体逻辑
3. 代码已经移除了原逻辑中大量不必要的Activate和Select操作，运行效率更高，不会出现窗口反复跳转的情况

内容的提问来源于stack exchange，提问作者Thom Haasert