VBA运行时错误1004：文件路径显示不全的解决方法咨询

原代码触发的运行时错误1004提示路径被截断，是因为系统默认错误提示框有长度限制，无法完整显示长路径。可以通过两种方式解决：

方法1：提前验证文件是否存在 #

在创建Power Query查询前，先检查目标文件是否存在，直接给出带完整路径的自定义提示：

使用Dir函数（无需额外引用） #

tableFilePath = "SomeTableFilePathName"
' 提前检查文件有效性
If Dir(tableFilePath) = "" Then
    MsgBox "指定文件不存在，完整路径：" & vbCrLf & tableFilePath, vbCritical, "路径错误"
    Exit Sub ' 终止后续代码执行
End If

' 以下是原代码的查询创建、工作表添加等逻辑
ActiveWorkbook.Queries.Add Name:="SomeTableName" & j, Formula:= _
  "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & tableFilePath & """),[Delimiter="","", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", Int64.Type}, {""Column2"", type number}, {""Column3"", type number}, {""Column4"", type number}" & _
  ", {""Column5"", type number}, {""Column6"", type number}, {""Column7"", type text}, {""Column8"", type number}, {""Column9"", type number}, {""Column10"", type number}, {""Column11"", type number}, {""Column12"", type text}, {""Column13"", type number}, {""Column14"", type number}, {""Column15"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
' ... 后续原代码

使用FileSystemObject（更可靠，支持网络路径） #

tableFilePath = "SomeTableFilePathName"
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(tableFilePath) Then
    MsgBox "指定文件不存在，完整路径：" & vbCrLf & tableFilePath, vbCritical, "路径错误"
    Set fso = Nothing
    Exit Sub
End If
Set fso = Nothing

' 以下是原代码的查询创建、工作表添加等逻辑
' ...

方法2：捕获运行时错误并自定义提示 #

如果提前检查存在遗漏（比如文件在检查后被删除），可以通过错误捕获机制拦截系统错误，替换为带完整路径的提示：

tableFilePath = "SomeTableFilePathName"

On Error Resume Next
' 创建查询
ActiveWorkbook.Queries.Add Name:="SomeTableName" & j, Formula:= _
  "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & tableFilePath & """),[Delimiter="","", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", Int64.Type}, {""Column2"", type number}, {""Column3"", type number}, {""Column4"", type number}" & _
  ", {""Column5"", type number}, {""Column6"", type number}, {""Column7"", type text}, {""Column8"", type number}, {""Column9"", type number}, {""Column10"", type number}, {""Column11"", type number}, {""Column12"", type text}, {""Column13"", type number}, {""Column14"", type number}, {""Column15"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

If Err.Number <> 0 Then
    MsgBox "创建查询失败：指定文件不存在，完整路径：" & vbCrLf & tableFilePath, vbCritical, "错误"
    Err.Clear
    On Error GoTo 0
    Exit Sub
End If
On Error GoTo 0

' 添加工作表并刷新查询
ActiveWorkbook.Worksheets.Add
On Error Resume Next
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
  "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SomeTableName" & j & ";Extended Properties=""""" _
  , Destination:=Range("$A$1")).QueryTable
  .CommandType = xlCmdSql
  .CommandText = Array("SELECT * FROM [SomeTableName]")
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .PreserveColumnInfo = True
  .ListObject.DisplayName = "SomeTableName"
  .Refresh BackgroundQuery:=False
End With

If Err.Number = 1004 Then
    MsgBox "刷新查询失败：指定文件不存在，完整路径：" & vbCrLf & tableFilePath, vbCritical, "错误"
    ActiveSheet.Delete ' 清理新建的空工作表
    Err.Clear
End If
On Error GoTo 0

Application.CommandBars("Queries and Connections").Visible = False
ActiveSheet.Name = "SomeTableName"

原理说明 #

系统默认的错误提示是Power Query返回后经VBA处理的截断版本，长度有限。通过自定义错误提示，直接使用代码中tableFilePath变量存储的完整路径，就能避免截断问题。

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