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

Based on your correction, it culminates to what I said in my answer :) — Siddharth Rout, Dec 13 '13 at 20:02

score 2 · Answer 1 · edited May 23 '17 at 11:48

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

Thank you very much! That did the trick. Actually, what caused this problem in the first place is that I had left off the last slash character at the end of my path. I will update this post with my full code to show what it looks like now. — skyline01, Dec 13 '13 at 19:13
No don't update my post :) you can explain that by updating your question — Siddharth Rout, Dec 13 '13 at 19:15

VBA: .Refresh Run-Time Error

1 Answers1

Linked