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:=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.

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 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:=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.

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*

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:=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 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:=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.

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
'*********************************************

--
Tim Williams
Palo Alto, CA


"Lilivati" <[email protected]> wrote in message news:[email protected]...
> 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*
>
> 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:=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 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:=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.
>

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






Tim Williams wrote:
> 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
> '*********************************************
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Lilivati" <[email protected]> wrote in message news:[email protected]...
> > 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*
> >
> > 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:=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 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:=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.
> >