Filling an online form with content from a word doc.

I would like to write a Macro that does the following.

1.) Takes the first paragraph from my word document

2.) Opens internet explorer and goes to the following link: http://onebank.intranet/news/_layouts/CreatePage.aspx?

This page is a form on our company intranet.

3.) Pastes the first paragraph from my word doc into one of the input-boxes of the form.

4.) Pastes the first paragraph from my word doc into a second input-box in the form but with all spaces replaced for hyphens. I.e. if the first paragraph read "the quick brown fox jumped over the lazy dog", this should paste "the-quick-brown-fox-jumped-over-the-lazy-dog".

I know that for points 3 and 4 you will need some source code for the macro to be able to select the input boxes, but I am not really sure what to look for. If someone could guide me that would be great.

I am very new to VBA, I posted a similar thread about a month ago but my knowledge of VBA was so low that I could barely understand the help people were trying to give me. I have since done an online course, my knowledge is by no means good but I think I now understand some of the very basic terminology, still it would help if you write your answers as if you are talking to complete novice.

Thanks in advance :).

Re: Filling an online form with content from a word doc.

Quote:

This page is a form on our company intranet

no one here can tell you how to login as we can not see your form

there are many examples of logging into web pages, in both this and the vb6 forums, you can search for examples, that can match the source code of your web page

no good trying to do any other part of the code, until your code can login

Re: Filling an online form with content from a word doc.

Its fine, my code doesnt need to logon, I get logged into my intranet automatically when I login to my desktop.

Assume that this is not a barrier. Obviously you wont be able to go to the link yourself, I just gave the link for the purpose of including it in a macro.

Re: Filling an online form with content from a word doc.

ok try
vb Code:
sub putfirstparagraph()
set wb = createobject("internetexplorer.application")
wb.navigate2 "http://onebank.intranet/news/_layouts/CreatePage.aspx?"
wb.visible = true   'optional
do until wb.readystate = 4
  doevents
loop
wb.document("somebox").value = thisdocument.paragraphs(1)
end sub
where somebox is the name of the appropriate input element
assumes the code is written in a code module of the document that contains the paragraph required

this procedure will need to be called by some button click or other event, but for testing you can click the run button in the vb editor
this is only the first step

Quote:

I know that for points 3 and 4 you will need some source code for the macro to be able to select the input boxes, but I am not really sure what to look for

look for form to /form, all the inputs will be within the form, there may be more than one form

Re: Filling an online form with content from a word doc.

Will test the code when I am at work, currently dont have access to intranet.

Quote:

Originally Posted by westconn1

look for form to /form, all the inputs will be within the form, there may be more than one form

I ran into this problem with the first thread I posted, the <form> and </form> are at the beginning and end of the page respectively, I think the whole page is one giant form (sorry, I probably should have mentioned this). Is there another way to select the boxes specifically?

Alternatively this can be done with sendkeys, if someone can paste the sendkeys code for 'tab and 'enter' I could probably get it to work.

I know there should be a smarter way of doing this without sendkeys but perhaps if someone can give me the sendkeys code for now I can start using the Macro while following your instructions on how to find the code to locate the input-boxes.

Re: Filling an online form with content from a word doc.

if you view the source code of the document manually you will see

<input type= ... name or id = ...

these are the bits you need to find you are looking for name= or id= inside these <input tags

when you have identified the appropriate textboxes or memoboxes that we can help you write the correct code!

you will need to copy the paragraph to a temporary place so that you can replace all the spaces with hyphens!

all very straight forward

here to help

Re: Filling an online form with content from a word doc.

sendkeys is not a good way to go, it certainly does not work on this machine
access denied error

"~" for enter
or just send vbtab and vbnewline

Re: Filling an online form with content from a word doc.

Here is what the form looks like.

http://www.vbforums.com/attachment.p...1&d=1330596505

