Copy Excel data to web page

[marktimm22]

Excel 2003, Windows XP:

I have 10 columns of address information in Excel. I need to copy the contacts of the columns into a web page that I have no control over. (I can not change the page) In other words, I am filling out a web form with the contents of row 2, hitting accept, then filling out the web page with row 3, etc...

Can this be done with Excel, or do I need to get another product/device?

Thanks!!!

 

[marktimm22]

This is some code I found but it gets stuck on the last while-wend. I thought this might be a good start but it doesn't even work.

Sub LoginMe()

Dim appIE As Object

Set appIE = CreateObject("InternetExplorer.Application")
appIE.Visible = True

While appIE.busy
DoEvents
Wend

appIE.navigate "https://access.leggmason.com/"

While appIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Wend

SendKeys "excel", True
SendKeys "{TAB}", True
SendKeys "hello", True
SendKeys "{ENTER}", True

End Sub

 

[NateO]

Take my example from earlier.

1) Change the url
2) username and password are valid input box names at FedX's login point
3) Change forms(0) to forms("logonForm")
4) Change .login.Click to .Submit
5) Change "index.php" to something that makes sense, something that will be in their url once you are logged in, or you'll loop for eternity.

See if that works.

 

[marktimm22]

Thanks Nate,

I made the changes, and it didn't work. I am trying to get it to work on this page first, then I can make the url changes. It won't even open a browser window. This is what I have:

Private Sub FedExClaim()
Dim ie As Object
On Error GoTo 1
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://www.mrexcel.com/board2/login.php"
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .Document.Forms("logonForm")
.UserName.Value = "marktimm22"
.Password.Value = "password"
.submit
End With
Do While Not CBool(InStrB(1, .Document.url, "index.php"))
DoEvents: Loop
Call ShowWindow(.hwnd, 3) 'Maximize
.Visible = True
End With
Set ie = Nothing
Exit Sub
1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing
End Sub

 

[NateO]

Hmmm, with all due respect, you might be learning to swim in the deep end here...

Delete:

Do While Not CBool(InStrB(1, .Document.url, "index.php"))
DoEvents: Loop

I'd be shocked if FedEx has 'index.php' in any of their urls.

When I said change the url, I mean:

http://www.mrexcel.com/board2/login.php

Becomes:

https://www.fedex.com/fcl/web/jsp/l...x/go/home?cc=US&language=en&programIndicator=

You're trying to navigate to FedEx's login page, not the MrExcel.com message board login page. :wink:


Edit: I see you're missing the api as well, go ahead and delete:

Call ShowWindow(.hwnd, 3) 'Maximize

as well.

 

[marktimm22]

First of all, you are right... I am definitly over my head

That's why I need the help, but as for the changing the URL I just wanted to test it first before trying it on FedEx's page.

It worked! Thanks for the help! (doing it for me actually)

If I could be so bold, now I need it to copy a field to the webpage, I'll play with it and let you know. ANy advice would be helpful. Mark

 

[NateO]

If I could be so bold, now I need it to copy a field to the webpage, I'll play with it and let you know. ANy advice would be helpful.

You already have done this actually, that's how you logged in!

As I said before:

.username.value = "myStr"

could easily be:

.username.value = range("a1").value

Which would pass a1 to a textbox named 'username' on a form.

You're going to have to sift through the html source code, I don't have an account there and have no idea which form or textbox you are speaking to.

Also, you'd want to loop, as I did in my original example so as to wait until you're fully logged in. Testing the url is a good way to do this in my experience.

 

[marktimm22]

Thanks. I logged in and copied the html source to get the field names. I'll let you know how it goes. Thank you very much, I really appreciate it!

 

[NateO]

You are welcome. Bon chance.

 

[dscheste]

Nate - thank you for the nice piece of code, I have been looking for a solution to do some automation in another forum and your code came very handy.

I have tested it here in the testing area, so if you have control over this section, please delete the test topic:

I will also drop a message to a mod.

Here is the code:

I was assuming the flood control is about 30 sec.



Public Declare Function ShowWindow& Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Integer)

Sub Post_to_MrExcel()
Dim ie As Object
Dim cell As Variant
Dim rng As Variant
rng = Range("A1:A18") 'any range with messages to be posted
On Error GoTo 1
For Each cell In rng
'/////////////// Login sequence
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://www.mrexcel.com/board2/login.php"
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .Document.Forms(0)
.UserName.Value = "dscheste" 'Change to YOUR Login ID
.Password.Value = "password" 'Change to YOUR password
.login.Click
End With
Do While Not CBool(InStrB(1, .Document.URL, "index.php"))
DoEvents: Loop
End With
'/////////////// Actual posting
With ie
.navigate "http://www.mrexcel.com/board2/posting.php?mode=reply&t=107238" 'test topic, I would use it as a topic to post special statistics ina forum or periodic announcements
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .Document.Forms("post")
.Message.Value = cell
.attach_sig.Value = "checked"
.Post.Click
End With
End With
Set ie = Nothing
Application.Wait (Now + TimeValue("0:00:30")) 'well, we have to consider the flood control
Next
Exit Sub
1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing
End Sub

 

[NateO]

Hello, five things:

1) You're welcome
2) Whoa, your creating an IE instance 18 times, then logging in 18 times. Just create the object once, login once and loop within that code.

Loop in here:

.navigate "http://www.mrexcel.com/board2/posting.php?mode=reply&t=107238" 'test topic, I would use it as a topic to post special statistics ina forum or periodic announcements
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .Document.Forms("post")
.Message.Value = cell
.attach_sig.Value = "checked"
.Post.Click
End With

Also, you still have your With IE statment working for you, so no need to do it again.

3) Don't use a timer, unreliable at best when loading to servers via the Web, etc... Loop on the url or text you know will be in the document of final destination. There's an example within the code you posted (following the logging in part ).

4) Cell and Rng need not be variants, they're range objects. So:

Dim Rng as Range

5) I am a moderator. Topic deleted.