PDA

View Full Version : Using VB with variables to access data from web sites



kking44
04-04-2008, 10:58 AM
I used your program to pull information from web sites into excel. It works great. I have one question(problem). I have a spread sheet with multiple web locations. I assigned a variable and constructed a loop so that the vb program would look at each web site, gather the info, and post it to the spread sheet. However, the program will not use the variable to identify to web location. I know the variable is obtaining the correct info because I am posting the cell value to another cell just to check. Each time I get a debug error stating that the web site is not vaild.

Any ideas on how to correct this?

lucas
04-04-2008, 11:04 AM
Hi Ken,
could you post the code you are having trouble with. after you paste it in to the window...select it and hit the vba button and it will be formatted automatically.

kking44
04-04-2008, 11:11 AM
Hi Ken,
could you post the code you are having trouble with. after you paste it in to the window...select it and hit the vba button and it will be formatted automatically.


Option Explicit
Sub urltest()
Dim objWeb As QueryTable
Dim sWebTable As String

Dim x As String
Dim y As Integer
Dim z As Integer
Dim aa As String


Range("c2").Select
aa = ActiveCell.Value
'the data in cell c2 is the url or link to the web site

'You have to count down the tables on the URL listed in your query
'This example shows how to retrieve the 2nd table from the web page.
sWebTable = 6
'Sets the url to run the query and the destination in the excel file
'You can change both to suit your needs
Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;aa", _
Destination:=Range("e2"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = sWebTable
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Set objWeb = Nothing

End Sub


select the code and then hit the vba button ken........

lucas
04-04-2008, 03:51 PM
Try this small change Ken:
Option Explicit
Sub urltest()
Dim objWeb As QueryTable
Dim sWebTable As String

Dim x As String
Dim y As Integer
Dim z As Integer
Dim aa As String


Range("c2").Select
aa = "URL;" & ActiveCell.Value

'the data in cell c2 is the url or link to the web site

'You have to count down the tables on the URL listed in your query
'This example shows how to retrieve the 2nd table from the web page.
sWebTable = 2
'Sets the url to run the query and the destination in the excel file
'You can change both to suit your needs
Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:=aa, _
Destination:=Range("e2"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = sWebTable
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Set objWeb = Nothing

End Sub

kking44
04-04-2008, 05:32 PM
This works! Now why didn't I think of that?

Thanks

lucas
04-04-2008, 05:38 PM
No problem Ken. Be sure to mark your thread solved using the thread tools at the top of the page.