Inconsistent copying of Excel cell data to ME variables

[mmezera]

I have successfully created multiple macros over the last few years that copy data from an excel spreadsheet for posting into a AS400 green screen.  A problem has arisen with one of the macros where Clipboard copy is not reliably copying contents to the designated variable. This only happens on some computers while others are unaffected. It is not always the same cell that fails. I have used to Wait command after all TEXT TYPE commands to assure that the macro doesn't get ahead of the text being typed. There are roughly 17 variables for each row. Those variables are copied and entered into the AS400 screen to create a sales order and ship release (I have no problem with this side of the macro). Each variable value is trimmed to accommodate AS400 entry. When one line is done, the macro repeats for the next row in the spreadsheet.

 

Any ideas as to what is happening? I am using ME5. Changing macro speed does not solve the problem.

[Samrae]

Sometimes the clipboard commands are temperamental. I think this is due to Windows and not necessarily caused by Macro Express. Have you tried adding a separate delay after the clipboard copy command?

 

Sometimes I do this:

Clipboard Empty
Clipboard Copy
Variable Set String %ClipboardContent% from the clipboard contents
 
// If %ClipboardContent% is empty then try again
If Variable %ClipboardContent% Equals ""
  Delay: 300 milliseconds
  Clipboard Copy
  Delay: 300 milliseconds
  Variable Set String %ClipboardContent% from the clipboard contents
End If

 

[rberq]

Samrae's suggestion is excellent, but the IF / END IF section should perhaps be enclosed in a REPEAT loop. 

 

Another idea you might combine with that, is to use keystrokes to highlight the entire spreadsheet row, do a single Clipboard Copy of the entire row, then parse the captured cells into individual variables.  That might be a bit faster and more reliable than copying individual cells to the clipboard, but you would still be wise to include Samrae's technique for the single copy command.

 

Incidentally, I have long used a separate macro called "Generic Copy to Clipboard" that I call from many other macros, and my generic copy uses a variation of Samrae's method.  The advantage of this technique is that the ME "Preferences" for a wait after clipboard commands can be set to zero.  A question in my mind was whether the clipboard might not be empty, but might be only partway filled.  In other words, might the macro timing be such that the clipboard contains (for example) only the first 4,000 bytes of 7,000 that eventually will be copied -- so the macro "thinks" it is done when it still needs more time.  In a word, this has never happened even during stress testing.  The clipboard copy is always all or nothing.  (But I suppose that Microsoft could "fix" this in a future release.😄)

[Cory]

Samrae's comment I agree with. I have great luck with many and then some machines, like my main workstation, have problems with the clipboard and it's not limited to my MEP macros and even my own .NET programs have issues. Clearing and checking is a good idea. 

 

I don't know how big a data set you're doing but when I was doing batches just like you, except vt100, I would export the worksheet to a file and use the ACSII File Process command in MEP. Much more reliable, but not really worth it for a few cells. 

[acantor]

I agree with all the earlier comments and suggestions, and especially rberq's idea of selecting an entire row, copying it, assigning the clipboard to a variable, and parsing it.

 

To this end, here are Excel keyboard shortcuts that might be helpful:

 

Shift + Right Arrow = select cell to the right.

Shift + Right Arrow repeated = repeatedly select cells to the right.

Shift + Ctrl + Right Arrow = select a range of cells to the right, until a blank cell is encountered or the end of the row.

Shift + Space Bar = select the entire row.

[acantor]

Here's a script that selects a range of cells along a row, copies the range to the clipboard, assigns the clipboard to a variable, splits the values into an array, and then displays the first ten cell values.

 

For testing, I manually placed the cursor in the leftmost cell of the range. But the cell shouldn't be in Edit mode.

 

Initially, I had delays after the steps. But with testing, the macro seemed to work without delays.

 

// Define Tab character
Variable Set to ASCII Char 9 to %Tab%
 
