ExcelBanter - Web Farming

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Web Farming (https://www.excelbanter.com/excel-programming/296439-web-farming.html)

OK, I know how to search a web page and read information from it into an Excel worksheet. What I need to know id how to place informatino from a worksheet into a web page. Here's what I have. We use a Web site to track orders, There is a memo field that I need to place a comment in then click the update button. I would really like to do this via a macro or VBscript.

Hi Mike,

This is possible using the XMLHTTP object. Take a look at some of these
threads to see if they help at all.

http://groups.google.com/groups?q=%2...UTF-8&filter=0

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Mike wrote:
OK, I know how to search a web page and read information from it into
an Excel worksheet. What I need to know id how to place informatino
from a worksheet into a web page. Here's what I have. We use a Web
site to track orders, There is a memo field that I need to place a
comment in then click the update button. I would really like to do
this via a macro or VBscript.

Hi Mike,

Mike wrote:
Open "POST", would be the web page I want to send data to.

Yes. But it may not be the page you thought. The page you visit to *enter*
the data is not the URL you want in some cases. You want the page the data
is actually *submitted to*. You can go to the data entry page and view
source to get this - just look for the action attribute of the <form
element. That's the URL you want to POST your data to.

abytPostData would be the data to send, but I don't understand the
format.

abytPostData is in name/value pair format. When viewing the source of the
data entry page, you will see several <input and other form elements. So
if you have a page the asks for a first name, you may see <input type="text"
name="txtFName" maxlength="50" / or similar. The name (or ID in some
cases) of the form element is what you use for the name part of the
name/value pair. So if you want to submit a first name of "Jake", you would
do this:

abytPostData = StrConv("txtFName=Jake", vbFromUnicode)

and how do I update the page, if I use manual entry there is a submit
button.

Go through each field that you need to enter and find the name of that form
element in the HTML source. String those name/value pairs together,
separated by ampersands, and put that string into abytPostData. When you
invoke the Open method specifying POST and the target URL, then invoke the
Send method, you are effectively "clicking" the submit button to POST the
data.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Something still isn't right. Here's a copy of the source code for the web page I'm trying to up date

<TR<TD bgcolor=yellowComment</TD<TD<input type=text name=comments value="" size=41 maxlength=40</TD</TR</TABLE<BR<input type=submit name=sub value="Update"</form</center</body</html

The part I'm trying to update is [ <input type=text name=comments value=""

Here is my code

Private Sub Web_Update_Click(
Dim xml As XMLHTTP4
Dim abytPostData() As Byt

abytPostData = StrConv("comments value=Test", vbFromUnicode

Set xml = New XMLHTTP4
With xm
.Open "POST", "http://iecmech1.sbc.com/proj-bin/c2fmen?report=UPDATE&cktid=28/HCGS/713101%20%20%20%20/SW%20%20/&tbl=c2f
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded
.Send abytPostDat
End Wit

Set xml = Nothin

End Su

CAn you see the problem.

Mike

Hi Mike,

The name of the textbox is "comments", not "comments value". So this:

abytPostData = StrConv("comments value=Test", vbFromUnicode)

should be:

abytPostData = StrConv("comments=Test", vbFromUnicode)

I'm not sure what the cktid querystring parameter signifies, but it may
cause problems if it changes each time you visit the page.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Mike wrote:
Something still isn't right. Here's a copy of the source code for
the web page I'm trying to up date,

<TR<TD bgcolor=yellowComment</TD<TD<input type=text name=comments
value="" size=41 maxlength=40</TD</TR</TABLE<BR<input
type=submit name=sub value="Update"</form</center</body</html

The part I'm trying to update is [ <input type=text name=comments
value="" ]

Here is my code.

Private Sub Web_Update_Click()
Dim xml As XMLHTTP40
Dim abytPostData() As Byte

abytPostData = StrConv("comments value=Test", vbFromUnicode)

Set xml = New XMLHTTP40
With xml
.Open "POST",

"http://iecmech1.sbc.com/proj-bin/c2fmen?report=UPDATE&cktid=28/HCGS/713101%
20%20%20%20/SW%20%20/&tbl=c2f"
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send abytPostData End With

Set xml = Nothing

End Sub

CAn you see the problem..

Mike

Thanks for what you've given me so far. Let me start off with my skill levels here, I don't write web pages (have a little understanding of the structure) and I'm a VB programmer only in a very loose interpretation

I think you my have pointed out the problem in a previous response, that being the web address. Yes it does change depending on what I'm looking for but I know how to write code to get to the right page there. But I may be trying to update the wrong screen. I've put the web address I'm using and a portion of the source code at the end of this message

One last thing, if you were looking for a book to learn this out of what would you choose

http://iecmech1.sbc.com/proj-bin/c2f...%20%20/&tbl=c2

<FONT COLOR=RED SIZE=3<BData as of 2004-04-30 10:10:25</FONT<BR<BR<a href="/SSPROJ/"Main Page</a</form<HR<FONT color=red size=5<BUpdate Circuit 28/HCGS/713101 /SW /</B</FONT<form method=POST action="/proj-bin/c2fmen"<input type=hidden name=report value=DOUPDATE<input type=hidden name=tbl value=c2f<input type=hidden name=cktid value="28/HCGS/713101 /SW /"<TABLE BORDER<TR<TD bgcolor=yellowCustomer Name</TD<TD<input type=text name=cust_name value="HUNTER-AND-SAGE" size=64 maxlength=63</TD</TR<TR<TD bgcolor=yellowAddress</TD<TD<input type=text name=addr value="6161 SAVOY" size=64 maxlength=63</TD</TR<TR<TD bgcolor=yellowMarket Area</TD<TD<select name=ma<option value=ARKANSASARKANSAS</option<option value=AUSTINAUSTIN</option<option value=DALLASDALLAS</option<option value=HOUSTON SELECTEDHOUSTON</option<option value=KANSASKANSAS</option<option value=MISSOURIMISSOURI</option<option value=OKLAHOMAOKLAHOMA</option<option value="SAN ANTONIO"SAN ANTONIO</option</select</TD</TR<TR<TD bgcolor=yellowACNA</TD<TD<input type=text name=acna value="WTL" size=9 maxlength=8</TD</TR<TR<TD bgcolor=yellowCAC</TD<TD<input type=text name=cac value="SSN6CK2" size=8 maxlength=7</TD</TR<TR<TD bgcolor=yellowLSO</TD<TD<input type=text name=lso value="HSTNTXSU" size=12 maxlength=11</TD</TR<TR<TD bgcolor=yellowMCN</TD<TD<input type=text name=mcn value="" size=16 maxlength=15</TD</TR<TR<TD bgcolor=yellowMCO</TD<TD<input type=text name=mco value="HSTNTXKLSSC" size=12 maxlength=11</TD</TR<TR<TD bgcolor=yellowCLO</TD<TD<input type=text name=clo value="HOS935173001" size=13 maxlength=12</TD</TR<TR<TD bgcolor=yellowDue Date<BR<FONT color=red size=2YYYY-MM-DD</FONT</TD<TD<input type=text name=dd value="2004-02-28" size=11 maxlength=10</TD</TR<TR<TD bgcolor=yellowCompletion Date<BR<FONT color=red size=2YYYY-MM-DD</font</TD<TD<input type=text name=cmpdte value="0000-00-00" size=11 maxlength=10</TD</TR<TR<TD bgcolor=yellowFiber-Scid Ready</TD<TD<select name=scid<option value="Y" selectedY</option<option value="N"N</option</select</TD</TR<TR<TD bgcolor=yellowPre-Service Dispatch<BR</TD<TD<select name=presvc<option value="Y" selectedY</option<option value="N"N</option<option value="SSDAC"SSDAC</option</select</TD</TR<TR<TD bgcolor=yellowCustomer Release Date<BR<FONT color=red size=2YYYY-MM-DD</font</TD<TD<input type=text name=cusrel value="2004-12-24" size=11 maxlength=10</TD</TR<TR<TD bgcolor=yellowComment</TD<TD<input type=text name=comments value="T" size=41 maxlength=40</TD</TR</TABLE<BR<input type=submit name=sub value="Update"</form</center</body</html

Hi Mike,

It looks like this particular form submits to itself (same URL in action
attribute of form element), so I think you're submitting to the correct
page. Many form submissions will fail if you don't pass *all* the input
fields (whether they be hidden, buttons, inputs, or other form elements), so
make sure you get them all. Just from the code below, the name/value pairs
would be:

Report=DOUPDATE&tbl=c2f&cktid=28/HCGS/713101 /SW /

and so on. I can't get to the URL you posted, so I don't think I'll be able
to help out any more on this. I don't know of any books that cover this
type of material. I would suggest searching Google or Google Groups to see
what's out there.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Mike wrote:
Thanks for what you've given me so far. Let me start off with my
skill levels here, I don't write web pages (have a little
understanding of the structure) and I'm a VB programmer only in a
very loose interpretation.

I think you my have pointed out the problem in a previous response,
that being the web address. Yes it does change depending on what I'm
looking for but I know how to write code to get to the right page
there. But I may be trying to update the wrong screen. I've put the
web address I'm using and a portion of the source code at the end of
this message.

One last thing, if you were looking for a book to learn this out of
what would you choose?

http://iecmech1.sbc.com/proj-bin/c2f...20%20/&tbl=c2f

<FONT COLOR=RED SIZE=3<BData as of 2004-04-30
10:10:25</FONT<BR<BR<a href="/SSPROJ/"Main
Page</a</form<HR<FONT color=red size=5<BUpdate Circuit
28/HCGS/713101 /SW /</B</FONT<form method=POST
action="/proj-bin/c2fmen"<input type=hidden name=report
value=DOUPDATE<input type=hidden name=tbl value=c2f<input
type=hidden name=cktid value="28/HCGS/713101 /SW /"<TABLE
BORDER<TR<TD bgcolor=yellowCustomer Name</TD<TD<input type=text
name=cust_name value="HUNTER-AND-SAGE" size=64
maxlength=63</TD</TR<TR<TD bgcolor=yellowAddress</TD<TD<input
type=text name=addr value="6161 SAVOY" size=64
maxlength=63</TD</TR<TR<TD bgcolor=yellowMarket
Area</TD<TD<select name=ma<option
value=ARKANSASARKANSAS</option<option
value=AUSTINAUSTIN</option<option
value=DALLASDALLAS</option<option value=HOUSTON
SELECTEDHOUSTON</option<option value=KANSASKANSAS</option<option
value=MISSOURIMISSOURI</option<option
value=OKLAHOMAOKLAHOMA</option<option value="SAN ANTONIO"SAN
ANTONIO</option</select</TD</TR<TR<TD
bgcolor=yellowACNA</TD<TD<input type=text name=acna value="WTL"
size=9 maxlength=8</TD</TR<TR<TD
bgcolor=yellowCAC</TD<TD<input type=text name=cac value="SSN6CK2"
size=8 maxlength=7</TD</TR<TR<TD
bgcolor=yellowLSO</TD<TD<input type=text name=lso value="HSTNTXSU"
size=12 maxlength=11</TD</TR<TR<TD
bgcolor=yellowMCN</TD<TD<input type=text name=mcn value="" size=16
maxlength=15</TD</TR<TR<TD bgcolor=yellowMCO</TD<TD<input
type=text name=mco value="HSTNTXKLSSC" size=12
maxlength=11</TD</TR<TR<TD bgcolor=yellowCLO</TD<TD<input
type=text name=clo value="HOS935173001" size=13
maxlength=12</TD</TR<TR<TD bgcolor=yellowDue Date<BR<FONT
color=red size=2YYYY-MM-DD</FONT</TD<TD<input type=text name=dd
value="2004-02-28" size=11 maxlength=10</TD</TR<TR<TD
bgcolor=yellowCompletion Date<BR<FONT color=red
size=2YYYY-MM-DD</font</TD<TD<input type=text name=cmpdte
value="0000-00-00" size=11 maxlength=10</TD</TR<TR<TD
bgcolor=yellowFiber-Scid Ready</TD<TD<select name=scid<option
value="Y" selectedY</option<option
value="N"N</option</select</TD</TR<TR<TD
bgcolor=yellowPre-Service Dispatch<BR</TD<TD<select
name=presvc<option value="Y" selectedY</option<option
value="N"N</option<option
value="SSDAC"SSDAC</option</select</TD</TR<TR<TD
bgcolor=yellowCustomer Release Date<BR<FONT color=red
size=2YYYY-MM-DD</font</TD<TD<input type=text name=cusrel
value="2004-12-24" size=11 maxlength=10</TD</TR<TR<TD
bgcolor=yellowComment</TD<TD<input type=text name=comments
value="T" size=41 maxlength=40</TD</TR</TABLE<BR<input
type=submit name=sub value="Update"</form</center</body</html

If you are ever in Dallas the coffee is on me

One last question.

The web page that displays the form to update is
http://iecmech1.sbc.com/proj-bin/c2f...%20%20/&tbl=c2

Yet in the very frist of the HTML code is the following
<form method=POST action="/proj-bin/c2fmen"

Is it possible that I need to open the first URL and then post the second

With xm
.Open "http://iecmech1.sbc.com/proj-bin/c2fmen?report=UPDATE&cktid=28/HCGS/713101%20%20%20%20/SW%20%20/&tbl=c2f
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded
.Send abytPostDat
.Open "POST", "/proj-bin/c2
End Wit

Or something along this line

Mike

Mike,

Mike wrote:
The web page that displays the form to update is;

http://iecmech1.sbc.com/proj-bin/c2f...20%20/&tbl=c2f

Yet in the very frist of the HTML code is the following;
<form method=POST action="/proj-bin/c2fmen"

Is it possible that I need to open the first URL and then post the
second?

You need to POST the data to the URL specified in the action attribute. So
it could be that you don't need the long URL after all. The page that shows
the form and the page submitted to are actually the same in this case.

So try:

.Open http://iecmech1.sbc.com/proj-bin/c2fmen

To see if that works.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]