Automatically filling web form

[radkri]

I have to fill a web form every day with data stored in an excel sheet. The web form has customer name, customer ID, street,city,state,country,phone,client name,client ID, no.of orders required, mode of payment(drop down box containing credit card, checking, paypal,check,money order).

This data for about 100 customers are stored in an excel sheet (The information keeps changing in this excel sheet).

I have to manually type in the above details one by one in the web form(ASP) and click a submit button.

I want to automate this by sending all the data, on a click of the button, from the excel sheet to the web form and submit the data.

There are several third party tools for autofilling web forms are available.

Need this functionalitiy to be done through Excel VBA.

Any comments.

 

[SkipVought]

Hi,

What design or code do you have so far?

What 3rd party software are you using?

Do all the rows in your workbook get updated on your web form?

Skip,

_[red][/red]

 

[radkri]

Hello,
I started like this.
Dim Obj As Object
WebPath = "http:\*****\Orders.htm"
Set Obj = CreateObject("InternetExplorer.Application")
Obj.Visible = 1
Obj.navigate (WebPath)

Obj.document.frmOrder.Name.Value = "VamSystems"

Obj.document.frmOrder.State.selectedIndex = 5
Obj.document.frmOrder.State.FireEvent ("onchange")

Obj.document.frmOrder.Country.selectedIndex = 1
Obj.document.frmOrder.Country.FireEvent ("onchange")

Obj.document.frmOrder.ClientID.Value = 19

This seems to be a novice code.

I have not used any third party autofill. When I was posting this at various sites and search engines, I am getting several responses about these tools.

Yes. All the 100 rows have to be submitted. one row represents one form entry.

This process I have to do every day at a specific time. The excel file is stored in a specific folder. Is there any way to automate the entire process through windows scheduler, etc so that all I have to do is replace the excel file with a new one(with the same file name) whenever the data changes?

Sorry if I am not expressing clearly.

Thanks!

 

[SkipVought]

" replace the excel file with a new one(with the same file name) whenever the data changes"

How does this data change?

I suppose that scheculer could open your workbook and run the routine on the Open Event.

So there's 4 basic things that your routine has to do.

Open the browser to the form

Loop thru each row of data on your sheet

Map the data from the sheet to the form

Submit

Here's what a loop might look like...

with YourSheet.UsedRange
  lStartRow = .row + 1 'assuming that the first row is a heading
  lLastRow = lStartRow + .rows.count - 1
  iStart Col = .column
  iLastCol = iStartCol + .columns.count - 1

  for lRow = lStartRow to lLastRow
      f=1
      for iCol = iStartCol to iLastCol
         Obj.document.frmOrder.Fields(f) = yoursheet.cells(lRow, iCol).value
         f=f+1
      next
  next
end with

I'm only guessing at the web form syntax.

Skip,

_[red][/red]

 

[radkri]

Hello SkipVought,

Thanks for the excel part coding.

I get new excel file from another user whenever the data changes. For example if the client changed his address or a new client is added or an existing client is deleted etc.

I used to replace the old excel file with this.

Is there any VBA code already written based on the IE object WebBrowser Control for this autofilling of a web form and submitting it?

Thanks again.

 

[CautionMP]

radkri,
faq707-6399: How to automate web forms from VBA using Internet Explorer.

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)