Visual Basic code working with Java! (need VBA code)

[zaq_37]

I need a fairly complicated script here (complicated by my standards), excel is only used a bit as a reference for pulling numbers... either way let me know what you guys think about this little problem and if you can assist me


i have little VBA knowledge and it mainly consists of copying and pasting codes from the internet, but I do have SOME understanding on how to write a few of them out. So doing this on my own would be a large undertaking.


OK - I have a spreadsheet with a long list of numbers on it going straight down a column, usually column B but it really doesn't matter.

now... I am copying a number from that list and pasting into an "intranet" page on our company's server that is run by java script.
the number is a reference number for a customer's record in our files

so here is what is going on.

I am copying the number (around 5-8 digits) into a box and hitting a "submit" button on the page (submit button is java script of course)
after it loads up it gives information about the record the number referes to.
sometimes loading the record can take anywhere from 5 seconds to 5 minutes. So after the record's information loads up there is another java button that loads on the page that says "cancel 212421" (the 212421 is an example of the numbers I will be pulling from the excel page) I need to click that button and let the page reload again, once the page reloads I need to take ANOTHER number from the excel sheet and paste it into the box again and submit again and cancel again, and this just goes on and on all day...

I want to automate this so I don't have to do it all day over and over.

so one full run of what I need automated is copying the number from the excel sheet, pasting it into the box on the intranet page, clicking "submit" and letting the page load. once it's loaded, click "cancel ###" let the page reload, go back to excel sheet for next number.

I thought about using sendkeys function to achieve it, but using Java Script in the buttons won't allow me to select it without using the mouse.

Also I need to be able to wait for the page to reload before continue with the script and I have no way of knowing how long it will take for it to completely finishing loading.

What would also be nice would be a "STOP" function that will somehow allow me to STOP the script if I need to.
I really hope someone can help me with this, it will save me a lot of time throughout my day.
if you don't think the ENTIRE thing can be automated are there any parts of it that can?

Thanks guys!

 

[Norie]

What makes you think you need to use Java or JavaScript?

Also what's a 'Java' button? Sure butttons on a webpage can be used to trigger code but that's all they really do.

There are other methods of automating webpages, buttons etc.

For example if you were inputting the data into an input field on a form on the page you could use it's 'submit' method to proceed.

There are also various events associated with buttons etc.

It could be possible to automate all of this using VBA.

Java/JavaScript might come into it but it probably might not be part of the code.

 

[zaq_37]

oh that's awesome, it would be nice to automate the entire process.

i figured that Java would have to be used because the webpage it created it Java.... this shows how much I really know about VBA.

My extent of VBA has been a few scripts mostly using sendkeys and it's stuff i have found on the internet. that is why I posted this

by JAVA button i mean it's a button that is embedded in the page

this is the Java script that is in the page source code for the button

<input type=button name="btnToHS" id="btnToHS" value="Cancel #####" *******="f_MoveToDO(#####)">

hope this helps

 

[Norie]

That isn't JavaScript, it's HTML for an input button that might be calling some script, Java or otherwise.

Exactly how you can do what you want is hard to tell.

It is possible to automate IE from VBA but there's no 'generic' code for it.

What I mean there is that the code needed is almost solely dependent on the webpage and the HTML behind it.

The normal starting point for this sort of thing would be to take a look at that and try to work out how to proceed.

One problem in this case is that you appear to be working with an intranet, so the site/page might not be accessible to most people.

 

[zaq_37]

ok, so HTML can be manipulated by VBA?
and yes the code is not really accessible other than just "view source"

so in order to do this i would need to be able to call the same functions that the HTML does?

would there be a way to use the code from the button i posted above and incorporate it into a VBA script to do what i need it to do, or is the code from that button dependent on the rest of the page when it populates the record?
i hope that makes sense...

 

[xenou]

Its hard to be specific without knowing much more than there are fields on the web page form to put numbers into and a button to click. As Norie mentioned, with the page on an intranet there is no easy means for "outsiders" (like us) to inspect the pages structure and write code for it (I'm not much of a hand at this kind of thing anyway).

It sounds like you *might* want to either consult your IT staff, or an outside consultant. Otherwise, you will certainly find example of this kind of thing on this board and elsewhere. Its not hard but its a little different and some knowledge of html helps (particularly, the DOM) - find the form field, enter data into it, submit the form, loop/wait for a response, and meanwhile keep track of where you are in your list of entries.

I'm not really competent to do this kind of thing for you, I'm afraid (I've only tried a few simple and experimental forays into coding to a web page). So these are just some general thoughts. Note that you can use VBA for this. You could probably use other languages too for that matter - the fact that you are working with Excel could mean VBA would be a good fit here.

 

[zaq_37]

ok, i think i may have figured this out, but i am missing one thing.
is there a line of VBA code that will stop the code from running until the intranet page (Internet Explorer) is finished loading?

 

[xenou]

Something like this is typical:

Do Until ie.ReadyState = 4 [COLOR="SeaGreen"]'//4 = readystate_complete[/COLOR]
Loop

Maybe that will help? In the above code ie is a reference to the web browser - you may need to substitute your own variable or object reference.