Place variable using TEXT TYPE

[Amerifax-Bob]

I am trying to copy a few cells (A,B,C) from Excel. I am trying to copy them into Variables (one each). To test this out, I opened up Notepad and Pasted the Variables into there. Each time I run the Macro, It is only pasting the Value from Variable 1 (%T[1]%).

 

Basically in A,B,C I have First Name, Middle Initial, Last Name;

I go into A, copy, "Variable Set String T[1] from the clipboard, then I tab and do the same for B-T[2] and C-T[3].

 

When I try to paste I am only getting the value from T[1]:

<TEXT TYPE Action="0" Text="%T[1]%<ARROW RIGHT>%T[2]%<ARROW RIGHT>%T[3]%"/>

 

I have tried using only one Variable and pasting, but the only one that works (even alone) is T[1].

 

Do you know what I might be doing wrong?

 

Heather (for Bob)

[Cory]

I keep getting you guys to process the text instead of doing it this problematic way but some horse just won't drink

 

My guess is that you second and third copies are happening too fast for the clipboard. Try adding delays of at least 200mS between each operation.

 

Might I also suggest you copy all three cells at once and use the Split String command on a tab character to bust them into a 3 element array. If you are interested I can give you a quick example. Might help you start to see the beauty of processing the text in variables instead of inching thru Excel with all it's timing pitfalls.

[Amerifax-Bob]

I keep getting you guys to process the text instead of doing it this problematic way but some horse just won't drink

 

My guess is that you second and third copies are happening too fast for the clipboard. Try adding delays of at least 200mS between each operation.

 

Might I also suggest you copy all three cells at once and use the Split String command on a tab character to bust them into a 3 element array. If you are interested I can give you a quick example. Might help you start to see the beauty of processing the text in variables instead of inching thru Excel with all it's timing pitfalls.

 

Why do I will I'm getting a slap on the wrist? This horse is still looking for the water.

 

Yes I sure am open to a better way for the mouse trap. I keep on getting my finger cought in what used to work.

 

Bob

[stevecasper]

Why do I will I'm getting a slap on the wrist? This horse is still looking for the water.

 

Yes I sure am open to a better way for the mouse trap. I keep on getting my finger cought in what used to work.

 

Bob

I think part of what Cory is getting at is that this particular issue has been dealt with over and over ad nauseum on both the ME3 boards as well as ME Pro.

 

Frankly, Excel is a bear to work with directly. It is generally easier to do like Cory suggests and copy the entire range of cells you're trying to manipulate and utilize functions like Text File Process and ASCII File Process commands (or, as he actually suggested, the Split String command).

[Amerifax-Bob]

I think part of what Cory is getting at is that this particular issue has been dealt with over and over ad nauseum on both the ME3 boards as well as ME Pro.

 

Frankly, Excel is a bear to work with directly. It is generally easier to do like Cory suggests and copy the entire range of cells you're trying to manipulate and utilize functions like Text File Process and ASCII File Process commands (or, as he actually suggested, the Split String command).

 

Copy the range is something I never thought of doing. So the general idea that we are talking about is removing the values from Excel as a range than do the manipulation. I would emagine removing the range would be the same as when I do a cell by puting the range into a variable.

 

In the past going to a program and using the arrow key to go from cell to cell and doing a variable on each one seemed so easy. But things change.

 

Bob

[stevecasper]

Copy the range is something I never thought of doing. So the general idea that we are talking about is removing the values from Excel as a range than do the manipulation. I would emagine removing the range would be the same as when I do a cell by puting the range into a variable.

 

In the past going to a program and using the arrow key to go from cell to cell and doing a variable on each one seemed so easy. But things change.

 

Bob

I put the names "John Wilkes Booth" in cells A1, B1, and C1 respectively. I highlighted them all, then did a Ctrl+C copy. At that point I ran this macro:

 

Variable Set to ASCII Char 9 to %Tab% - I did this because Excel uses TABs when separating cells along a row. ASCII char 9 is TAB, so I assigned ASCII 9 to the variable %Tab%

Variable Set String %T[1]% from the clipboard contents

Split String "%T[1]%" on "%Tab%" into Names, starting at 1 - Here I use the variable %Tab% to tell the macro where to make the transition from one array index to the next

Text Box Display:

<VARIABLE SET TO ASCII CHAR Value="9" Destination="%Tab%"/>
<VARIABLE SET STRING Option="\x02" Destination="%T[1]%"/>
<SPLIT STRING Source="%T[1]%" SplitChar="%Tab%" Dest="Names" Index="1"/>
<TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 %Names[1]%\r\n\\par %Names[2]%\r\n\\par %Names[3]%\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="1" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

 

This appears to do the trick.