asked on

Excel to Web automation

Hi I am trying to automate excel to web and tried the below code wchih is working fine however, i need the values to be picked up from excel (cell a1, b1 etc.) and loop after every submission

Application.StatusBar = "Submitting"
' Wait while IE loading...
While IE.Busy
DoEvents
Wend
' **********************************************************************
delay 2
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Kunde_Number").Value = "12345"
delay 1
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Kunde_Name").Value = "lars"
delay 1
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Kunder_Ref").Value = "52369"
delay 1
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Total_Amount").Value = "50.26"
delay 1
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Vat_Percentage").Value = "11"
delay 1
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Invoice_No").Value = "436652"
delay 1
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Status").selectedindex = 2
delay 5
IE.Document.getElementById("ctl00$ContentPlaceHolder1$btnSubmit").Click
'**********************************************************************
Application.StatusBar = "Form Submitted"
IE.Quit
Set IE = Nothing

Application.ScreenUpdating = True
End Sub

Private Sub delay(seconds As Long)
Dim endTime As Date
endTime = DateAdd("s", seconds, Now())
Do While Now() < endTime
DoEvents
Loop
End Sub

Ramesh V

Hi Uday,
Can you send me the excel you are trying?

Ejgil Hedegaard

To get a value from cell A1 on the active sheet, you can use Range("A1") like this
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Kunde_Number").Value = Range("A1")

Or Cell(Row,Column) to make it more flexible.
The row and column numbers can be assigned to variables, so the first loop get the values from column 1 (A), and next loop get the values from column 2 (B) etc.

But without a real workbook, it is difficult to see what the loop must do.

Besides that you should reset the Statusbar at the end of the sub, like this
Application.StatusBar = False

Nirvana

ASKER

Thank you Ejgil Hedegaard, I will add excel tommorrw however , each field is a column for example kunde number in a2 , kunde reference in b2 etc once and after H2 once it clicks on submit it have to go to a3 and so on

Nirvana

ASKER

Hi Ejgil Hedegaard, attached the input file where it would pick the data and load it in the webite and loop all the yellow cells will be manually keyed and orange cells are web fields
attachment.xlsx

Ejgil Hedegaard

The cells A1:G3 are connected to a source, but I don't see how that is updated.
Is that how the data is supposed to get into the worksheet?

Don't you get the data from a product database in an ERP system?
Then it should be possible to get an output like on Sheet2, witch is a more suitable design for loop.
Web fields as headers, and records in the rows below.
See the macro in Module1, where I added how the loop (For..Next) can be done.

If the list is long, it will take a while with all the delays.
Is it really needed, that the delays must be so long?
Or is it possible to check for IE.Busy and then just a short delay after, like 0.2 seconds.

The setup on Sheet1 is also possible, but something must trigger update of the fields for the next submit.
attachment-A.xlsm

Nirvana

ASKER

The data in excel is keyed manually, so you must be thinking why can't they key manually directly in the website the reason being they update the same data into multiple websites

We can have all the web fields in the attachment as headers and data in rows and loop each row after submit once page is loaded

I do not such long delays

Thank you again for all your help

Nirvana

ASKER

Hi Ejgil,

a loop with the format that you have mentioned in the file "Attachament-A" will be helpful

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nirvana

ASKER

Thanks a lot!!!Ejgil Hedegaard exactly what i was looking for. Can't thank you enough..wsih i had a brain like yours :)

Thanks again

Nirvana

ASKER

the soilution whas exactly what i was looking for