Copy A Portion Of An Excel Cell & Repeat

[Rick D]

I have orderd ME Explained, but in the meantime, I am trying to separate items in an Excel cell into multiple cells. I started with a Word Table that had bullitted formatting and needed it into Excel. Due to all the formatting it would not move cleanly. I have it all moved correctly now except one column. Each cell in the column contains data as shown here:

 

*Document Users*Educate users and leadership on appropriate access controls and passwords*Place “Authorized Personnel Only” Sign on all entrances*Long Term*On going review of current procedures*Yearly review

 

What I would like to do is set a Macro to copy each bullit and all text until the next bullit, assign that to a variable and repeat the process until each string in the cell is assigned a variable. There could be a maximum of 10 bullited strings. I would then paste each variable to a specific cell within Excel.

 

So the finished product might look like this:

 

Cell AA1 - *Document Users

Cell AA2 - *Educate Users and leadership on appropriate access controls and passwords.

Cell AA3 - *Place "Authorized Personnel Only" Sign on all entrances

etc.....

 

I then need to repeat the whole process for each cell in the column that contains data.

 

I would then delete the original column and have another macro paste all of the info in the spreadsheet to appropriate places in an HTML form. I can do all of this except break the cell apart.

 

Any ideas???

 

Rick

[floyd]

Rick D -

 

There is an easy way to parse tokens from a string if you have the PGM Functions Library. If not, here is a bit of code that will do the job for you. The example uses the string you provided in your post. It simply extracts each bullet and places them in sequential variables. You will of course want to modify it to suit your situation; adding the code to write the the cells, and so forth.

 

// Set the first "holding" string variable to 19 so that we do not have to deal with leading
// zeros in the dynamic command. This means that we will use %T20% and up to hold the individual
// bullets extracted from the main string.
Variable Set String %T1% "*Document Users*Educate users and leadership on appropriate access controls and passwords*Place “Authorized Personnel Only” Sign on all entrances*Long Term*On going review of current procedures*Yearly review"
Variable Set Integer %N2% to 19

// Repeat extracting bullets until the main string contains nothing.
Repeat Until %N1% = 0
 
 // Replace the next occurrence of an asterisk with a tilde. It will always be the first
 // character in what remains of the main string.
 Replace "*" with "~" in %T1%
 
 // Get position of the next asterisk. If result is zero then we have reached the last bullet.
 Variable Set Integer %N1% from Position of Text in Variable %T1%
 If Variable %N1% > 0
   Variable Modify Integer: Dec (%N1%)
   Variable Modify String: Copy Part of %T1% to %T2%
 Else
   Variable Modify String: Copy %T1% to %T2%
 End If
 
 // Remove the current bullet from the main string and replace the tilde with an asterisk.
 Replace "%T2%" with "" in %T1%
 Replace "~" with "*" in %T2%
 
 // Increment the next "holding" variable and run the dynamic "Set Variable To" command
 // "<TMVAR2:09:20:02:000:000:>".
 Variable Modify Integer: Inc (%N2%)
 Variable Set String %T3% "<TMVAR2:09:%N2%:02:000:000:>"
 Run Macro in Variable %T3%
 
Repeat End

----------
<REM2:Set the first "holding" string variable to 19 so that we do not have to deal with leading><REM2:zeros in the dynamic command. This means that we will use %T20% and up to hold the individual><REM2:bullets extracted from the main string.><TVAR2:01:01:*Document Users*Educate users and leadership on appropriate access controls and passwords*Place “Authorized Personnel Only” Sign on all entrances*Long Term*On going review of current procedures*Yearly review><IVAR2:02:01:19><REM2:><REM2:Repeat extracting bullets until the main string contains nothing.><REP3:08:000001:000002:0001:0:01:0><REM2:><REM2:Replace the next occurrence of an asterisk with a tilde. It will always be the first><REM2:character in what remains of the main string.><TMVAR2:21:01:00:000:000:*~><REM2:><REM2:Get position of the next asterisk. If result is zero then we have reached the last bullet.><IVAR2:01:13:1:*><IFVAR2:2:01:4:0><NMVAR:09:01:0:0000001:0:0000000><TMVAR2:10:02:01:001:N01:><ELSE><TMVAR2:09:02:01:000:000:><ENDIF><REM2:><REM2:Remove the current bullet from the main string and replace the tilde with an asterisk.><TMVAR2:21:01:01:000:000:%T2%><TMVAR2:21:02:00:000:000:~*><REM2:><REM2:Increment the next "holding" variable and run the dynamic "Set Variable To" command><REM2:"<TMVAR2:09:20:02:000:000:>".><NMVAR:08:02:0:0000001:0:0000000><TVAR2:03:01:<TMVAR2:09:%N2%:02:000:000:>><RUNMACVAR:3><REM2:><ENDREP>

