"Unexpected error" in web query

[krice1974]

Hi all. This is a slight modification of a great macro from Mr Jelen's book. It has been working, but today I'm getting an error. I've just made some very minor changes, but none pertaining to the source of the error (at least, I think.) The error is the last line. It halts and the query doesn't refresh. Any ideas?

Sub Update_Stock_List_Web_Query()
    
    'This updates the master stock list pricing/ volume info. It rebuilds the stock list string and
    'rebuilds the query every time its run.
    
        Dim QT As QueryTable
        Dim MSLWfinalrow As Long
        Dim MSLfinalrow As Long
        Dim i As Integer
        Dim connectstring As String
        Dim finalresultrow As Long
        
        Set MSL = Worksheets("MasterStockList")
        'Set WDI = Worksheets("WorkspaceDailyInfo")
        Set MSLW = Worksheets("MSLWorksp")
        'Read column A of master stock list to find all symbols.
        
        'Convert the data to a table to remove the duplicates.
        ConvertRangeToTableSub "MasterStockList", 4
        
        'Remove the duplicates
        MSL.Range("MasterStockListTable[#All]").RemoveDuplicates Columns:=Array(1), Header:=xlYes
        
        'Convert back to a range (unlist)
        ConvertTableToRangeSub "MasterStockList"
        
        'Add the date added.
        AddDateColumn "MasterStockList", 4, 1
        
        MSLfinalrow = MSL.Cells(Rows.count, 1).End(xlUp).Row
        
        For i = 8 To MSLfinalrow
            Select Case i
                Case 8
                    connectstring = "URL;http://finance.Yahoo.com/q/cq?d=v1&s=" & MSL.Cells(i, 1).Value
                Case Else
                    connectstring = connectstring & "%2c+" & MSL.Cells(i, 1).Value
            End Select
        Next i

        'Delete the old query.
        
        MSLWfinalrow = MSLW.Cells(Rows.count, 1).End(xlUp).Row
                
        MSLW.Activate
        
        MSLW.Range(MSLW.Cells(1, 1), MSLW.Cells(MSLWfinalrow, 1)).EntireRow.Delete
        
        'Define a new web query
        
        Set QT = MSLW.QueryTables.Add(Connection:=connectstring, Destination:=MSLW.Range("A1"))
        
        With QT
            .Name = "Portfolio"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "11"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = True
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
        End With
        
        'Refresh the query
        QT.Refresh BackgroundQuery:=False

 

[krice1974]

Re: "Unexpected error" in web query macro works on other files?

Hi all. Here's an update to my issue if anyone could help. After going over and over my code (I might have made a couple very minor tweaks since my post last night), I ran it and stepped through it on a backup copy of the file, and it works great. On my main copy I get the unexpected error 1004 on the query refresh. I've done far too much work to the new file to just work off of the backup though... any idea what is causing this? It's got me quite stuck.

 

[krice1974]

Re: Excel bug found?

Another update, this has been an interesting process. I converted over to the working version/ working file to make that my current file, and as I said the issue originally posted was solved. When I add a symbol to the list, it performs as it should. BUT. When I add by pasting a list of symbols (multiple values) to the symbol list (which is a table, if it matters), the unexpected error then occurs. Might be one for the pros?