Web query problem

babycody · Aug 22, 2004

I have a web query set up to import several pages into one column. The problem I am having is that sometimes the amount of information imported changes in length. When that happens one query can write over another query. This leaves several rows of information cut off of the query above it. Is there a property setting I should use to solve this, or some other solution?

Example:
Today I used A3 for a query. The results took up 8 rows. I started a new query in A12. The next day I refreshed. However the query for A3 took up 10 rows this time. That would make the results end at cell A13. Since the second query starts on A12 they wind up overlapping. This results in the last line of the first query being cut off.

Thanks for any help on this guys!

parry · Aug 22, 2004

Hi Babycody, according to the website you have had 644 views of your post but no answer. I can assure you thats not normally the case

Anyway, I believe its the RefreshStyle property you want to amend.
If you right click the query and select Data Range properties then you will see at the bottom there is options for insert cells or entire rows etc. I believe this is the RefreshStyle property.

Edit: its RefreshStyle property.

babycody · Aug 22, 2004

Thanks for the reply Parry. I was surprised that over 600 people had viewed this post. I did adjust the properties in every way possible. This doesn't seem to be the solution. I still have information cut off at the point the second query begins. Excel doesn't want to push the rows down to make room for the first query when it expands. Maybe the makers of Excel thought that the query table would always be static. The tables from this website shrink and expand.

parry · Aug 22, 2004

babycody said:
I was surprised that over 600 people had viewed this post.

It's just a temporary problem with the board I think. Your over 1000 now.

On your problem I did a test with a new web query with data underneath and it actually moved the data to the right rather than inserting rows above (even though I selected the "insert entire rows for new data" option). Im only on XL97 at work so maybe they fixed that in later versions?

I would need to consult a book to see if theres a way around this. I'll post back later on if I find an answer.

babycody · Aug 22, 2004

Maybe this will qualify for the challenge of the month! Not likely, but nobody seems to have the answer. After all of the wonderful answers I have gotten from this site if one problem doesn't get solved I will live. Maybe over a thousand views without an answer is a hint that it isn't possible. Maybe I will give each query its own column and then merge the columns. Seem to remember some post on this. MS Excel didn't have a solution either in there FAQ.

parry · Aug 22, 2004

Dont despair, if theres nothing within the query object model then a work-around may be able to implemented. You could for example, insert x number of rows prior to the query being refreshed then delete excess rows afterwards etc. However, there should be something within the query object to handle this properly but I havent got time to play around with this at the moment.

babycody · Aug 23, 2004

Thanks Parry. Haven't given up hope just yet.

parry · Aug 23, 2004

Hi, no luck getting this to work Im afraid. From reading the Help the constant xlInsertEntireRows in the RefreshStyle property should insert entire rows thus moving data underneath downwards. However, it seems to insert partial rows moving other data to the right. Whether this is just a bug in '97 I dont know.

Anyway, heres an example of a work around to insert temporary rows. Ive used a figure of 100 rows but you may wish to increase this as desired.

Code:

Sub Example()
Dim LastRow As Long, NumInsertRows As Long, LastRow2 As Long
Dim qt As QueryTable, Rng As Range

'set an object variable for the query table located in Sheet3
Sheets("Sheet3").Activate
Set qt = Sheets("Sheet3").QueryTables("My Query")

'Find the range of cells occupied by the query
Set Rng = qt.ResultRange

'Find last used row used by the query
LastRow = Rng.Rows.Count + (qt.Destination.Row - 1)

'Turn off screen updating
Application.ScreenUpdating = False

'Define number of temporary rows to insert
NumInsertRows = 100

'Insert temporary rows
Range(Cells(LastRow + 1, 1), Cells(LastRow + 1 + NumInsertRows, 1)).EntireRow.Insert

'Refresh query
qt.Refresh BackgroundQuery:=False

'Find new last row
Set Rng = qt.ResultRange
LastRow2 = Rng.Rows.Count + (qt.Destination.Row - 1)

'Delete unused temporary rows
Range(Cells(LastRow2 + 1, 1), Cells(LastRow + NumInsertRows + 1, 1)).EntireRow.Delete

'Turn back on screen updating
Application.ScreenUpdating = True
End Sub

babycody · Aug 23, 2004

Thanks Parry! Wish I could find a VBA class in my area. This is great just what I needed.

parry · Aug 23, 2004

My pleasure, if you have any probs post again. The lines you will need to change to match your workbook are these. The rest you can leave as is and it should work OK.

Code:

Sheets("Sheet3").Activate 'change to indicate your worksheet name
Set qt = Sheets("Sheet3").QueryTables("My Query") 'change to indivate worksheet name and name of the querytable.
NumInsertRows = 100 'Change to indicate the number you want

Web query problem

babycody

Well-known Member

Excel Facts

parry

MrExcel MVP

babycody

Well-known Member

parry

MrExcel MVP

babycody

Well-known Member

parry

MrExcel MVP

babycody

Well-known Member

parry

MrExcel MVP

babycody

Well-known Member

parry

MrExcel MVP

Similar threads

Forum statistics

Share this page

Web query problem

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock