Websites & Variables

[mtbsteve]

Hi everyone,

 

I'm completely new to ME and have been trying to get my head around a problem that would help some of my office staff.

 

I'm trying to read strings out of an excel file (1st column only) into a variable. This variable would be used as part of a URL that I need to launch.

 

Example:

Read number 123456 from excel file into variable %v1%

 

Load page: www.fakepage.com/editjob.php?jobnumber=%v1%

 

I would then use that same address for the 'wait for web page'.

 

It would be part of a repeat-until loop, so it can 'edit many jobs' based on whats in the excel file.

 

If anyone could help, it would be much appreciated. (a basic example would be great!)

 

Regards,

Steve

[kevin]

Macro Express cannot read directly from an Excel file. I can think of two options.

 

You can open Excel and move through the spreadsheet one cell at a time. Copy each cell to the clipboard and then read the clipboard into a %T% variable.

 

Excel can export a spreadsheet in a .csv format. Using the ASCII Text Begin Process and ASCII Text End Process commands your macro can loop through each record placing the content of that record in a %T% variable.

 

Once you have the content of the cell in a variable you can use one of the many Variable Modify commands to manipulate the text.

 

Here is a quick example using the ASCII Text Begin/End Process command. This example assumes you have already created the .csv file.

ASCII File Begin Process: "text.csv" (Comma Delimited Text )
 Web Site: http://www.fakepage.com/editjob.php?jobnumber=%T1% [Default Browser - Wait to load]
 // Other macro commands here
ASCII File End Process

This example uses the 'Wait for Web Page' option in the Web Site command to eliminate the need for a second macro command.

[mtbsteve]

Thanks for the reply Kevin - I've managed to get it launching websites from the csv file.

 

Part 2 (if I may be so bold). Can I now loop that process so for each string from the csv file so that it opens the web page, performs a small task on that page, then moves onto the next entry in the csv file (opens next address)? I'd also like to do it so that I'm not opening a new window everytime I want to goto the next web address (I could be processing >50 strings).

 

Thanks...

[kevin]

You can place any number of macro commands between the ASCII File Begin Process and ASCII File End Process commands so there is no problem doing "a small task on that page" before moving on to the next iteration.

 

The Web Site command opens up a new instance of the browser. However, you may be able to perform one Web Site command outside the ASCII File Begin/End Process loop and use a Text Type command to go to a new url.

 

This technique requires a test for a specific browser. To go to the Address field in IE or Firefox you need to type ALT+d. To get to the Address field in Netscape you need to type ALT+l (lowercase L).

 

Something like this may work:

// Determine which browser is running and set the appropriate code to 'go to the Address bar'

Variable Set String %T50% from Window Title

If Window Title "- Mozilla Firefox" is on top

  OR

If Window Title "- Microsoft Internet Explorer" is on top

  Variable Set String %T51% "<CONTROL>d"

Else

  If Window Title "- Netscape" is on top

    Variable Set String %T51% "<CONTROL>l"

  End If

End If

 

// Load the browser

Web Site: http://www.fakepage.com [Default Browser - Wait to load]

 

ASCII File Begin Process: "test.csv" (Comma Delimited Text )

  // Go to the Address bar

  Text Type: %T51%

  // Wait a moment for the browser to respond

  Delay 0.1 Seconds

  // Type the new URL

  Text Type: http://www.fakepage.com/editjob.php?jobnumber=%T1%

 

  // Use Wait for Web Page if IE is running

  If Variable %T50% contains "Microsoft Internet Explorer"

    Wait for Web Page: "http://www.fakepage.com/editjob.php?jobnumber=%T1%"

  Else

    // Otherwise delay

    // Or you could run a macro that performs the wait for web page command for the alternate browser

    Delay 2 Seconds

  End If

 

  // Do other macro commands to "perform a small task on that page"

 

ASCII File End Process

Note that this example determines what browser is running and tries to adapt to that browser. The Wait for Web Page command only works with IE. There are techniques you can use to perform a Wait for Web Page type function for other browsers using macro commands. Here are a few references:

 

Sample macro to wait for Web Page in Netscape:

www.macros.com/usermacs/umwaitwebpagen.htm

 

Forum discussions:

pgmacros.com/community/index.php?showtopic=451&hl=Wait+web+page

pgmacros.com/community/index.php?showtopic=360&hl=Wait+web+page

[mtbsteve]

Thanks again Kevin.

 

Only thing that I'm not 100% happy with this macro I'm creating, is how it waits for web pages. I have a screen where I click on a link, it performs an action, then returns to the same web address. As far as I know I can't use the wait for web page command because the browser is already there... Currently I have just set a delay which works ok with my bandwidth and the other website. However, it may not be suitable for its intended purpose. Without setting a 'very high' delay to cater for all, is there any other methods I could use so that I could keep it moving quickly? (The cursor type is one that won't work either.)

[kevin]

You might have success if you use the Wait for Web Page command and leave the url blank. This will tell your macro to wait until IE is no longer busy, but it does not care which web address was loaded.

 

If that does not work you could use the Get Pixel Color in a macro to determine when the web page has finshed loading when the 'progress bar' in the status line indicates the page has fully loaded.

 

Here is a discussion in this forum about using Get Pixel Color command: http://pgmacros.com/community/index.php?sh...26&hl=Get+Pixel.

 

And here is an example of this technique used to implement Wait for Web Page in Netscape: http://www.macros.com/usermacs/umwaitwebpagen.htm. This could be adapted for use in IE, Opera or Firefox.

[randallc]

Hi,

The "amateur" addon, MEBasic, now accesses Excel directly by simple "ExcelReadCell$".

This also means formulas can be used in excel, and high precision maths, loan calculations, etc available in excel are available in addon ME3 (without actually running excel).

(eg power(7.1,-3.1) etc, including cube etc roots)

Best, Randall