// Select right to the first blank cell. Copy it.
Text Type (Simulate Keystrokes): <CONTROL><SHIFT><ARROW RIGHT>
Text Type (Simulate Keystrokes): <CONTROL>c
 
// Assign clipboard to variable %Clip%. Split %Clip% at tabs into array %Results%
Variable Set String %Clip% from the clipboard contents
Split String "%Clip%" on "%Tab%" into %Results%, starting at 1
 
// Display first ten cells
Repeat Start (Repeat 10 times)
  Text Box Display: Cell %Count%
End Repeat

<COMMENT Value="Define Tab character"/>
<VARIABLE SET TO ASCII CHAR Value="9" Destination="%Tab%"/>
<COMMENT/>
<COMMENT Value="Select right to the first blank cell. Copy it."/>
<TEXT TYPE Action="0" Text="<CONTROL><SHIFT><ARROW RIGHT>"/>
<TEXT TYPE Action="0" Text="<CONTROL>c"/>
<COMMENT/>
<COMMENT Value="Assign clipboard to variable %Clip%. Split %Clip% at tabs into array %Results%"/>
<VARIABLE SET STRING Option="\x02" Destination="%Clip%" NoEmbeddedVars="FALSE"/>
<SPLIT STRING Source="%Clip%" SplitChar="%Tab%" Dest="%Results%" Index="1"/>
<COMMENT/>
<COMMENT Value="Display first ten cells"/>
<REPEAT START Start="1" Step="1" Count="10" Save="TRUE" Variable="%Count%"/>
<TEXT BOX DISPLAY Title="Cell %Count%" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs32 [%Results[%Count%]%]\\fs14 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<END REPEAT/>

 

[mmezera]

I was going to use the Spit String command until I found out that it isn't available in ME5. I don't know if the client will want to upgrade. I will pursue that if I continue to have problems.

[rberq]

1 hour ago, mmezera said:

I was going to use the Spit String command until I found out that it isn't available in ME5

 

If you export the spreadsheet to a tab-delimited-txt file, or to a comma-delimited-csv, per Cory's suggestion, then the ASCII File Begin Process command can split the string for you. 

 

Or, per acantor's method, it's not too hard to write a Repeat loop that does the same thing as the Split String command. 

[Cory]

In the old days I wrote my own sub-macro for splitting. It's not difficult. 

http://bluepointdesign.com/macros/CRLFandTAB.aspx

http://bluepointdesign.com/macros/HexEdit/Default.aspx

http://bluepointdesign.com/macros/TabularData/Default.aspx

If you're interested. 

[acantor]

I modified my script to "manually" split the string at Tab characters. This probably is not the most efficient or elegant way to split a string, but it seems to work... so far!

 

// Define Tab character
Variable Set to ASCII Char 9 to %Tab%
 
// Cancel edit mode. Go to start of row. Select right to the first blank cell. Copy it.
Text Type (Simulate Keystrokes): <ESC><HOME>
Text Type (Simulate Keystrokes): <CONTROL><SHIFT><ARROW RIGHT>
Text Type (Simulate Keystrokes): <CONTROL>c
 
// Assign clipboard to variable %Clip%.
Variable Set String %Clip% from the clipboard contents
Variable Set String %Clip% to "%Clip%%Tab%" // The last cell in a range does not end in a Tab. So add one!
 
// "Manually" split %Clip% at tabs into array %Results%
Variable Set Integer %Count% to 1
Repeat Until %Clip% Does not Contain "%Tab%"
  Variable Set Integer %CharCount% to the position of "%Tab%" in %Clip%
  Variable Modify String: Copy part of text in %Clip% starting at 1 and %CharCount% characters long to %Results[%Count%]%
  Variable Modify String %Results[%Count%]%: Trim
   
  Text Box Display: Checking results...
  Variable Modify String: Delete part of text from %Clip% starting at 1 and %CharCount% characters long
  Variable Modify Integer %Count%: Increment
End Repeat

