By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,656 Members | 1,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,656 IT Pros & Developers. It's quick & easy.

VBA Importing Data from Web into Excel2007

P: 8
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!
Nov 1 '11 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,712
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?
Nov 2 '11 #2

P: 8
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:

Expand|Select|Wrap|Line Numbers
  1. Sub Macro4()
  2. Dim conString As String
  3. Dim conName As String
  4. Dim txtSymbols(0, 700) As String
  5.  
  6. txtSymbols(0, 1) = "A"
  7. txtSymbols(0, 2) = "AAPL"
  8. txtSymbols(0, 3) = "AATI"
  9. txtSymbols(0, 4) = "ABAT"
  10. txtSymbols(0, 5) = "ACH"
  11. txtSymbols(0, 6) = "ACLS"
  12. txtSymbols(0, ...) = "..."
  13. txtSymbols(0, 685) = "GURE"
  14. txtSymbols(0, 686) = "KUTV"
  15. txtSymbols(0, 687) = "CHNR"
  16. txtSymbols(0, 688) = "TBOW"
  17. txtSymbols(0, 689) = "EFUT"
  18.  
  19. For i = 1 To 689
  20.  
  21. Sheets.Add After:=Sheets(Sheets.Count)
  22. ActiveSheet.Name = txtSymbols(0, i)
  23.  
  24. conString = "URL;http://finance.yahoo.com/q/ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
  25. conName = "ks?s=" & txtSymbols(0, i) & "+Key+Statistics"
  26.  
  27. With ActiveSheet.QueryTables.Add(Connection:=conString, Destination:=Range("$A$1"))
  28. .Name = conName
  29. .FieldNames = True
  30. .RowNumbers = False
  31. .FillAdjacentFormulas = False
  32. .PreserveFormatting = True
  33. .RefreshOnFileOpen = False
  34. .BackgroundQuery = True
  35. .RefreshStyle = xlInsertDeleteCells
  36. .SavePassword = False
  37. .SaveData = True
  38. .AdjustColumnWidth = True
  39. .RefreshPeriod = 0
  40. .WebSelectionType = xlSpecifiedTables
  41. .WebFormatting = xlWebFormattingNone
  42. .WebTables = """yfncsubtit"",8,10,11,13,15,17,19,21,23"
  43. .WebPreFormattedTextToColumns = True
  44. .WebConsecutiveDelimitersAsOne = True
  45. .WebSingleBlockTextImport = False
  46. .WebDisableDateRecognition = False
  47. .WebDisableRedirections = False
  48. .Refresh BackgroundQuery:=False
  49. End With
  50. Next i
  51.  
  52. End Sub
Nov 12 '11 #3

NeoPa
Expert Mod 15k+
P: 31,712
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.
Nov 12 '11 #4

P: 8
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!
Nov 15 '11 #5

NeoPa
Expert Mod 15k+
P: 31,712
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.
Nov 16 '11 #6

P: 8
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 **
Dec 21 '11 #7

NeoPa
Expert Mod 15k+
P: 31,712
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.
Dec 22 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.