VBA Importing Data from Web into Excel2007

[Bill Mochal]

I have a VBA macro that imports data from a long (600+)series of web pages into an Excel 2007 workbook.
There are occasional errors each time the macro is run such as: "The Web query returned no data. To change the query... etc." or "Unable to open... etc."
When these occur, a dialouge box with an "OK" button pops up and the macro waits until I click OK.

I'm looking for a modification to the VBA code that would ignore these web page access errors and let the macro run continuously through the long list of webpages to the end.

Thanks!

[NeoPa]

How about you post the code you want help with modifying, then I'll take that and edit into your question post for you where it should have been. Sound like an idea?

[Bill Mochal]

Thanks, NeoPa

Thanks, NeoPa, I will post the VBA code below. I should clarify one thing - the VBA code is only executed once as an Excel macro to initialize the queries. For subsequent data imports into Excel, I only use the "Data Refresh All" function in Excel and that is when I get those various errors.

OK, here's the code:

Sub Macro4()
Dim conString As String
Dim conName As String
Dim txtSymbols(0, 700) As String

txtSymbols(0, 1) = "A"
txtSymbols(0, 2) = "AAPL"
txtSymbols(0, 3) = "AATI"
txtSymbols(0, 4) = "ABAT"
txtSymbols(0, 5) = "ACH"
txtSymbols(0, 6) = "ACLS"
txtSymbols(0, ...) = "..."
txtSymbols(0, 685) = "GURE"
txtSymbols(0, 686) = "KUTV"
txtSymbols(0, 687) = "CHNR"
txtSymbols(0, 688) = "TBOW"
txtSymbols(0, 689) = "EFUT"

For i = 1 To 689

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = txtSymbols(0, i)

conString = "URL;http://finance.yahoo.com/q/ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
conName = "ks?s=" & txtSymbols(0, i) & "+Key+Statistics"

With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$A$1"))
.Name = conName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next i

End Sub

[NeoPa]

Well, Bill, as you say these errors happen when you're not actually running the code there is little scope for helping by changing the code.

If you know this never happens when the code is run then I would consider running the code every time instead and ensuring that the QueryTables() object added is not saved with the workbook (That will probably reduce the size of the file somewhat too I expect). You can easily set the code to be executed whenever the workbook is opened if you choose.

[Bill Mochal]

I suspect the same errors will occur if the code is re-run (after all, a page that can't be accessed, can't be accessed), but it's worth a try.
Thanks again!

[NeoPa]

If that turns out to be the case then you will at least be in a position to look at what's happening more closely.

[Bill Mochal]

Your suggestion worked, NeoPa.

There's one issue left.

** Post edited to split the follow-up question into a separate thread - How do I Manage Worksheets Within a Workbook **

[NeoPa]

Bill, resetting a Best Answer because you have a follow-up question is not the way to go. If you feel that for any reason the post was not as valuable as you'd previously understood, then go for it. No issues there. Each thread has only one question though (otherwise they get pretty useless pretty fast for the site that's hosting these resources).

I'll move your new question to a separate thread for you this time, but I'll leave you to remember that for future questions and handle the Best Answer situation as you see fit for this one.