excel - VBA: .Refresh Run-Time Error

Question

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

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!

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

Answer 1

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

to

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

You fname just has the name of the file and not the full path

Also avoid using .Select and fully qualify your Objects. INTERESTING READ

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