# Import Data from website to excel using vba



## cpossamai (Dec 23, 2008)

I am trying to do a web query on http://www.tntexpress.com.au/interac...imes_tntau.asp with information entered into the text boxes and the result into excel.

I have tried using a macro (in the workbook vba of excel) but it failed and I'm not sure if I am supposed to use post or some other way for text boxes :

Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = "http://www.tntexpress.com.au/interac...esX_tntau.asp?" 'DONT FORGET TO COPY LINK LOCATION BECAUSE TECH SUPPORT GUY SHORTENS THE ADDRESS

Const PostOSuburb As String = "txtOSuburb=CRANBOURNE" 
Const PostOState As String = "&txtOState=VIC" 
Const PostOCode As String = "&txtOPcode=3977" 
Const PostDSuburb As String = "&txtDSuburb=ASPENDALE"
Const PostDState As String = "&txtDState=VIC" 
Const PostDCode As String = "&txtDPcode=3195" 
Const Postcolmonth As String = "&colmonth=December" 
Const Postcolyear As String = "&colyear=2008"
Const Postcolhour As String = "&colhour=15" 
Const Postcolmin As String = "&colmin=00"

MyPost = PostOSuburb & PostOState & PostOPcode & PostDSuburb & PostDState & PostDPcode & Postcolmonth & Postcolyear & Postcolhour & Postcolmin

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & MyUrl, Destination:=Cells(1, 1))
.PostText = MyPost
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

End Sub

The error produced is 'run time error 1004'
Unable to open http://www.tntexpress.com.au/interac...esX_tntau.asp?

Cannot download the information you requested.

and stops at .Refresh BackgroundQuery:=False

If I take out .PostText = MyPost

it will work but it wont give me the required information I need, it will be as if I didn't fill in any of the text boxes.

Please clarify with me the details if you think you can help me, but you don't quite understand what I am trying to do.

click on this link http://www.tntexpress.com.au/interac...esX_tntau.asp? to see what I am talking about, enter in the inormation manually and the next page is the information I want displayed in Excel.

Thanks - hope someone can help


----------



## JohnWill (Oct 19, 2002)

Please don't post duplicate threads. Continue the problem resolution here: http://forums.techguy.org/business-applications/782450-excel-web-query.html


----------

