VBA：.Refresh 运行时错误

VBA: .Refresh Run-Time Error

提问by skyline01

I am having a problem with some VBA code. I'm running Excel 2010 on Windows 7 Enterprise.

我遇到了一些 VBA 代码的问题。我在 Windows 7 Enterprise 上运行 Excel 2010。

I'm trying to read in several tab-delimited text files from a folder and put them onto separate sheets in one Excel workbook. To do this, I'm using a Query Table. In debugging, I have a problem with .Refresh BackgroundQuery:=False. When it reaches this line, it throws a 1004 run-time error, stating that Excel cannot find the text file to refresh this external data range. I don't know why this is occurring. I know that the Query Table isn't created until it reads this line, which makes debugging difficult. Here is the code. Any help would be much appreciated. Thanks in advance!

我试图从一个文件夹中读取几个制表符分隔的文本文件，并将它们放在一个 Excel 工作簿中的单独工作表上。为此，我使用了查询表。在调试中，我有一个问题.Refresh BackgroundQuery:=False。当它到达这一行时，它会抛出一个 1004 运行时错误，指出 Excel 找不到用于刷新此外部数据范围的文本文件。我不知道为什么会发生这种情况。我知道在读取此行之前不会创建查询表，这使得调试变得困难。这是代码。任何帮助将非常感激。提前致谢！

Sub LoadPipeDelimitedFiles()
Dim idx As Integer
Dim fname As String

idx = 0
fname = Dir("C:\files\*.txt")
While (Len(fname) > 0)
    idx = idx + 1
    Sheets("Sheet" & idx).Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname, Destination:=Range("A1"))
        .Name = "a" & idx
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        fname = Dir
    End With
Wend
End Sub

Here is the correction:

这是更正：

Sub LoadPipeDelimitedFiles()
Dim idx As Integer
Dim fpath As String
Dim fname As String
Dim f_dummy As String

idx = 0
fpath = "C:\files\"
f_dummy = fpath & "*.txt"
fname = Dir(f_dummy)
While (Len(fname) > 0)
    idx = idx + 1
    Sheets("Sheet" & idx).Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
      & fpath & fname, Destination:=Range("A1"))
        .Name = "a" & idx
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        fname = Dir
    End With
Wend
End Sub

回答by Siddharth Rout

Change the line With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname, Destination:=Range("A1"))

换线 With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname, Destination:=Range("A1"))

to

到

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "C:\files\" & fname, Destination:=Range("A1"))

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "C:\files\" & fname, Destination:=Range("A1"))

You fnamejust has the name of the file and not the full path

你fname只有文件名而不是完整路径

Also avoid using .Selectand fully qualify your Objects. INTERESTING READ

还要避免使用.Select并完全限定您的Objects. 有趣的阅​​读

Your code can be written as

你的代码可以写成

Sub LoadPipeDelimitedFiles()
    Dim idx As Integer
    Dim fname As String, FullName As String
    Dim ws As Worksheet

    idx = 0

    fname = Dir("C:\*.txt")

    While (Len(fname) > 0)
        FullName = "C:\" & fname
        idx = idx + 1

        Set ws = ThisWorkbook.Sheets("Sheet" & idx)

        With ws.QueryTables.Add(Connection:="TEXT;" & _
        FullName, Destination:=ws.Range("A1"))
            '
            '~~> Rest of the code
            '
            fname = Dir
        End With
    Wend
End Sub