Sleeper: VBA Help on Excel to Web portal

[baigm]

Team i need a Macro which can open a inv in webportal (chrome) and click a specific button of change in that portal.

have a list of invoices to do this to around 1000 in a excel tracker

can you please support

Regards
Baigm

[georgiboy]

As a starting point GIYBF on 'Selenium Basic'

[baigm]

Hi thanks for this - i have done the Selenium basic and as below, but its running only for one. i want the macro to run for all the inv list i have in the excel tracker one after the other, pls suggest.

obj.Get "weblink//0/invoices/43536"
obj.FindElementById("change_exported").Click

[Aussiebear]

Is that all of the code that you currently have?

[baigm]

Sub testEdgeAuto()
Dim obj As New WebDriver
obj.Start "edge", ""
obj.Get "weblink/nvoices/43531"
obj.FindElementById("change_exported").Click
Element details <button id="change_exported" class="buttonAsLink" type="button">&nbsp;(Change)</button>
End Sub

my invoices links are in excel Sheet1 and from A1 - A500, the macro has to just open 1 by 1 invoices in Edge and just click the Change button.

Thanks
Baigm

[Aussiebear]

One other thing which concerns me is you suggesting a "button to change" on the portal. Are you uploading or downloading the invoices? If uploading the invoices then get the webmaster to show how to upload.

[baigm]

Change.JPG

I am not uploading anything, i will just open an invoice in the portal (copy pasting invoice link from excel to Edge) and click on a button called "Change" which will make the invoice re available for interface that's all.

But this opening of invoice and clicking change button i have to do for 500+ invoices which i have as a list in excel

[georgiboy]

What happens after the button has been clicked - does the page reset, do you have to click a back button?

Remember you have the website and the spreadsheet - we have neither so at this stage would be guessing at solution. It is tricky to do any work on web automation without access to the website and the data being used.

With the information we have i can only guess at some kind of loop, i was not sure if the invoice number or the whole link is in column A so:

Sub testEdgeAuto()
    Dim obj As New WebDriver, rCell As Range
    obj.Start "edge", ""
    For Each rCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Cells
        obj.Get "weblink/nvoices/" & rCell.Value
        obj.FindElementById("change_exported").Click
    Next rCell
End Sub

I also noted that the request changed from Chrome to Edge through the thread, maybe this was to do with when you read about Selenium?

Looking at that screenshot of a website is like me looking at a picture of a wheel nut and deciding to buy the car from it.

[baigm]

Hi Thanks for this - when i run this, macro is opening a new web page every time and asking for id and pwd, to your question once we click the change button - the option next to it will change to "No", then we need to take the next invoice from the excel and do the same. untill all status gets changed to "No".

i didnt got the correct drivers for Chrome which i am using, so i am going ahead with Edge.

can you please support in changing the code - so that all the invoice links open in already logged in edge web page, one by one. instead of opening new web page for every invoice.

Sub testEdgeAuto()
Dim obj As New WebDriver, rCell As Range
obj.Start "edge", ""
For Each rCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Cells
obj.Get "" & rCell.Value
obj.FindElementById("change_exported").Click
Next rCell
End Sub

my inv list in excel looks like below sample


inv screenshot.JPG

[georgiboy]

To do that we will need to know the name of the input box on the website where the invoice number is to be placed.

I am guessing again:::

you have an input box on the webpage for the invoice number? (we need the name of that box, you will need to play with selenium to get it to write something in the box to confirm it works)

You click GO button after the input box has been filled? (we need the name of this button, you will need to play with selenium to get it to click the button)

Once you click GO, what happens - does it go back to the invoice input box?, do you have to click back?, do you click another button?

Without access to the website or any of the data you are going to have to do quite a bit of work yourself, we can only assist with guesswork.

[baigm]

Hi

Thanks for your responses, my flow ask is simple sorry if i am confusing much.

i have all the invoice links in excel sheet - the macro/flow should copy each of the invoice links and open the same in edge.
once the invoice is open in the portal it should just go to the status change which is "Yes" always and click on that and once we click on this Yes it will change to "No".
then the macro should come back to excel and copy the next invoice link and paste in the same opened webpage and do the status change.
it should perform this activity until all the invoice links in the rows gets over.

We should also give the wait time to invoice to be opened in webpage. then click status change in the coding.

Appreciate your help!

Regards
Baig