External Data more than 65000 rows. What now?
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: External Data more than 65000 rows. What now?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If your web query is run via code, you could have the data run over into multiple sheets.
    Tom

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sample data would be "Nickname", "First Name", "Last Name" :

    Darell, Peter, Willis

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom
    check your private messages
    Hans

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Still nobody with a solution for my problem???
    Hans

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com