<COMMENT Value="Define Tab character"/>
<VARIABLE SET TO ASCII CHAR Value="9" Destination="%Tab%"/>
<COMMENT/>
<COMMENT Value="Cancel edit mode. Go to start of row. Select right to the first blank cell. Copy it."/>
<TEXT TYPE Action="0" Text="<ESC><HOME>"/>
<TEXT TYPE Action="0" Text="<CONTROL><SHIFT><ARROW RIGHT>"/>
<TEXT TYPE Action="0" Text="<CONTROL>c"/>
<COMMENT/>
<COMMENT Value="Assign clipboard to variable %Clip%."/>
<VARIABLE SET STRING Option="\x02" Destination="%Clip%" NoEmbeddedVars="FALSE"/>
<VARIABLE SET STRING Option="\x00" Destination="%Clip%" Value="%Clip%%Tab%" NoEmbeddedVars="FALSE" _COMMENT="The last cell in a range does not end in a Tab. So add one!"/>
<COMMENT/>
<COMMENT Value="\"Manually\" split %Clip% at tabs into array %Results%"/>
<VARIABLE SET INTEGER Option="\x00" Destination="%Count%" Value="1"/>
<REPEAT UNTIL Variable="%Clip%" Condition="\x07" Value="%Tab%"/>
<VARIABLE SET INTEGER Option="\x0E" Destination="%CharCount%" Text_Variable="%Clip%" Text="%Tab%" Ignore_Case="FALSE"/>
<VARIABLE MODIFY STRING Option="\x09" Destination="%Results[%Count%]%" Variable="%Clip%" Start="1" Count="%CharCount%" NoEmbeddedVars="FALSE"/>
<VARIABLE MODIFY STRING Option="\x00" Destination="%Results[%Count%]%"/>
<COMMENT/>
<TEXT BOX DISPLAY Title="Checking results..." Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\lang4105\\f0\\fs20 Clip            = [%Clip%]\r\n\\par CharCount  = [%CharCount%]\r\n\\par Count         = [%Count%]\r\n\\par Results       = [%Results[%Count%]%]\\lang1033\\f1\\fs14 \r\n\\par }\r\n" Left="821" Top="432" Width="970" Height="449" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%Clip%" Start="1" Count="%CharCount%"/>
<VARIABLE MODIFY INTEGER Option="\x07" Destination="%Count%"/>
<END REPEAT/>

 

[rberq]

7 hours ago, acantor said:

This probably is not the most efficient or elegant way to split a string

 

Looks pretty elegant to me.

[mmezera]

Thanks for the input. I am starting with adding in the delays after the clipboard copy commands and adding in the IF loops if the info isn't copied. I tested this with the client this morning and it appears to have solved the problem. This gives them a workable macro while I explore more elegant ways that have been suggested.

[Cory]

I was just thinking about the suggestion of copying and parsing a row. I've never done that but I do something similar. I use F5 to highlight a range of cells then copy. Then I would use the newline characters and tabs to parse it out. 
Also have named ranges and table in Excel helps.

[mmezera]

That sounds similar to what acantor proposed with his code. now that I have an interim solution I am going to work on copy and parse.

[Cory]

Also know you can link to delimited text files in Excel. The macro can do things there and instead of moving around in Excel and posting, you just refresh the query and "BAM!" there it all is. Often you have to reference the data in the right spot however.

Also with the F5 Go To you can paste ranges you build in MEP with tabs and newline sets. 

[mmezera]

Thanks for the input.

[Cory]

You're welcome

[acantor]

Another way to select a range of cells is to move to the first cell. Press F8 to turn on "Select" mode. Then use Right, Left, Up, and Down to select cells one at a time. In pseudo-code:

 

// Go to cell B12

{F5}

// Slight pause

B12{Enter}

// Slight pause

// Select right four cells, and select down four cells

{F8}

{Right}{Right}{Right}{Down}{Down}{Down}   // Number of repeats = the number of cells minus one

// Copy the range

{Ctrl}c