External Data more than 65000 rows. What now?

Bukol

Board Regular
Joined
Mar 31, 2002
Messages
55
I am using a web query to extract external data from a website. The lines of data is more than 130000. Excel gives me only 65000+ rows and thats all whats transfered. I dont have a choice of extracting only part of the table from the website. What to do to get all the data? MS Word would have enough lines but no web query !?
Any ideas?
Hans
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
If your web query is run via code, you could have the data run over into multiple sheets.
Tom
 
Upvote 0
Tom
How??
Actually I dont use a code, but just the menu item "get external data" + "new web query" and there is no way to modify.
Hans
 
Upvote 0
Bukol.
Record your web query as a macro.
Edit or post the code here.
You or someone here will enable the query to
spill over onto another sheet. I hope we can anyway. Don't see why not!
Try it!
Tom

P.S.
Just in case...
Post a bit of the sample data as well...
 
Upvote 0
Here is the Macro code. This would give you only 3 rows, the one I am trying to extract is searching the e-mail field for "@"
Hans

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/8/2002 by Hans
'

'
With Selection.QueryTable
.Connection = _
"URL;http://1on1.adultclub.com/admin/search.cgi?query=digitelone&field=email"
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub
 
Upvote 0
Sample data would be "Nickname", "First Name", "Last Name" :

Darell, Peter, Willis
 
Upvote 0
Bukol
The website required a password so I was unable to get a grip on how to fix your problem exactly.
My suggestion would be to place you query into one cell.
You will need VBA to create a seperate list on another sheet with a counter.
This cell would transfer it's value to another list each time it is updated by the query. I know of no other way.
If you can manage to get your query to place it's data into one or several of the same cells, repost and we can go from there.
Tom
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top