Clearing Out a Variable

[Amerifax-Bob]

I am running a macro that is splitting up the clipboard contents into variables by %Line[]%. When the Macro gets down to the bottom, the lines may or may not exist. I seem to be hitting a problem around %Line[19]%:

 

  If Variable %Line[15]% Contains "Web"
   Text Type (Simulate Keystrokes): <TAB>%Line[15]%
   Text Type (Simulate Keystrokes): <TAB>%Line[18]%
   Text Type (Simulate Keystrokes): <TAB>%Line[19]%
   Text Type (Simulate Keystrokes): <TAB>%Line[20]%
   Text Type (Simulate Keystrokes): <TAB>%Line[21]%
   Text Type (Simulate Keystrokes): <TAB>%Line[22]%
   Text Type (Simulate Keystrokes): <TAB>%Line[23]%<ARROW DOWN><HOME>
   Delay: 95 milliseconds
 Else
   Text Type (Simulate Keystrokes): <TAB>
   Text Type (Simulate Keystrokes): <TAB>%Line[17]%
   Text Type (Simulate Keystrokes): <TAB>%Line[18]%
   Text Type (Simulate Keystrokes): <TAB>%Line[19]%
   Text Type (Simulate Keystrokes): <TAB>%Line[20]%
   Text Type (Simulate Keystrokes): <TAB>%Line[21]%
   Text Type (Simulate Keystrokes): <TAB>%Line[22]%
   Text Type (Simulate Keystrokes): <TAB>%Line[21]%
   Text Type (Simulate Keystrokes): <TAB>%Line[22]%
   Text Type (Simulate Keystrokes): <TAB>%Line[23]%<ARROW DOWN><HOME>
   Delay: 95 milliseconds
 End If

 

For the most part there are not many variables after %Line[18]%. In some cases though, there are... I am getting a problem where if a record above has values for %Line[18]%-%Line[20]%, it is duplicating those values (18-20) on the next record if the current record DOES NOT have it's own values for %Line[18]%-%Line[20]%.

 

I thought if there was no value for the variable, it would be blank, but it is retains the values from the prior record. I am not sure what I can do to clear out the Line Variable if there is not value for that Line, because from %Line[17]% on there is not always a value in the variables.

 

Thanks for any help.

 

Heather

[Cory]

I thought if there was no value for the variable, it would be blank, but it is retains the values from the prior record.

Not necessarily, it depends on your method. In cases like these you should clear the variable before reading the next batch in with the "Clear Variables" command.

 

And of course there's a much simpler way to avoid all these issues

[redwards]

Not necessarily, it depends on your method. In cases like these you should clear the variable before reading the next batch in with the "Clear Variables" command.

 

And of course there's a much simpler way to avoid all these issues

 

 

Cory, you say there is a simpler way then leave us hanging with no answer. I read the forums for tips.

[Cory]

Cory, you say there is a simpler way then leave us hanging with no answer. I read the forums for tips.

Ha ha ha ha! Sorry about that. This was an inside joke. My point was this whole method is inefficient. He wants to process thousands of records jumping back bad forth between Excel and other programs GUI and this is not the best method. Better to get the data in a file and process everything invisibly with variables and then output to a neutral file format like CSV and import that to where they need to go. Actually MEP isn’t even the best tool for this sort of thing. When it comes to stuff like this it really should be a compiled application. But I do write a lot of stuff like this just because I haven’t had time to relearn a proper modern programming language. Anyway I'm angling to help him professionally so this was just a little needling by me.

 

But I will pass on one improvement. They’re creating a block of text in Excel, right? Well if it were me I’d create the entire block in a variable and paste the entire block. EG let’s say we have a list of racers first and last names like Roger Penske, Mark Donahue and Sam Posey and I want to create a block 2 columns by 3 rows. I would create a variable to represent tabs and end of line (carriage return/line feed). Say %Tab% and %EoL%. Now I would repeat thru my array until the first blank and append the variable %Output% with %First%%Tab%%Last%%EoL% You could imagine it looks like this in the variable though not accurate.Roger%Tab%Penske%EoL%Mark%Tab%Donahue%EoL%Sam%Tab%Posey%EoL%. Move the variable %Output% to the clipboard and paste into Excel. Since Excel understand tab text files this would be interpreted as the 2X3 block and all this would be as desired. Much better than typing and tabbing between cells.