acantor

A few days ago, someone asked how to accomplish a task with Macro Express that involved Excel and a website. The scenario was a little unclear, so in separate posts, Cory and I encouraged the OP to provide details. So far, the OP hasn’t responded. (It’s not too late! )

Because the problem looked interesting, I decided to invent my own details, and try to script a solution:

An Excel worksheet stores personal data in Row 1: an honorific in A1, first name in B1, middle initial in C1, surname in D1, and so on:

The challenge: Write a Macro Express script to copy values from ten cells, A1 to J1, and insert the values into the ten adjacent fields on the RoboForm web page:

The value from A1 goes in the “Title” field, the value from B1 goes in the “First Name” field, etc.

Here’s the URL to the RoboForm web page:

RoboForm Test - Fill in the fields

I'm not sure what RoboForm is, but I suggest your solution use fake data -- not your personal information -- and that you don't complete the form. Just use the page for test purposes. Feel free to use any other web page you find that consists of a series of form fields.
 

My first attempt to script a solution was horribly unreliable. Then I tried an entirely different method, which I eventually got to work, although it was clunky and failed occasionally. My latest macro appears to be reliable, but I’m not sure it’s the best solution.

How would you script this macro? I’ll post my solution in a couple of days.

Please provide details about the steps you want to automate. For examples: How many rows, or until what condition is met? What data do you want to extract from Excel? Are you wanting to add or delete data in Excel? What website?

Quote

I am trying to write a macro to make the first letter of the last word typed uppercase.

In Word documents, Outlook messages that you're composing or responding to, and PowerPoint slides, you can capitalize the first letter of the most-recently typed word by pressing Shift + F3. No need to select the word. And no need for the cursor to be at the right boundary of the word -- the cursor can be on any letter of the word. Furthermore, pressing the hotkey does NOT change the position of the cursor.

You may have to press Shift + F3 twice; the number of times depends on the initial capitalization state of the word when you start, e.g., "HELLO" vs. "hello".

So although you may not need a macro to accomplish this, writing a macro might be more fun!

PS: For this to work, the function keys on your keyboard must be mapped as function keys, not as media keys.

If the function keys are mapped as media keys, you will need to add the Fn key: Fn + Shift + F3... but that requires too much finger gymnastics for my liking!

I've inadvertently added an invisible character to "TEXT TYPE" more times than I'm comfortable admitting!

Your script is not working because of this line:

<TEXT TYPE Action="0" Text="<SHIFT><ARROW RIGHT> " _COMMENT="Select first character"/>

There's an extra space after Shift + Right, which deselects the selected character, which means there's nothing to cut.

I've used the Clipboard Cut command in Windows 10 without issue. However, my preference is to use Text Type <CTRL>x followed by a very short delay, e.g., 50 or 100 ms.

What happens if you substitute Ctrl + x?

Looks fine to me. But maybe increase the length of the delay in Step 9.

To debug, I would add this before Step 10 to ensure the value of the variable is what you think it should be:

Text Box Display: Test value: [%CLR%]

It might be helpful to post your actual script rather than a description of it.

You may not need to send F2 to the cell. It may be OK to send Ctrl + C to the focused cell, without the need to edit the cell and select its contents.

Macro Express has an "Email Send" command, which would eliminate the need to launch Teams or another program.

I've never used this feature, so tried it just now and couldn't make it work.

Perhaps if you know about MAPI, and about SMTP settings, perhaps you will have more success.

Hi Terry,

Are you referring to the "Start Debugger in a Different Window" screen?

There are debug settings: Options > Preferences... > General > Debug Tools

But I don't see how the settings can affect the appearance (or non-appearance) of the debug window. Maybe try the "Reset Page" button on that screen.

I wonder whether the issue might resolve itself by closing the MEP Explorer and restarting the computer...

Quote

Close enough for gummint work. 

Agreed.

Adding a handful of extra tests would weed out some invalid addresses. But then the macro would be a lot longer than 19 lines!

Here are examples of simple tests to filter out invalid email addresses:

If the length < 6...  [the shortest possible email must contain at least five characters: x@x.x]

If the number of at-signs is not equal to 1... [I think there can only be one @]

If the first character is an at-sign...

If the last character is an at-sign...

If the number of periods is zero... [I don't think an email address can live without a period]

etc.

Tests like these will filter out duds, but for me, it would not be worth the effort because my source documents normally contain only valid email addresses.

BTW, I use the macro regularly. A client communicates to me via a quirky web-based email application. When using this application, it's difficult to select and copy email addresses from messages via keyboard or mouse, which I need to paste into Outlook. So I select the entire page (Ctrl + A), copy (Ctrl + C), and trigger the macro. It takes the macro a second or two to chew through the text and deliver a list of email addresses.

When I set this challenge, I didn't anticipate the issues that folks have raised. I've found these discussions interesting and illuminating.

I acknowledge that RegEx is the way to go if the goal is to extract valid email addresses only. On the other hand, here is the original background to the challenge:

Quote

I often need to extract email addresses that appear in documents, spreadsheets, email messages, and webpages. I used to do this manually, but recently, I realized I should be using a macro to do the heavy lifting... at least most of the heavy lifting.

When I wrote the above, I was thinking about validity mostly in terms of deciding whether a string has the appearance of an email address. So my code does two things: it checks whether a string contains valid email address characters:

abcdefghijklmnopqrstuvwxyz1234567890-_@.

Then the code checks whether a string contains the at-sign. If yes, I assume the string is an email address.

Because of these discussions, I realize my solution is not a good general solution. My code extracts invalid email addresses like these:

@hello

hello@

...@...

.com@hello

hello@.com

hello@hello@hello.com

But for the specific challenge, I think my simple test is adequate. That's because the email addresses contained in my documents, spreadsheets, and email addresses are valid -- they are email addresses I already use! But had the challenge been to harvest email addresses gathered from the wild, my solution is meh!

I didn't remember that setting. My more "brute-force" method works, but I would prefer to use nested variables. Just an aesthetic choice.

Something I find irritating about all of the script samples I've submitted in this thread: The variables get assigned every single time the macro runs. I would rather they get assigned once.

If I were doing this again, I would use two scripts: the first script to load the variables into memory. You'd only need to trigger it once, at the start of a session. The variables would persist while Macro Express is running.

The second script outputs the variables... and that's all it does, other than iterate the counter.

Because of the complexities of nested variables in Version 3, you could do this instead:

If Variable %N1% = 1

   Text Type: Test 1

End If

If Variable %N1% = 2

   Text Type: Test 2

End If

[etcetra]

If Variable %N1% = 0

OR

If Variable %N1% = 4

   Text Type: Test 1

End If

I think you're very close.

The first time this macro is run, the value of %N1% is probably 0. Therefore, the macro types the value of %T0%, which has no value.

Add these lines after the lines that define T1, T2, etc.:

If Variable %N1% = 0

   Variable Set Integer %N1% to 1

End If

I'm not sure the Text Type instruction is right. If it isn't, try this:

Text Type: %T[%N1%]%

Even if someone on the forum were to create a Macro Express Pro script that does what you want, you won't be able to run the script in Macro Express.

That's because the underlying code in the two versions is different. Fortunately, Macro Express Pro converts Macro Express scripts, but unfortunately, the opposite isn't true: Macro Express cannot convert Macro Express Pro scripts. 

So unless you update to the Pro edition, you must start from scratch. If you post your code of your non-working script, some of us may be able to spot what's wrong.

Cory's suggestion is spot-on: Start simple. Try making a script that works with two (or three) texts instead of eight.

Quote

Also this shoudl be posted in the appropriate forum where acantor and others who use ME can help you better. 

Although the discussion on this forum has, for years, focused almost exclusively on Macro Express Pro, the forum continues to be called "Macro Express and Macro Express Pro." So this forum is appropriate for questions about the older product. The problem is that most of the "regulars" switched to the Pro version eons ago. We may need our memories refreshed!

Share your code... maybe we can help sort out what's not working. My recollection is that square brackets are integral to Version 3 variable names: %N[1]% rather than %N1%.

Nested variable names in Version 3 were very messy... %T[%N[1]%]% or something like that. It can be done, but it's much easier with the modern version.

It's been so long since I used Macro Express 3 that I don't recall if all of the features of later versions are included. For example:

Variable Restore: Restore Integer Variables

This "Restore" instruction at the start of the script, and the corresponding "Save" command at the end, may or may not be available in Version 3. 

Macro Express 6 will convert Version 3 scripts to Version 6. But you can't go in the opposite direction.

When a script uses variables (as my script does), Version 3 is MUCH less convenient. The naming convention for variables in Version 3 is very constrained: %N[1]% in Version 3 can be %Counter% or %Value%... in other words, you can use easy-to-understand variable names in Version 6.

I second Cory's suggestion: you'll learn more by working through it yourself. My script consists of fewer than 20 lines of code, so building your own version shouldn't take too long!

Another reason to start from scratch: my script probably doesn't do exactly what you need it to do. Maybe you want ten different texts instead of three. Maybe you don't need to select outputted texts.

And another reason: Scripts can be "fragile." They sometimes fail when least expected. I learned recently that my script fails when run in certain editors. You'll be better prepared to deal with "edge" conditions, which are almost inevitable, if you build on an example rather than import an example.

And a final reason: If I were writing this script today, I wouldn't use the same method. I'm fairly certain I would end up with code that's easier to understand, more efficient, and possibly easier to maintain.

Please share your version!

Some font sizes are fixed, and can't be changed:

If you need certain fonts to be larger or smaller, consider submitting a feature request.

Variable Restore: Restore Integer Variables
 
// Here are the texts to display
Variable Set String %Text1% to "This is a test."
Variable Set String %Text2% to "This is a second test."
Variable Set String %Text3% to "This is a third test."
 
// There are only three texts, so adjust %Count% when it is out of range
If Variable %Count% Equals "0"
  Variable Set Integer %Count% to 1
End If
 
If Variable %Count% Equals "4"
  Variable Set Integer %Count% to 1
End If
 
// Get the text length so the text can be selected
Variable Set Integer %LenText1% to the length of variable %Text1%
Variable Set Integer %LenText2% to the length of variable %Text2%
Variable Set Integer %LenText3% to the length of variable %Text3%
 
// Type out a text...
Text Type (Simulate Keystrokes): %Text%Count%%
 
// ...and immediately select it via Shift + Left
Repeat Start (Repeat %LenText%Count%% times)
  Text Type (Simulate Keystrokes): <SHIFT><ARROW LEFT>
End Repeat
 
// Increment %Count%
Variable Modify Integer %Count%: Increment
 
// Preserve Integer variable (but won't survive a reboot)
Variable Save: Save Integer Variables


<VARIABLE RESTORE Option="\x02"/>
<COMMENT/>
<COMMENT Value="Here are the texts to display"/>
<VARIABLE SET STRING Option="\x00" Destination="%Text1%" Value="This is a test." NoEmbeddedVars="FALSE"/>
<VARIABLE SET STRING Option="\x00" Destination="%Text2%" Value="This is a second test." NoEmbeddedVars="FALSE"/>
<VARIABLE SET STRING Option="\x00" Destination="%Text3%" Value="This is a third test." NoEmbeddedVars="FALSE"/>
<COMMENT/>
<COMMENT Value="There are only three texts, so adjust %Count% when it is out of range"/>
<IF VARIABLE Variable="%Count%" Condition="\x00" Value="0" IgnoreCase="FALSE"/>
<VARIABLE SET INTEGER Option="\x00" Destination="%Count%" Value="1"/>
<END IF/>
<COMMENT/>
<IF VARIABLE Variable="%Count%" Condition="\x00" Value="4" IgnoreCase="FALSE"/>
<VARIABLE SET INTEGER Option="\x00" Destination="%Count%" Value="1"/>
<END IF/>
<COMMENT/>
<COMMENT Value="Get the text length so the text can be selected"/>
<VARIABLE SET INTEGER Option="\x0D" Destination="%LenText1%" Text_Variable="%Text1%"/>
<VARIABLE SET INTEGER Option="\x0D" Destination="%LenText2%" Text_Variable="%Text2%"/>
<VARIABLE SET INTEGER Option="\x0D" Destination="%LenText3%" Text_Variable="%Text3%"/>
<COMMENT/>
<COMMENT Value="Type out a text..."/>
<TEXT TYPE Action="0" Text="%Text%Count%%"/>
<COMMENT/>
<COMMENT Value="...and immediately select it via Shift + Left"/>
<REPEAT START Start="1" Step="1" Count="%LenText%Count%%" Save="FALSE"/>
<TEXT TYPE Action="0" Text="<SHIFT><ARROW LEFT>"/>
<END REPEAT/>
<COMMENT/>
<COMMENT Value="Increment %Count%"/>
<VARIABLE MODIFY INTEGER Option="\x07" Destination="%Count%"/>
<COMMENT/>
<COMMENT Value="Preserve Integer variable (but won't survive a reboot)"/>
<VARIABLE SAVE Option="\x02"/>

I think I've scripted macros that do something like that. I'll check and report back.

If you haven't seen this screen while scripting the macro, then you haven't defined the variable as an array:

Macro Express must be installed on the hard drive to work. In contrast, Macro Express Portable does not need to be installed on the hard drive.

Macro Express Portable runs from a portable drive connected to a PC, e.g., on a USB thumb drive.

When running Macro Express Portable, macro files and temporary files are stored on the portable drive. ME Portable leaves no trace on the hard drive of the PC.

I got the script down to 19 lines, although the logic may not be as easy to follow as before. (The former IF-THEN-ELSE is now IF-THEN.)

I tried to compensate for the reduction in transparency by reorganizing the script and revising some comments.

Variable Set String %ValidChars% to "abcdefghijklmnopqrstuvwxyz1234567890-_@." // Every valid character in an email address
 
Variable Set String %Clip% from the clipboard contents
Variable Set Integer %ClipLength% to the length of variable %Clip%
 
Repeat Start (Repeat %ClipLength% times) // Parse input, one character at a time
  Variable Modify String: Copy part of text in %Clip% starting at %Count% and 1 characters long to %Char%
  If Variable %ValidChars% Does not Contain "%Char%" // %Char% is NOT valid in an email address
    Variable Set String %Char% to "*" // Substitute * for the invalid character
    Variable Modify Integer %StarCount%: Increment // Keep track of the number of invalid characters
  End If
  Variable Set String %Result% to "%Result%%Char%" // Append %Char% to %Result%
End Repeat
 
Split String "%Result%" on "*" into %PossibleEmail%, starting at 1
 
Variable Modify Integer: %StarCount% = %StarCount% + 1 // Number of possible email addresses to check for "@"
 
Repeat Start (Repeat %StarCount% times)
  If Variable %PossibleEmail[%Count%]% Contains "@" // Assume an "@" means a string is part of an email address
    Variable Set String %EmailList% to "%EmailList%
%PossibleEmail[%Count%]%" // Create a list of email addresses
  End If
End Repeat
 
Text Box Display: Scraped Email Addresses

<VARIABLE SET STRING Option="\x00" Destination="%ValidChars%" Value="abcdefghijklmnopqrstuvwxyz1234567890-_@." NoEmbeddedVars="FALSE" _COMMENT="Every valid character in an email address"/>
<COMMENT/>
<VARIABLE SET STRING Option="\x02" Destination="%Clip%" NoEmbeddedVars="FALSE"/>
<VARIABLE SET INTEGER Option="\x0D" Destination="%ClipLength%" Text_Variable="%Clip%"/>
<COMMENT/>
<REPEAT START Start="1" Step="1" Count="%ClipLength%" Save="TRUE" Variable="%Count%" _COMMENT="Parse input, one character at a time"/>
<VARIABLE MODIFY STRING Option="\x09" Destination="%Char%" Variable="%Clip%" Start="%Count%" Count="1" NoEmbeddedVars="FALSE"/>
<IF VARIABLE Variable="%ValidChars%" Condition="\x07" Value="%Char%" IgnoreCase="TRUE" _COMMENT="%Char% is NOT valid in an email address"/>
<VARIABLE SET STRING Option="\x00" Destination="%Char%" Value="*" NoEmbeddedVars="FALSE" _COMMENT="Substitute * for the invalid character"/>
<VARIABLE MODIFY INTEGER Option="\x07" Destination="%StarCount%" _COMMENT="Keep track of the number of invalid characters"/>
<END IF/>
<VARIABLE SET STRING Option="\x00" Destination="%Result%" Value="%Result%%Char%" NoEmbeddedVars="FALSE" _COMMENT="Append %Char% to %Result%"/>
<END REPEAT/>
<COMMENT/>
<SPLIT STRING Source="%Result%" SplitChar="*" Dest="%PossibleEmail%" Index="1"/>
<COMMENT/>
<VARIABLE MODIFY INTEGER Option="\x00" Destination="%StarCount%" Value1="%StarCount%" Value2="1" _COMMENT="Number of possible email addresses to check for \"@\""/>
<COMMENT/>
<REPEAT START Start="1" Step="1" Count="%StarCount%" Save="TRUE" Variable="%Count%"/>
<IF VARIABLE Variable="%PossibleEmail[%Count%]%" Condition="\x06" Value="@" IgnoreCase="FALSE" _COMMENT="Assume an \"@\" means a string is part of an email address"/>
<VARIABLE SET STRING Option="\x00" Destination="%EmailList%" Value="%EmailList%\r\n%PossibleEmail[%Count%]%" NoEmbeddedVars="FALSE" _COMMENT="Create a list of email addresses"/>
<END IF/>
<END REPEAT/>
<COMMENT/>
<TEXT BOX DISPLAY Title="Scraped Email Addresses" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\lang4105\\f0\\fs20 %EmailList%\\lang1033\\f1\\fs14 \r\n\\par }\r\n" Left="645" Top="19" Width="664" Height="976" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>