[randallc]

Hi,

Working with excel, sometimes its easier to put your cell into a string containing tabs and paste them into a row you select [Excel uses the tabs to separate them into separate cells], then manipulate the cells [in your case into columns] in Excel;

<REM2:Set up tab environment variable chr 9><ASCIIC:1:1:9><TMVAR2:19:01:00:000:000:TAB><TVAR2:01:01:*123*tyu*gjh*bnm*kklj><REM2:Replace each bullet with ["tab"and bullet]><TMVAR2:21:01:01:000:000:*%TAB%*>

// Set up tab environment variable chr 9

Variable Set %T1% to ASCII Char of 9

Variable Modify String: Save %T1% to Environment Variable

Variable Set String %T1% "*123*tyu*gjh*bnm*kklj"

// Replace each bullet with ["tab"and bullet]

Replace "*" with "%TAB%*" in %T1%

Activate or Launch: "Excel" OR "excel.exe"

Text Type: %T1%

Best, Randall

[Rick D]

Floyd,

 

Thanks for the help, works great. I hope to have my book soon (had to order via a contracted software vendor for our company) it has been over a week since it was shipped..... I have learned a lot though through reading this board as well as trial and error.

[randallc]

Hi Rick and Floyd,

Glad to hear Floyd's string split helped.

Incidentally, [and as a reminder to myself!] to extend my comment on parsing, for large strings where even the pgmacro function fills the registry with tokens, instead of "tab" in my example above , we could relace with "crlf", save string to text file, then text file process; I haven't seen this done but should be good for parsing large web pages etc too?

Best, Randall

[joe]

Hello Rick D!

 

Who did you order the book from? I can help trace it.

[MJB]

Hi Floyd,

 

I've been working with Rick on this Excel String Split and everything is working great, but could you explain this part of the code a little more:

// Increment the next "holding" variable and run the dynamic "Set Variable To" command
// "<TMVAR2:09:20:02:000:000:>".
Variable Modify Integer: Inc (%N2%)
Variable Set String %T3% "<TMVAR2:09:%N2%:02:000:000:>"
Run Macro in Variable %T3%

We've got all the tokens in the variables but now we'll need to get them back out. Can we add them to an array instead and then loop back through our array? We also downloaded the PGM Functions Library but as you can tell we are both Newbies when it comes to Macro Express, still waiting for our book to arrive.

 

Thanks for all your help!

 

MJB

 

P.S. I know that the code fragment above is assigning dynamic variables but what specifically is

"<TMVAR2:09:%N2%:02:000:000:>"

doing?

[MJB]

Hey Joe we ordered the book from Software Spectrum!

 

Thank you,

 

MJB

[floyd]

MJB -

 

The loop parses the next token each time through and places it into the %T2% string variable. Before looping again we need to save it to the next sequential holding variable, which starts at %T20%. To copy %T2% to %T20% you would normally use the following command in the Script Editor:

 

Variable Modify String: Copy %T2% to %T20%

 

which looks like this in the Direct Editor (its native code):

 

<TMVAR2:09:20:02:000:000:>

 

We are, however, needing to increment to the next holding variable. Manually it would be like this:

 

First time through the loop: Variable Modify String: Copy %T2% to %T20%

