Hi Everyone. I am new to VBA and would like to receive some help regarding this issue.
I am planning to have the actual price of fuel from a website. I am using web query to access the site and retrieve a particular table. In this table table only two rows are relevant ( time and price). rest don't need. how to delete the other rows and have the required rows transpose.
Am planning to have other websites in column A, time in column B and price in Column C. the data should be refreshed periodically.
Also planning to have other website in column A (one under the other) and have same results tabulated.
How can this be done using VBA. Thanks for your valuable support and expertise.
the code is as follows:
Public Sub ImportWeb()
Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;http://www.petro-canada.ca/en/locations/4085.aspx?MODE=DTS&ID=59041", _
Destination:=Range("B1"))
With qt
.Name = "Regular, Posted, Self serve"
.WebSelectionType = xlSpecifiedTables
.WebTables = "20" ' Regular table
.WebFormatting = xlWebFormattingNone
.EnableRefresh = True
.RefreshPeriod = 60 'Unit in minutes
.Refresh 'Execute query
End With
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
> </o>
Set qt = Nothing
End Sub
I am planning to have the actual price of fuel from a website. I am using web query to access the site and retrieve a particular table. In this table table only two rows are relevant ( time and price). rest don't need. how to delete the other rows and have the required rows transpose.
Am planning to have other websites in column A, time in column B and price in Column C. the data should be refreshed periodically.
Also planning to have other website in column A (one under the other) and have same results tabulated.
How can this be done using VBA. Thanks for your valuable support and expertise.
the code is as follows:
Public Sub ImportWeb()
Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;http://www.petro-canada.ca/en/locations/4085.aspx?MODE=DTS&ID=59041", _
Destination:=Range("B1"))
With qt
.Name = "Regular, Posted, Self serve"
.WebSelectionType = xlSpecifiedTables
.WebTables = "20" ' Regular table
.WebFormatting = xlWebFormattingNone
.EnableRefresh = True
.RefreshPeriod = 60 'Unit in minutes
.Refresh 'Execute query
End With
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o> </o>Set qt = Nothing
End Sub
