Transfering excel variable to a webpage

[Elari35]

Hi,

 

I was just wondering what were the exact steps for the following procedure:

 

I have a value in excel cell D6. The value can range from 1-10. The website where I need to transfer that info has a dropdown list. I saved the pictures of the dropdown list in the attachment.

 

What would be the fastest and simplest of ways to do it? Each of the cell number corresponds to the number in the dropdown list. The goal would be to select the correct number in the list.

 

Thank you

[Yehnfikm8Gq]

There are a few issue on the web page. It's assumed you know how to get to the list on the page. Here are the basics:

In Excel

Text Type Ctrl+G

Text Type Enter

Text Type D6

Text Type Ctrl+C or Clipboard Copy

(for some reason comes with CR/LF (carriage return+line feed)

Variable Set String T1 from Clipboard

Variable Modify String Strip CR/LF (may not be required but does no harm)

Variable Modify String T1 to Integer N1

Activate web page, navigate to list, using Text Type Tab if possible

To ensure at top of list:*

Text Type Home

(if that does not work, Text Type at least 10 Up arrows in case list is remembered from last access)

Text Type N1 down arrows (Repeat loop?)

Navigate to list action button on webpage (Text Type Tab?) - may not be necessary if next command works

Text Type Enter

*at this point sometimes typing the first letter will get the correct item but you have 2 As and 2 Es so stick with the arrow procedure

 

The number of arrows may depend on list behaviour. May have to do one more or less down arrows than N1.

This sort of procedure cannot be predicted entirely from a brief description. One needs to have the page for testing. Expect to do other workarounds.

[Elari35]

Hi again,

 

JohnS, thank you for the great tips and nicely written out guide. It worked for me and I managed to solve my problem! Thanks a lot!

 

Elari

[pferris]

Hi again,

 

JohnS, thank you for the great tips and nicely written out guide. It worked for me and I managed to solve my problem! Thanks a lot!

 

Elari

 

JohnS is a real godsend to this forum!

 

I do something sort of similar - sans the Excel aspect (but as JohnS alluded to - that should be fairly trivial. You can even have MEP switch to the spreadsheet and go to the cell and "scrape" the value for you.

 

In my case, I go to a web page where I have to enter the DAY of the month in a rather gruesome way; e.g.: If today is the 17th, I have to go to the correct drop-down and then "down arrow" 17 times. But since this is a macro that gets run every day, the constantly changes (as your Excel value may vary between 1 and 10 or whatever). This is simlar to what JohnS showed, I think. The delays are necessary because the server I'm dealing with ranges from "fast as lightning" to "slow as molasses". Depending on day of week, estrogen levels of server, phase of moon, height of tide, etc; I found these delays work well for me. Here's a sample of my code:

 

=-=-=-=-=-=-=-=-=

 

// Now let's do the day...

Delay: 400 milliseconds

Mouse Move: 430, 446 Relative to Screen //move to the dropdown trigger point

Delay: 400 milliseconds

Mouse Left Click

Delay: 400 milliseconds

Repeat Start (Repeat %Day_As_Integer% times)

Text Type (Simulate Keystrokes): <ARROW DOWN>

Delay: 80 milliseconds

End Repeat

Text Type (Simulate Keystrokes): <ENTER>

=-=-=-=-=-=-=-=-=

 

 

The actual "%Day_As_Integer% is set elsewhere, and not necessary for your purposes. Nevertheless, I THINK this will serve as a little example for you (or anyone else that might ever be interested!). Hope this helps. Sounds as if you're already on your way to success though. Let us know if we can help!

 

Cheers,

 

--Pete

 

P.S.: To the MEP Curious, this is how I set the %Day_As_Integer%, there may be other / better ways, this is just how I do it:

 

Date/Time: Set %Todays_Day_DD_Format% to the current date/time using "dd" as the format

Variable Modify String %Todays_Day_DD_Format%: Convert to Integer (%Day_As_Integer%)

[Yehnfikm8Gq]

Re Help in general, it's a community effort although I take credit for maximum verbage, "Never mind the quality, feel the width".

 

Good to see someone else's code. I'm sure I've mentioned this before but one can look at some of the things we do with ME and they seem a bit tedious. Case in point, up to 30 down arrows to enter a date? My comment, what else is your PC going to do with its time?...and you don't even have to give it Air Miles. There are so many ways of doing things:

 

1. You can do a down arrow for each day (checked)

2. If Home and End work you can do the lower half with End and count up

3. You can enter 1, 2, or 3 and then the digit down arrows for 11, 12 etc, or count up

4. PM to self, create hotkey macro to get to T99 etc in Set dialogs (actually that's just an easy, whopping mouse drag)

 

BTW us stick-in-the-muds still use Set Integer Variable to Day of Month but it's so passe. The Date/Month additions in Pro are awesome.

[Elari35]

Hi again guys,

 

Thanks for the additional tips and samples of code. I'll try to learn something from them.

 

I mostly have everything in the bag except for one thing, dropdown menu.

 

Lets say my excel cell A2 has a value 6.4.2 and I need to transfer that to the dropdown menu. For x.x value I could just copy it, set to string and then to integer and just text type that into dropdown list. This works when I manually do it but haven't added it to the macro script yet.

 

My question is that can the dropdown menu be used somehow to quickly get to the right line or do I have to write a large script so if the value is 6.4.2 then you need to click the down arrow for x times?

The problem is that I have a lot of codes and the dropdown menu can change depending on the presettings before I get to it. If the down arrow way is really the only way then can you give me any tips how to write it the best or if it even possible to somehow make a txt or some file where it is all written down so I don't need to write that many lines for each value?

 

Hopefully you understood my point and thanks

 

Elari

[Yehnfikm8Gq]

This is quite a difficult problem. You can usually navigate down a list if each item starts with a unique character. Type "C" and you will get the first item starting with "C". If there is are more than one item it is of limited value. If the list is fixed, you can then arrow down a number of times to get to another "C" item. With a varying list it is not much help, other than the fact you can get part of the way down the list.

 

The only method I can think of would be as follows:

Use the "6" to get to the start of that section of items. A small time delay may be necessary for the list to appear. You may have to type "6" and down arrow. You may have to type "Enter", "Esc" or another character to select the item without opening the selection. You could also try typing the full set "6.4.2" to see if that gets you any closer and may save extracting the "6" **.

 

Copy the item text to a Text Variable

See if the Text Variable contains the string you are looking for

Arrow Down, repeat the copy and comparison until you find the item, then proceed.

 

This will be slowish, depending on text length and number of similar items.

 

**I tried that with Firefox's Save dialog and it even found multi-letter strings directly that were not visible on the list, perhaps 100 items further down. That could be a good or bad thing and may be application-specific. For everyone's benefit, could you post your experience with this so we can learn too?

Edited February 23, 2010 by JohnS

[Elari35]

Hi again,

 

To the followup of the last question due to the new and changing nature of the website I managed to gain contact with the webmaster and provided some ideas how it could be done. Hopefully the website will undergo a update soon.

 

I have managed to get my macros working well but few small details that perhaps you could iron out.

 

1) Excel 2007 windows activate. I have a Excel file open but no matter what I write or do it doesn't work if there are more than 1 window of Excel open. For example I have my file and another excel file open in different windows. No matter if I set it to exact match and set the exact name it still won't open the correct Excel. Is there a solution for this or the only advise is to keep one Excel window open constantly?

 

2) Internet Explorer 8. I have a webpage where I need to navigate to a button to activate it. Sadly the problem is that if I navigate there with the tab key then I have no means of activating it. Clicking enter makes the webpage click on another button at the page that means "next page" rather than the link I need to open. Mouse locator seems to like to act up sometimes making it slightly miss the coordinates. No idea exactly what is causing it. In the end I though that perhaps if I opened the buttons URL then it works but sadly I don't seem to find a way to activate a URL through MEPro without causing it to open a new window. I clicked the settings in the internet options but nothing. Any advise how this could be done would be greatly appreciated.

 

Thanks again for taking the time to answer,

 

Elari

[Yehnfikm8Gq]

I did not have any problems with Window Activate or Get Control/Set Focus but I have an older version of Excel. If you are opening windows of variable names you can get the (full or partial) window title or get control when it's on top on opening. Exact name should work but even the slightest change in title will cause a window not to be selected.

 

Re browser, if the button location varies, do several mouse clicks at slightly different positions to ensure you get it. Use change of page title; mouse cursor is hour glass; colour changes etc to tell the page has changed. Any changes to browser layout will affect position of button (including no-tabs to tabs). You can also find a local pixel of unique colour and move mouse from there - quite reliable apparently.

 

To open in the same window is a function of the browser settings/shortcuts. I don't use IE8. If you can get the URL, type it in the location bar and the page will open in the same window, if that's what your browser is set to.

[kevin]

1) Excel 2007 windows activate. I have a Excel file open but no matter what I write or do it doesn't work if there are more than 1 window of Excel open. For example I have my file and another excel file open in different windows. No matter if I set it to exact match and set the exact name it still won't open the correct Excel. Is there a solution for this or the only advise is to keep one Excel window open constantly?

I have noticed in my manual (non-automated) use of Excel that it tends to open new workbooks as windows in the same instance of Excel. I get around this by launching a second instance of Excel and clicking File, Open to open the new file. This can be automated via a macro.

 

2) Internet Explorer 8. I have a webpage where I need to navigate to a button to activate it. Sadly the problem is that if I navigate there with the tab key then I have no means of activating it. Clicking enter makes the webpage click on another button at the page that means "next page" rather than the link I need to open. Mouse locator seems to like to act up sometimes making it slightly miss the coordinates. No idea exactly what is causing it. In the end I though that perhaps if I opened the buttons URL then it works but sadly I don't seem to find a way to activate a URL through MEPro without causing it to open a new window. I clicked the settings in the internet options but nothing. Any advise how this could be done would be greatly appreciated.

Please do not mix topics in a single post. It is best to start a new topic. Answers to different topics in a single post get confusing.

 

Briefly, have you tried doing a 'find' on the web page and then moving the mouse as needed to be over the button? You could then use the Mouse Left Click command to click on the button.