I need to first select the first box ('title:') and paste the first paragraph from my word doc. Then I need to select the third box ('URL name') and paste the first paragraph from my word doc with all spaces replaces for hyphens.

Here is the code for what I think is the right part of the page. I can see in the code the lables for the various input boxes but there are multiple instances of 'input type= name' or 'id =' not sure which are the ones I need.

PHP Code:

<tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl00_tablerow1"> <td class="ms-authoringcontrols" colspan="2" nowrap> <label for="ctl00_PlaceHolderMain_pageTitleSection_ctl00_titleTextBox" id="ctl00_PlaceHolderMain_pageTitleSection_ctl00_LiteralLabelText">Title:</label> </td> </tr> <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl00_tablerow2"> <td><IMG SRC="/_layouts/images/blank.gif" width=1 height=3 alt=""></td> </tr>  <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl00_tablerow3"> <td width="11" ><IMG SRC="/_layouts/images/blank.gif" width=11 height=1 alt=""></td> <td class="ms-authoringcontrols" width="99%"> <input name="ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox" type="text" maxlength="255" size="35" id="ctl00_PlaceHolderMain_pageTitleSection_ctl00_titleTextBox" class="ms-input" onchange="UpdateUrl()" onfocusout="UpdateUrl()" Title="" AlwaysEnableSilent="true" /> </td> </tr> <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl00_tablerow5"> <td><IMG SRC="/_layouts/images/blank.gif" width=1 height=6 alt=""></td> </tr> <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl01_tablerow1"> <td class="ms-authoringcontrols" colspan="2" nowrap> <label for="ctl00_PlaceHolderMain_pageTitleSection_ctl01_descriptionTextBox" id="ctl00_PlaceHolderMain_pageTitleSection_ctl01_LiteralLabelText">Description:</label> </td> </tr> <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl01_tablerow2"> <td><IMG SRC="/_layouts/images/blank.gif" width=1 height=3 alt=""></td> </tr>  <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl01_tablerow3"> <td width="11" ><IMG SRC="/_layouts/images/blank.gif" width=11 height=1 alt=""></td> <td class="ms-authoringcontrols" width="99%"> <textarea name="ctl00$PlaceHolderMain$pageTitleSection$ctl01$descriptionTextBox" rows="3" cols="40" id="ctl00_PlaceHolderMain_pageTitleSection_ctl01_descriptionTextBox" class="ms-input" Title="" AlwaysEnableSilent="true" onkeypress="MultiLineTextBoxOnKeyPress(this, 512)" onpaste="MultiLineTextBoxOnPaste(this, 512)"></textarea> </td> </tr> <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl01_tablerow5"> <td><IMG SRC="/_layouts/images/blank.gif" width=1 height=6 alt=""></td> </tr> <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl02_tablerow1"> <td class="ms-authoringcontrols" colspan="2" nowrap> <label for="ctl00_PlaceHolderMain_pageTitleSection_ctl02_urlNameTextBox" id="ctl00_PlaceHolderMain_pageTitleSection_ctl02_LiteralLabelText">URL Name:</label> </td> </tr> <tr id="ctl00_PlaceHolderMain_pageTitleSection_ctl02_tablerow2"> <td><IMG SRC="/_layouts/images/blank.gif" width=1 height=3 alt=""></td> </tr>

The little I know about html tells me that this page is set up as a table with most of the page made from pictures.

A couple of small questions. When I previewed my post, the code it appears to come out quite messy.

Is there a way for me to tidy this up in future posts? I.e get rid of the extra space on the left or limit the width of the box?
When should I be using the <CODE></CODE> tags and when should I use the <PHP></PHP> tags?

1 Attachment(s)

Re: Filling an online form with content from a word doc.

Had to make another post to upload the picture. Had the picture as an attachment to the previous post but then removed it because I didnt want it to appear at the end of the post which ofcourse made the picture dissapear from the post (facepalm).