Next time through: Variable Modify String: Copy %T2% to %T21%

Next time: Variable Modify String: Copy %T2% to %T22%

... and so on

 

The problem is to programmatically increment the holding variable without having to use constructs like If/End If or Switch/End Switch. Thus, the Run Macro in Variable command. It takes a command string written as native code and runs it as if it were the actual command, which is what this sequence does:

 

Variable Modify Integer: Inc (%N2%)

Variable Set String %T3% "<TMVAR2:09:%N2%:02:000:000:>"

Run Macro in Variable %T3%

 

We increment %N2% each time through the loop (20, 21, 22, and so forth). Then we create a command string %T3% using the %N2% variable as a replacement for the normally static to position in the native command. The result is this:

 

First time through the loop: <TMVAR2:09:20:02:000:000:>

Next time through: <TMVAR2:09:21:02:000:000:>

Next time: <TMVAR2:09:22:02:000:000:>

... and so on

 

And the Run Macro in Variable command runs the command each time.

[joe]

MJB -

Hey Joe we ordered the book from Software Spectrum!

I can set you up with a PDF file until the book arrives. Let me know if you are interested.

[MJB]

Thank you Floyd! Great explaination! The native code threw me off. So knowing this I should now be able to just do the reverse to get the values back out of the variables:

 

//Pseudo Code:
Repeat %N3% Times


Variable Modify Integer: Inc (%N2%)
Variable Set String %T3% "Text Type:<TMVAR2:09:%N2%:02:000:000:>"
Run Macro in Variable %T3%

End Repeat

// I know this isn't right but is the idea at least right?

 

Thanks again!

 

MJB

[MJB]

Joe,

 

Yes please ! That would be great! We've been waiting on the book for two weeks so we've been having to learn a lot by trial and error, well at least until we found this great forum.

 

Thank you!

 

MJB

[joe]

Hello MJB!

 

Yes please! That would be great!

Two weeks seems like an awfully long time. Send to me joe@pgmacros.com your email address and I will get you set up.

[floyd]

MJB -

 

We've got all the tokens in the variables but now we'll need to get them back out.

Can we add them to an array instead and then loop back through our array?

Macro Express 3.x lacks variable arrays.

 

//Pseudo Code:

Repeat %N3% Times

  Variable Modify Integer: Inc (%N2%)

  Variable Set String %T3% "Text Type:<TMVAR2:09:%N2%:02:000:000:>"

  Run Macro in Variable %T3%

End Repeat

 

I know this isn't right but is the idea at least right?

Yes, you are on the right track. The code below assumes that Excel has focus, the active cell is the first target celll, and the <Enter> key moves to the next row. It loops by incrementing the holding variable each time until it finds an empty one.

 

Variable Set Integer %N2% to 19
Repeat Until %T2% = ""
 Variable Modify Integer: Inc (%N2%)
 Variable Set String %T3% "<TMVAR2:09:02:%N2%:000:000:>"
 Run Macro in Variable %T3%
 If Variable %T2% > ""
   Text Type: %T2%<ENTER>
 End If
Repeat End

--------------------
<IVAR2:02:01:19><REP3:08:000001:000001:0002:0:01:><NMVAR:08:02:0:0000001:0:0000000><TVAR2:03:01:<TMVAR2:09:02:%N2%:000:000:>><RUNMACVAR:3><IFVAR2:1:02:4:><TEXTTYPE:%T2%<ENTER>><ENDIF><ENDREP>

[randallc]

hi,

Maybe my new macro will help you;

puts the text file lines into a column in excel; though you need to use the "Wizard"

Best, Randall

Excel function for Wizard

[MJB]

Thanks for the help Floyd! I'll give it a try. While I was waiting for a reply I went back and took out the Dynamic Variables Code and just performed all the Excel Functions within that same loop just using %T2% over and over... It seemed to work but still having problems with some other parts of the code unrelated to this thread. But thanks for all the help we really appreciate it!

 

Joe, I sent you an email, Thank you.

 

 

Thanks all,

 

MJB