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
NirvanalearnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramesh VCommented:
Hi Uday,
Can you send me the excel you are trying?
Ejgil HedegaardCommented:
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
NirvanalearnerAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NirvanalearnerAuthor Commented:
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 HedegaardCommented:
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
NirvanalearnerAuthor Commented:
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
NirvanalearnerAuthor Commented:
Hi Ejgil,

a loop with the format that you have mentioned in the file "Attachament-A" will be helpful
Ejgil HedegaardCommented:
There is a loop in the file for Sheet2, see Module1.
I have just added the For..Next loop and the cell references to your code to show how it can be done.
Guess this is just a part of your code, because reference to IE is needed to make it work.

No loop is possible for Sheet1, since there are only 1 reference, the range A1:F3.
For the code above (in your initial question) the fixed values just need to be changed to cell references

IE.Document.getElementById("ctl00$ContentPlaceHolder1$Kunde_Number").Value = "12345"
changed to
IE.Document.getElementById("ctl00$ContentPlaceHolder1$Kunde_Number").Value = Worksheets("Sheet1").Range("B1")
and similar for the others.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NirvanalearnerAuthor Commented:
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
NirvanalearnerAuthor Commented:
the soilution whas exactly what i was looking for
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.