Refreshable Data Connection from Web to Excel TABLE

I've found the Get External Data From Web to work only in a limited set of circumstances. Can you attach a sample workbook of what it does bring down?

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Can you change names or whatever so as to desensitize the data? I just need to see what the format of the data looks like. If you can fill it in with dummy values, it should be enough to suggest a solution.

There are two ways to do this:
1. Attempt to make a table out of the range
2. Cheat: copy the range contents into a pre-existing table.

My adage is "If you can't play and cheat, don't play." So I opted for way 2.

Set the table page up with the same column headers that you will be importing. The program will figure out how many rows and columns you are importing.

I commented the code where you may want to change it in case you have different sheet names or table name. Also, in determining the last row, in the example the last row is a comment and I will assume you don't want it. So there is a piece of code that looks like:
' LRowS = LRowS -1
If you take away the single quote, this becomes a command instead of a comment, and you will not copy the last row.

Please Login or Register  to view this content.

The second issue is related to the first. The ClearTable subroutine removes all data from the table, leaving only the headers and the "format" (formats, formulas, etc.). So copying the new data in, even if it has fewer rows than what was there, won't be an issue.

As for the error: I can't duplicate it. Can you include the line of code that is highlighted in the debugger?

I need to see the actual line that is highlighted. I've been using this code for several years now and have included it in many of my solutions. I've never seen that particular error. I'd need to drill down to see what it might be getting indigestion over. I'm running this code on 2013 as well.