QueryTable: Always getting Error 1004 after first bad refresh despite valid connections

[Apple2Plus]

Hello,

I am using Excel 2007 to work on a problem which might be shared by others; that is, to retrieve delimited data (tab, comma, etc) from text files but that are at remote locations. The files are obtained by http or ftp protocol though I have not yet tried with ftp.

Instead of using a URL connection type with a URL string (Eg: connection:="URL;http://www.awebsitename.com/filename.txt"), I am unconventionally using a TEXT connection with the URL string (i.e. connection:="TEXT;http://www.awebsitename.com/filename.txt").

Looping over a list of files, this method works well until you attempt to connect to a file that doesn't exist. At this instant, you get Error 1004 and then you get Error 1004 for all subsequent files you attempt to connect to even though they do truely exist and are accessible.

Below you will find the code I created to accomplish this. The error occurs at the line refreshing the query table:
".Refresh BackgroundQuery = False"

Am I missing something? Currently the only way of proceeding is to close Excel, re-open it, remove the bad file from the list, then resume at the next valid file.

Does anyone have some insight into this?

Thanks in advance!

Function downloadDataToSheet(ByRef urlString As String, ByRef destinationSheet As Worksheet) As Boolean
 
    Dim success As Boolean
    success = true ' start optimistically
 
On Error GoTo EH
 
    ' Clear any previous data from the destination sheet
    clearAllDataFromSheet destinationSheet
 
    Dim connectionString As String
    connectionString = "TEXT;" + urlString
    Dim pQueryTable As QueryTable
    Set pQueryTable = destinationSheet.QueryTables.Add(Connection:=connectionString, Destination:=destinationSheet.Range("A1"))
 
    If Not (pQueryTable Is Nothing) Then
        With pQueryTable
            .Name = "downloadTable"
            .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 = True
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery = False
        End With
    Else
        ' Couldn't add the QueryTable
        success = false
    End If
EH:
    If Err.Number <> 0 Then
 
        If (True) Then
            Msg = "Error # " & Str(Err.Number) & " was generated by " _
                    & Err.Source & Chr(13) & Err.Description
            MsgBox Msg, , Error, Err.HelpFile, Err.HelpContext
        End If
 
        success = false
 
    End If
 
    downloadDataToSheet = success
 
End Function
 
Sub clearAllDataFromSheet(ByRef aSheet As Worksheet)
 
On Error GoTo EH1
 ' Remove all data connections
 Dim i As Long
 For i = (ActiveWorkbook.Connections.Count) To 1 Step -1
     ActiveWorkbook.Connections.Item(i).Delete
 Next i
 
EH1:
 
On Error GoTo EH2
 ' Remove all query tables
 aSheet.Range("A:IV").QueryTable.Delete
 
EH2:
 
 aSheet.Range("A:IV").ClearContents
 
End Sub

 

[Apple2Plus]

Looks like similar problems exist: http://www.mrexcel.com/forum/showthread.php?t=338613