Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: web query

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

    Default

    Hi again,
    I am trying to retrieve data from an external website using following macro:

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 4/21/2002 by Hans
    '

    '
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://1on1.xxxxxxx.com/admin/girltimes.cgi?girl=Oshin&date=04/20/02", _
    Destination:=Range("A1"))
    .Name = "02_6"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery:=False
    End With
    End Sub


    I have to extract data for about 1200 girls names (Oshin) each for the last 365 days.
    How do I write the code if I want to start with todays date and on the next loop with the same girl but yesterdays date and so on.
    For the next girl I would just modify the macro manually.
    Hans

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

    Default

    TsTom,
    I've seen you are online, you helped me before. Can you look at this one?
    Hans

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

    Hi Bukol.
    You want a simple loop back one year by each day of the previous year?
    Tom

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

    Default

    Tom,
    yes, but the date has to be part of the URL line, so I would have to put a placeholder in there and then create a variable with todays date and every loop would start with didacting one day from that variable.
    Right?
    How?
    Hans

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

    Sub QueryPastYear()
    Dim FirstDate As Date
    Dim CurrentDate As Date

    CurrentDate = Now ' or any date that you choose ex. #04/10/2002#
    FirstDate = CurrentDate - 365

    Do Until CurrentDate = FirstDate
    CurrentDate = CurrentDate - 1
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://1on1.xxxxxxx.com/admin/girltimes.cgi?girl=Oshin&date=" & _
    Format(CurrentDate, "MM/DD/YY"), _
    Destination:=Range("A1"))
    .Name = "02_6"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery:=False
    End With
    Loop
    End Sub

  6. #6
    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,
    I did not test it...
    Should work as is...
    Just wanted to add.
    You should be able to query the database of this site with a range of dates and get one return instead of having to do 365 seperate connections...
    What type of database is this?
    You may be able to send an SQL statement...
    May solve your other problem as well.
    Were you ever able to figure out a way to get your list of E-Mails?
    Tom

    [ This Message was edited by: TsTom on 2002-04-21 05:13 ]

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

    Default

    Tom,
    you are a genius!
    Last question: if I only want to retrieve lets say one month, I only have to change:
    FirstDate = CurrentDate - 31 (instad of 365)
    Correct?
    Hans

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

    Correct...
    If you will be incuding todays date in the query then make
    CurrentDate = yesterdays date
    Tom

    [ This Message was edited by: TsTom on 2002-04-21 09:24 ]

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

    Default

    Tom,
    1) I have very limited access to that external database. But I am happy with your code now.

    2) Yes, I did finally get almost all of my 134000 names which I needed to get their e-mail addresses, but I had to do it the hard way:
    Remember I only got 65000+ rows out of 134000 lines of data because of the limitaion in excel.
    When I entered "@" as search query, I could be sure I would get all of the customers.
    So I searched instead for the 5 vowels a e i o u seperately. That way I got 2450000 names in 5 batches of between 55000 and 30000.
    I sorted them aplphabetically and deleted duplicates and I was left with 129000 names. (some guys must have funny names with no vowels in it.)
    Hans

Some videos you may like

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
  •