Is there a way to have a picture in your post without having it appear again as an attachment at the end?

Re: Filling an online form with content from a word doc.

Quote:

<input name="ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox" type="text" maxlength="255" size="35" id="ctl00_PlaceHolderMain_pageTitleSection_ctl00_titleTextBox" class="ms-input" onchange="UpdateUrl()" onfocusout="UpdateUrl()" Title="" AlwaysEnableSilent="true" />

this defines an input element of type text (single line textbox)
you should be able to put some text in it using as line 8 in code posted above
vb Code:
wb.document("ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox").value = "this is my test"
a mutli line textbox is a textarea

Quote:

<textarea name="ctl00$PlaceHolderMain$pageTitleSection$ctl01$descriptionTextBox" rows="3" cols="40" id="ctl00_PlaceHolderMain_pageTitleSection_ctl01_descriptionTextBox" class="ms-input" Title="" AlwaysEnableSilent="true" onkeypress="MultiLineTextBoxOnKeyPress(this, 512)" onpaste="MultiLineTextBoxOnPaste(this, 512)"></textarea>

note putting code into text elements by code may not fire the javascript events, which, if required, you would also need to fire by code

Re: Filling an online form with content from a word doc.

Right... making progress!

Here is the code im using.

PHP Code:

Sub putfirstparagraph() Set wb = CreateObject("internetexplorer.application") wb.navigate2 "http://onebank.intranet/news/_layouts/CreatePage.aspx?" wb.Visible = True 'optional Do Until wb.readystate = 4 DoEvents Loop wb.Document("ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox").Value = "this is my test" End Sub

Successfully opens the page and navigates to correct destination.

Im bugging on this line

PHP Code:

wb.document("ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox").value = "this is my test"

Runtime error 424 - object required.

Re: Filling an online form with content from a word doc.

oops, try

vb Code:

wb.document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox").value = "this is my test"

Re: Filling an online form with content from a word doc.

have you managed to identify the third box and put you example into it yet...

are you ready for the x-y-z version yet?

here to help

Re: Filling an online form with content from a word doc.

Ok, just tested, code working, successfully pastes in first box.

I looked for something that said 'inputname=' for the third box and found something. Put it in the code and tested it, its working too.

My code currently looks like this.

PHP Code:

Sub putfirstparagraph() Set wb = CreateObject("internetexplorer.application") wb.navigate2 "http://onebank.intranet/news/_layouts/CreatePage.aspx?" wb.Visible = True 'optional Do Until wb.readystate = 4 DoEvents Loop wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox").Value = "this is my test" wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl02$urlNameTextBox").Value = "this is my test" End Sub

Now need to get, "this is my test" from third box to become "this-is-my-test".

I would also like the macro to 'press' the 'create button' (see bottom of form in my previous post), how can I identify the 'create button' in the source code and what should I do to get the macro to 'press it'? Also what is the technical term for 'pressing the button', would be nice to learn these things.

Thanks a lot for the help so far :).

Re: Filling an online form with content from a word doc.

Did some research. Found out that chrome has a feature which lets you inspect web elements.

I did this for the create button and it gave me this bit of code:

PHP Code:

name="ctl00$PlaceHolderMain$ctl00$RptControls$buttonCreatePage"

I assume this is what I need to use to 'press the create button'.

Re: Filling an online form with content from a word doc.

Quote:

Also what is the technical term for 'pressing the button', would be nice to learn these things.

click

Re: Filling an online form with content from a word doc.

Quote:

Originally Posted by westconn1

click

Hmmm.... thought there would be a more technical sounding name.

Ok, so how do I get my macro to

Turn "this is my test" in the third textbox to "this-is-my-test"
Click the 'create button'.

Re: Filling an online form with content from a word doc.

just run the same code again with the new value, or

vb Code:

wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl02$urlNameTextBox").Value = replace(wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl02$urlNameTextBox").Value, " ", "-")

vb Code:

wb.Document.all("ctl00$PlaceHolderMain$ctl00$RptControls$buttonCreatePage").click

Re: Filling an online form with content from a word doc.

Ok, great.

I also modified the code to actually take the first paragraph.

Code now looks like this.

PHP Code:

Sub putfirstparagraph() myvar = ThisDocument.Paragraphs(1) Set wb = CreateObject("internetexplorer.application") wb.navigate2 "http://onebank.intranet/news/_layouts/CreatePage.aspx?" wb.Visible = True 'optional Do Until wb.readystate = 4 DoEvents Loop wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox").Value = myvar wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl02$urlNameTextBox").Value = myvar wb.Document.getElementById("ctl00$PlaceHolderMain$ctl00$RptControls$buttonCreatePage").Click End Sub

Last challenge is to replace all spaces in myvar with hyphens. Is it also possible to remove any non-letter characters from myvar in box 3?

i.e "this is mango's sample: text". Should be become "this-is-mangos-sample-text" (removing both the apostrophy and the colon and replacing all spaces for hyphens).

95% of the way there, thanks so much for all the help so far!

Re: Filling an online form with content from a word doc.

i would fix the content of myvar, before putting into the textbox
replace can work to remove all none text character
replace can also be used to put hyphens in place of spaces
vb Code:
mychars = "!@#$%^""&*("    ' add more to suit
for i = 1 to len(mychars)
  myvar = replace(myvar, mid(mychars, i, 1), "")
next
myvar = replace(myvar, " ", "-")
there would be other ways to achieve the same result, but i would believe this would be as good as any

Re: Filling an online form with content from a word doc.

Ok, everything seems to work except for a small detail that I notice when I step through the code.

I have

PHP Code:

1. wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox").Value = myvar 2. mychars = "!@#'$%^"":;&*(" 'add more to suit 3. For i = 1 To Len(mychars) 4. myvar = Replace(myvar, Mid(mychars, i, 1), "") 5. Next 6. myvar = Replace(myvar, " ", "-") 7. wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl02$urlNameTextBox").Value = myvar 8. wb.Document.getElementById("ctl00$PlaceHolderMain$ctl00$RptControls$buttonCreatePage").Click

When I run the macro all in one go there are no problems.

If I try to step through the code, the macro seems to alternate infinitely between lines 4 and 5. I wonder if this is somehow avoidable, I plan to expand this macro in the future (though I will post a separate thread for it) and it would be nice to be able to step through the code without hitting this problem. Stepping over the line doesnt work either.

Another very small problem is that if for some reason the first paragraph of the word document is left empty (someone pressed enter before putting in the first paragraph), the macro regonizes the empty space as the first paragraph and doesnt work, is there a way to have the macro delete all empty spaces before the first paragraph so that this doesnt happen? Or somehow have it ignore spaces when selecting the first paragraph.

Finally, the last line of the macro clicks the create page button on the form. This redirects the page to another very complex form. I would like to continue writting this macro to fill out this form but that is an entirely different thread so I will post it separately. How can I get my macro to wait for this second form to load, can I just reuse:

PHP Code:

wb.Visible = True 'optional Do Until wb.readystate = 4 DoEvents Loop

Re: Filling an online form with content from a word doc.

Quote:

If I try to step through the code, the macro seems to alternate infinitely between lines 4 and 5.

this is correct, it has to do that for each character you want to remove, put a breakpoint on the line after next, then click the run, to step through all of those and stop after

Quote:

if for some reason the first paragraph of the word document is left empty
vb Code:
if len(myvar) < 3 then myvar = thisdocument.paragraphs(2)
change length to whatever you think could be to short to be valid

Quote:

can I just reuse:

that should work in most cases

Re: Filling an online form with content from a word doc.

you were dong so well

the code for th myvar as m-y-v-a-r (wrong example for effect) is

Code:

wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl02$urlNameTextBox").Value = replace(myvar," ","-")

which returns all the spaces as hyphens in the string myvar ( its a one liner )

here to help

Re: Filling an online form with content from a word doc.

Quote:

Originally Posted by westconn1

this is correct, it has to do that for each character you want to remove, put a breakpoint on the line after next, then click the run, to step through all of those and stop after

Ah that explains it, thanks!

Quote:

Originally Posted by westconn1

change length to whatever you think could be to short to be valid

Im not sure what you mean here. Lets say I want it to account for 8 spaces before the first line, I just wrote multiple lines lines which seems to work:
vb Code:
If Len(myvar) < 3 Then myvar = ThisDocument.Paragraphs(2)
If Len(myvar) < 4 Then myvar = ThisDocument.Paragraphs(3)
If Len(myvar) < 5 Then myvar = ThisDocument.Paragraphs(4)
If Len(myvar) < 6 Then myvar = ThisDocument.Paragraphs(5)
If Len(myvar) < 7 Then myvar = ThisDocument.Paragraphs(6)
If Len(myvar) < 8 Then myvar = ThisDocument.Paragraphs(7)
If Len(myvar) < 9 Then myvar = ThisDocument.Paragraphs(8)
There should be a way to do this with 1 line though no?

Quote:

Originally Posted by westconn1

that should work in most cases

I tried to add in the lines to the end of my code to make the webpage wait (see below).

Line 38 clicks the create button which loads a new page, during this loading time I want the page to wait. To test the waiting I tried to have the macro select and write in an new element on the newly loaded page (line 46), but this doesnt seem to work. I get:

Run-time error '91':
Object variable or With block variable not set
vb Code:
'sets first paragraph as variable
myvar = ThisDocument.Paragraphs(1)
 
'accounts for extra spaces in front of first paragraph
If Len(myvar) < 3 Then myvar = ThisDocument.Paragraphs(2)
If Len(myvar) < 4 Then myvar = ThisDocument.Paragraphs(3)
If Len(myvar) < 5 Then myvar = ThisDocument.Paragraphs(4)
If Len(myvar) < 6 Then myvar = ThisDocument.Paragraphs(5)
If Len(myvar) < 7 Then myvar = ThisDocument.Paragraphs(6)
If Len(myvar) < 8 Then myvar = ThisDocument.Paragraphs(7)
If Len(myvar) < 9 Then myvar = ThisDocument.Paragraphs(8)
 
 
'sets variable for paragraph 2 till the end of the document
Set r = ThisDocument.Range  ' set whole document to range objectr.
Start = ThisDocument.Paragraphs(2).Range.Start  ' move start to paragraph 2
mytxt = r.Text   'assign text value of range to string variable
 
 
 
Set wb = CreateObject("internetexplorer.application")
wb.navigate2 "http://onebank.intranet/_layouts/CreatePage.aspx?CancelSource=%2Fingbankglobal%2Fnews%2FPages%2FNews%2Easpx"
wb.Visible = True
'optional
Do Until wb.readystate = 4
DoEvents
Loop
 
wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl00$titleTextBox").Value = myvar
 
mychars = "!@#$%^""':;&*()_+='’/\|,.<>?~`" 'add more to suit
For i = 1 To Len(mychars)
myvar = Replace(myvar, Mid(mychars, i, 1), "")
Next
myvar = Replace(myvar, " ", "-")
 
wb.Document.all("ctl00$PlaceHolderMain$pageTitleSection$ctl02$urlNameTextBox").Value = myvar
wb.Document.getElementById("ctl00$PlaceHolderMain$ctl00$RptControls$buttonCreatePage").Click
 
'wait for page to load
wb.Visible = True
Do Until wb.readystate = 4
DoEvents
Loop
 
wb.Document.all("ctl00$PlaceHolderMain$datetimefield3$ctl00$DateTimeField$DateTimeFieldDate").Value = myvar