Query returned more data than will fit

[John Reyes]

I have a query that I "Refresh Data" everyday without any
problems. Today I attempted to refresh the data & got the
following error msg: "The query returned more data than
will fit on a worksheet". I doubled checked the address to
the server that contains the Web Page data & it's correct.
The table's 14 rows is constant, the column's represent
days of the month & I'm up to column "W". It won't refresh
past this column.
At the end of the month the table is saved to a file & a
another table for the new month is started with the same
query starting on day 1. I have been running this query
for about a year without a hitch until today.
Any help greatly appreciated.

 

[Nick Hodge]

John

Shot in the dark. XL may think it has more data than it does. Open the
workbook and run the vertical scrollbar all the way to the bottom. This is
were excel thinks the last row is. Select the row label for the lowest row
and select all the way up until you reach actual data. Now right click and
select delete. Nothing appears to happen but it will.

Next do the same with columns by dragging the horizontal scrollbar all the
way to the right and deleting the excess columns.

Now save the workbook. (The scrollbars should now only scroll the *actual*
size of the sheet). Try querying again

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[John Reyes]

Nick,
Thanks for the reply. Your solution makes a lot of sense &
I would have loved to have given it a shot. After I posted
my problem I continued working on it and I managed to get
it to work. I'm using XL 2K on W2K (Sorry for leaving that
out from the original posting); from the Tool Menu I
selected: Data > Get External Data > Data Range Properties

External Data Range Properties. Near the bottom of the

dialog box I had previously selected; "Insert entire rows
for new data, clear unused cells". I changed that
to "Overwrite existing cells with new data, clear unused
cells". It appears to me that this new selection is
nothing more than your solution being done by XL, so I'm
confident it would have worked. When I get a little more
time, I'll probably check off the original selection in
the dialog box & test out your solution. It's always good
to know different ways to solve a problem. Thanx again for
your time & solution.

Best regards,
John Reyes