VBA QueryTables

**mrdomer07** · 02-18-2019, 01:38 PM

I have a macro that uses QueryTables. I am a novice with VBA and basically took this code from a webpage. My macro works fine most of the time, but sometimes excel will stop responding. When this happens, the debugger takes me to .Refresh BackgroundQuery:=False from the below code. If I shut down excel and start it back up again and run the exact same macro again, it usually works fine so I do not think it has anything to do with incorrect URLs passing through rhyperlink.

Here is an excerpt of the code that is having the issue:

Please Login or Register  to view this content.

What I am ultimately asking is 1) any ideas what could be causing excel to stop responding and require me to shut it down on occasion? 2) can someone explain what each of the properties are doing in this code (other than .Add which is self-explanatory to me)?

As a side note, this code is inside of a loop and rhyperlink is updated in each loop. For each unique rhyperlink, there is a named range that gets created (actually 2 because I copy the querytable to another tab in each loop). Is there a way to prevent each querytable resulting in a new named range - is it related to .SaveData = True?

**Leith Ross** · 02-18-2019, 03:34 PM

Hello mrdomer07,

Your code is simply copying tables from a website to the ActiveSheet. If the website fails to respond and maintains an open connection, it will hang Excel. There are no properties you can set in the Query to "cancel" the operation if this situation arises.

There are other ways to copy web tables. You should post the full code the Query is in. It is possible there may be some other code causing problems with the Query. The best option would be to attach a copy of the workbook for review. If your workbook contains sensitive, confidential, or personal information then redact or change it before you post.

**mrdomer07** · 02-18-2019, 05:20 PM

Hi Leith - Here is my spreadsheet. I deleted most of the tabs in the file to make it more manageable but I do not think it broke anything. I ran the Macro_Scrape macro on the InputMacro tab as a test and it worked with no problem. As a quick tutorial, B2 of the InputMacro tab dictates the first URL link that will be generated. B3 dictates the last URL that will be generated. So if you start with B2 = 4, then if you set B3 = 10 then it will run 7 loops and scrape 7 web pages.

The macro is fairly cobbled together with code I found here and there but it works. Any suggestions you have to make the QueryTables section more stable/reliable or to generally improve the code would be very much appreciated. Thanks in advance!