more excel help

[DR RON]

I can download the results from tabonline one race at a time into excel. By using the import data function. Can some one tell me how I can automate this process to save me some time thanks. All replies appreciated.

[wesmip1]

Dr Ron,

The info is copyright .... So there could be an issue with storing the data in excel.

Not sure if it can be done in excel, but I have done a program in java that jut reads the results but does not store the results anywhere.

Thanks

[KennyVictor]

I asked a barrister to give an opinion on the copyright of racing results and he was (unoficially) of the opinion that they are in the public arena. I can't see that tab payouts would be any different.

KV

[DR RON]

Wesmip 1, as far as copyright is concerned I dont have a problem downloading any info that is freely available for the world to see, as long as I dont reproduce it to make any personal gain. It is purely for my own use. Anyway thanks for the reply, I just thought that there might be a way I can automate it so I dont have to keep typing in virtualy the same info with a couple of different characters, being the race code and number. For example the qquery address is
[url]www.tabonline.com.au/2005/10/15/SR01.html for sydney race 1 reults today, I just need to be able to have the race number change automatically. Thanks for your opinion as wll KV, I think your barrister is correct. I couldnt see any one being charged over something like I am trying to do.

[Chrome Prince]

Dr RON on sheet1 copy a list of the urls as such in cellA1,A2,A3 etc. under eachother......
http://www.tabonline.com.au/2002/10/12/MR01.html
http://www.tabonline.com.au/2002/10/13/MR02.html
http://www.tabonline.com.au/2002/10/13/MR03.html
http://www.tabonline.com.au/2002/10/13/MR04.html

etc.

You'll need to do this part manually unfortunately.

Now record a macro, any dummy macro and save it.

Open up and step into the newly created macro, clear all entries until you have a blank macro page and copy and paste the code below....

Sub ImportData()

Sheets(2).Activate
A = 1
Do Until Sheets(1).Cells(A, 1) = ""
myquery = Sheets(1).Cells(A, 1)
Sheets(2).Cells(1, 1) = myquery
myrow = Sheets(2).UsedRange.Rows.Count + 1
Do
myrow = myrow - 1
Loop Until Sheets(2).Cells(myrow, 1) <> ""
myrow = myrow + 1
With Sheets(2).QueryTables.Add(Connection:= _
"URL;" & myquery, Destination:=Sheets(2).Cells(myrow, 1))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
A = A + 1
Loop

End Sub

Save it as ImportData

Run the macro, it should paste the webpages one under the other on sheet2 of your workbook.

As long as you use it for yourself only, I don't see the problem. Unitab are hardly going to break into your house with a torch at 4am and go "Gotcha!".

They keep this information on their website for 2 years, so they keep it there for people to use.

[DR RON]

Thanks Chrome, I'll give it a go and see if how it goes, it will test the boundries of my computer skills.

[DR RON]

Chrome, box came up with a runtime error 1004 , address not valid, even though it is.?

[Chrome Prince]

DR make sure you've entered the date correctly, in that the page exists and hasn't been taken off, they only keep pages active for 3 years I think and delete them by the day.

[Chrome Prince]

Also make sure you have the tab address exactly as it's supposed to be and that the address is totally in Column A and not partly in column b of sheet1

[DR RON]

Chrome, I used saturdays date so shouldn't be a problem, address is correct because after I type it in if I click on it it goes straight to that page. Also the whole address is in column A. Any other suggestions, I copied and pasted the code straight from your post so that wouldn't be the problem either. I still get the same error message.Is their something that I should have on my computer to enable me to do this that I might not have perhaps? Thanks Chrome.

[Chrome Prince]

DR,

It should work fine, I've tested it extensively, you might have not configured the macro correctly.

Here's the workbook attached.

Right click the link and select save target as and download it to your desktop before opening.

Make sure that your security settings for Excel are set to low, or it may not run.

Open it
Select Tools
Macro
Macros
Run

Viola!

;)

The major drawback is that you have to manually enter the web addresses, which I've yet to find a solution to, and I'd prefer to only download select information and manipulate the entries and formatting, but I'll stick with it for now.

[DR RON]

Thanks Chrome, you're a genius. Now to some research.