THE BASICS PART 2
Now you have the basic idea of importing webpages let’s make it easier to change races or pages.
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://tatts.com/racing/2012/2/1/MR/7", Destination:=Range("$A$1"))
This is the part of the code that tell the macro what url you want and where to place it, as you know some parts of a url change to load different pages so what we need to do is link that part of the url with a cell on the sheet so when you put in a different race number for example it will change the macro without you going in to the macro itself.
As you can see there are ” at the start and finish of the url, these are important because when you add a cell location you will cut the url in half and these need to be added to any part of the url to make it work.
When we add anything to the url we use the & sign this tells us the combine “this part&the next part” to make one part, I will show you an example of adding a cell reference to change date, race venue and number.
Add a new sheet to excel; I will assume this is sheet4 for this example, right click and go to “view code” you can add “Sub Test4()” macro to an existing module.
I am going to use the old Qtab site as they have not added as much junk to it as the new one.
For proper formatting you should restrict VBA code to a reasonable length across the page, if code becomes too long and you want to go to the next line put this symbol in first_ then hit enter this tells the code to continue on the next line.
The coloured text corresponds to the code needed in the macro.
Here is the original link
www.tabonline.com.au/2012/02/02/NR01.html
The
red is the standard address that does not change.
The
green is the date the code formats the date the correct way for the site.
The
blue is the venue.
The
orange is the race number.
The
yellowgreen is the last part of the url.
On the new in cell A1 you put the date, in cell B1 you put the venue in cell C1 you put the race number in single digit, then add this code to D1
=IF(C1<10,0&C1,C1)
The code adds a zero to races below 10
Code:
Sub Test4()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;
http://www.tabonline.com.au" &
Format(Sheets("Sheet4").Range("A1").Value, "/yyyy/mm/dd/") & _
Sheets("sheet4").Range("B1").Value &
Sheets("sheet4").Range("D1").Value & "
.html", Destination:=Range("$A$3"))
.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
If you can get this to work see if you can get the NSW/Vic page to load in the same way.