using excel to query asp page
 

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:=ConnectUR L,
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.

using excel to query asp page
 

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:=ConnectUR L,
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 wrote:
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:=ConnectUR L,
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.

using excel to query asp page
 

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=su bmit&Label2&cmdActiveConcessions&cmdUnitCost&cmdPr oductCodes"
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*

Lilivati wrote:
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:=ConnectUR L,
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 wrote:
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:=ConnectUR L,
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.