vba - How can I post-process the data from an Excel web query when the query is complete?

Question

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.

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?

Answer 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.)

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.