0

As a spreadsheet developer, I am trying to stitch together two sets of rows: one from a web query to a web service I own, and the other a set of manual rows added by the spreadsheet's user (not me).

Excel's built in Web Query / Connections object only provides two modes: I can turn on "Enable background refresh" which makes the web query asynchronous, or uncheck it.

Excel web query properties dialog

With it unchecked, Excel freezes up while the query executes, which is undesireable. With it checked, there doesn't seem to be any kind of callback or event hook available to be notified, so that I can operate against the refreshed web data.

Is there another way to do this?

mcw
  • 3,500
  • 1
  • 31
  • 33

2 Answers2

1

Excel supports the ability to open a URL as another Excel workbook, via the Workbooks.Open method:

From MSDN:

Sub OpenUSDRatesPage()
   Dim objBK As Workbook
   Dim objRng As Range

   'Open the page as a workbook.
   Set objBK = Workbooks.Open("http://www.x-rates.com/tables/USD.HTML")

   'Find the Canadian Dollar cell.
   Set objRng = objBK.Worksheets(1).Cells.Find("Canadian Dollar")

   'Retrieve the exchange rate.
   MsgBox "The CAD/USD exchange rate is " & objRng.Offset(-6, -1).Value
End Sub

The call is synchronous, so you can operate on the resulting data in the new workbook immediately after the Open call.

While the workbook is loading, Excel will display a progress bar. When you're done, you can call .Close to close the web data workbook. (e.g., for the MSDN example, you'd call objBK.Close when you're done.)

enter image description here

The caveats of using this approach:

  1. You're on the hook to migrate the data from the web workbook to your own (ThisWorkbook) yourself, unlike a refreshable Excel Web Query that has a set destination.
  2. If your web endpoint has a document name that matches the name of a document open in Excel, the user will get a warning that a document with the same name is open.
mcw
  • 3,500
  • 1
  • 31
  • 33
1

An Excel web query utilizes an object called a QueryTable to carry out the business of retrieving and displaying the data.

A QueryTable can be accessed by VBA.

And just like the chart object a querytable object has events that can only be responded to by using the WithEvents keyword from a class module, like so:

Private WithEvents MyQueryTable As QueryTable

Private Sub MyQueryTable_AfterRefresh(ByVal Success As Boolean)
    'Do your post processing here...
End Sub
Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • I got this to work, but only after adding in some wire up code. Following the guidance from this forum thread: http://www.mrexcel.com/forum/excel-questions/331550-afterrefresh-help-where-does-code-go.html, I needed to add a sub to the class module to hook the event capture on the QueryTable of interest. Then I needed to make sure I called that hookup routine before kicking off a refresh. That works ok for me because we trigger the refresh by a form button, but I can't figure out how to guarantee the AfterRefresh fires if I instead click Excel's Refresh All in the ribbon? – mcw Oct 06 '15 at 19:36
  • Yes, those are things you would do when working with a class module the purpose of which is to trap object events with the WithEvents keyword. The AfterRefresh() event procedure should fire whenever the table is refreshed... no matter how that occurs. It can be from Excel's user interface or from code. – Excel Hero Oct 06 '15 at 19:44
  • I'm not seeing that. Here's [a github repo](https://github.com/mcw0933/StackOverflowQuestion32974553) where I try it both ways. I only see that the _AfterRefresh event is being fired when I specifically hook it up first. – mcw Oct 06 '15 at 20:25
  • You need to make a change. Place `Dim c As Class1` at the very top of the standard code module, instead of inside a procedure. This keeps it in scope. In addition you should reconfigure your code so that `c` is instantiated when the workbook opens. But yes, you will need `c` alive in order for the class to do anything. – Excel Hero Oct 06 '15 at 22:33