using excel to query asp page

[Lilivati]

I need to query an asp page from excelusing VBA. I have a couple of
roadblocks though. I can't post the asp page here because it is a
company intranet page. All I want it to do is copy a part number from
an excel cell into a text field on the form (the text field name is
txtPN), and then "click" a button called Button1. There are four
buttons on the page so I really need to be able to specify which one.
Then a label field (lblPartName) displays the part name found as a
result of the query, and this is the field I need to retrieve. I don't
really have any code to post because I've never tried to do a web query
with Excel and I don't even know where to begin.

This is the sad little bit of code I have. It doesn't do anything when
I run it.

Sub PartNameFetch()

Dim ws As Worksheet
Dim ConnectURL As String
Dim PostStr As String
Set ws = Sheets("Sheet1")
ConnectURL =
"URL;http://infocentreapp/EngrgDataLookup/PartNumberLookup.aspx"

'eventually this will have to be dynamic, but right now I have a
specific part just to test
PostStr = "txtPN=23069000"
On Error Resume Next
With ScratchSheet.QueryTables.Add(Connection:=ConnectURL,
Destination:=Range("B1"))
..PostText = PostStr
..BackgroundQuery = True
..SaveData = True
End With
End Sub


I know I haven't given much to go on, but any and all help would be
appreciated at this point.

 

[Lilivati]

Alright here is what I have now:

Sub PostTest()
Dim ScratchSheet As Worksheet
Dim ConnectURL As String
Dim PostStr As String


Set ScratchSheet = Sheets("Sheet1")


ConnectURL =
"URL;http://infocentreapp/EngrgDataLookup/PartNumberLookup.aspx"
PostStr =
"txtPN=23069000&lblPartName&Button1=submit&Label2&cmdActiveConcessions&cmdUnitCost&cmdProductCodes"
MsgBox PostStr
On Error Resume Next
With ScratchSheet.QueryTables.Add(Connection:=ConnectURL,
Destination:=Range("A1:T20"))
.PostText = PostStr
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub


It copies something to the excel file, namely the Label2 text (Enter
Part Number) but NOTHING ELSE from the page. I can't even tell if it
is actually querying the page or if it is just copying this field for
some obscure reason.

 

[Lilivati]

So I tried this:

Sub PostTest()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

Dim PostStr As String
Dim sURL As String
Dim sHeader As String

sURL = "http://infocentreapp/EngrgDataLookup/PartNumberLookup.aspx"
'ie.Document.all("txtPN").Innertext = "23069000"


'ie.Document.all("Button1").submit
'MsgBox ie.Document.all("lblPartName").Value
' PostStr =
"__VIEWSTATE&txtPN=23069000&lblPartName&Button1=submit&Label2&cmdActiveConcessions&cmdUnitCost&cmdProductCodes"
PostStr = "txtPN=23069000"

sHeader = "Content-Type: " & _
"application/x-www-form-urlencoded" & vbCrLf
ie.Navigate sURL, 0, "_self", PostStr, sHeader
ie.Visible = True
End Sub

And while it opens the web page all nice and stuff, it posts NOTHING in
the page. So I have two questions:

1. How do I get it to actually post the input value I need in the text
box on the page?
2. Where is the click? How do I tell it with VBA "click this button"?

Someone please help, I have been trying to figure this out for the last
eight hours and I am ready to take a bat to this stupid computer.
*grumble*

 

[Tim Williams]

Something like this might work.
Depends on whether there are >1 form on your page, and how your buttons/fields are named.

Tim

'********************************************
Sub PostTest()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

Dim PostStr As String
Dim sURL As String
Dim sHeader As String

ie.Visible = True
ie.Navigate "http://infocentreapp/EngrgDataLookup/PartNumberLookup.aspx"
Do While ie.busy And Not ie.readystate = 4
DoEvents
Loop

ie.Document.all("txtPN").value = "23069000"
ie.Document.Forms(1).submit
Do While ie.busy And Not ie.readystate = 4
DoEvents
Loop

msgbox ie.Document.all("lblPartName").innerHTML

ie.quit
set ie = Nothing
end sub
'*********************************************

 

[Lilivati]

Tim,

Thank you so much!! The code needed a few tweaks to work for me, but
it is now working beautifully. (And as an added bonus I won't have to
explain to my boss how my computer ended up falling out the window.
)

Here is the final version in case it should be of use to somebody:

Sub PostTest()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")


Dim PostStr As String
Dim sURL As String
Dim sHeader As String


ie.Visible = True
ie.Navigate
"http://infocentreapp/EngrgDataLookup/PartNumberLookup.aspx"
Do While ie.Busy And Not ie.ReadyState = 4
DoEvents
Loop

ie.Document.all("txtPN").Value = "23069000"
'ie.Document.Forms(1).submit
ie.Document.all.Item("Button1").Click
Application.Wait Now + TimeValue("00:00:01")

MsgBox ie.Document.all.Item("lblPartName").innertext

ie.Quit
Set ie = Nothing
End Sub