web query

[Bukol]

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

 

[Bukol]

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

 

[Legacy 98055]

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

 

[Bukol]

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

 

[Legacy 98055]

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

 

[Legacy 98055]

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

 

[Bukol]

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

 

[Legacy 98055]

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

 

[Bukol]

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