Web Farming

M

Mike

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.
 
J

Jake Marx

Hi Mike,
Open "POST", would be the web page I want to send data to.
Click to expand...

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
abytPostData would be the data to send, but I don't understand the
format.
Click to expand...

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.
Click to expand...

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]
 
J

Jake Marx

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]
 
M

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/c2fmen?report=UPDATE&cktid=28/HCGS/713101 /SW /&tbl=c2

<FONT COLOR=RED SIZE=3><B>Data as of 2004-04-30 10:10:25</FONT><BR><BR><a href="/SSPROJ/">Main Page</a></form><HR><FONT color=red size=5><B>Update 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=yellow>Customer Name</TD><TD><input type=text name=cust_name value="HUNTER-AND-SAGE" size=64 maxlength=63></TD></TR><TR><TD bgcolor=yellow>Address</TD><TD><input type=text name=addr value="6161 SAVOY" size=64 maxlength=63></TD></TR><TR><TD bgcolor=yellow>Market Area</TD><TD><select name=ma><option value=ARKANSAS>ARKANSAS</option><option value=AUSTIN>AUSTIN</option><option value=DALLAS>DALLAS</option><option value=HOUSTON SELECTED>HOUSTON</option><option value=KANSAS>KANSAS</option><option value=MISSOURI>MISSOURI</option><option value=OKLAHOMA>OKLAHOMA</option><option value="SAN ANTONIO">SAN ANTONIO</option></select></TD></TR><TR><TD bgcolor=yellow>ACNA</TD><TD><input type=text name=acna value="WTL" size=9 maxlength=8></TD></TR><TR><TD bgcolor=yellow>CAC</TD><TD><input type=text name=cac value="SSN6CK2" size=8 maxlength=7></TD></TR><TR><TD bgcolor=yellow>LSO</TD><TD><input type=text name=lso value="HSTNTXSU" size=12 maxlength=11></TD></TR><TR><TD bgcolor=yellow>MCN</TD><TD><input type=text name=mcn value="" size=16 maxlength=15></TD></TR><TR><TD bgcolor=yellow>MCO</TD><TD><input type=text name=mco value="HSTNTXKLSSC" size=12 maxlength=11></TD></TR><TR><TD bgcolor=yellow>CLO</TD><TD><input type=text name=clo value="HOS935173001" size=13 maxlength=12></TD></TR><TR><TD bgcolor=yellow>Due Date<BR><FONT color=red size=2>YYYY-MM-DD</FONT></TD><TD><input type=text name=dd value="2004-02-28" size=11 maxlength=10></TD></TR><TR><TD bgcolor=yellow>Completion Date<BR><FONT color=red size=2>YYYY-MM-DD</font></TD><TD><input type=text name=cmpdte value="0000-00-00" size=11 maxlength=10></TD></TR><TR><TD bgcolor=yellow>Fiber-Scid Ready</TD><TD><select name=scid><option value="Y" selected>Y</option><option value="N">N</option></select></TD></TR><TR><TD bgcolor=yellow>Pre-Service Dispatch<BR></TD><TD><select name=presvc><option value="Y" selected>Y</option><option value="N">N</option><option value="SSDAC">SSDAC</option></select></TD></TR><TR><TD bgcolor=yellow>Customer Release Date<BR><FONT color=red size=2>YYYY-MM-DD</font></TD><TD><input type=text name=cusrel value="2004-12-24" size=11 maxlength=10></TD></TR><TR><TD bgcolor=yellow>Comment</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>
 
J

Jake Marx

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]
 
M

Mike

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/c2fmen?report=UPDATE&cktid=28/HCGS/713101 /SW /&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 /SW /&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
 
J

Jake Marx

Mike,
The web page that displays the form to update is;
http://iecmech1.sbc.com/proj-bin/c2fmen?report=UPDATE&cktid=28/HCGS/713101 /SW /&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?
Click to expand...

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]
 
Top