Appending T-Var to Excel

[stevecasper]

Hi,

 

I've searched the boards and tried a few different things I've found, but nothing I've seen does what I'm trying to do. Here's the basic idea:

 

In Excel I have a list of numbers in column A and another list of numbers in column B. I run a macro that searches for the specific number in column A and when it is found, the macro then takes the information on that line from column B.

 

This much works fine.

 

Here's where I have a problem:

 

If there is no match in column A (the number searched for doesn't exist), I get a prompt for what should be in column B (The macro already knows what should be in column A based on the search).

 

Now I want the macro to add the information from my variables to column A and column B.

 

Here's what I have so far:

 

Scripting Editor

 

// For this example, 972 is not in my list yet.  I need it to be.
// The number needed in Column B is 801 (it is not there yet, either).
Variable Set String %T3% "972"
// The following Process puts Column A into T1 and Column B into T2, line by line.
ASCII File Begin Process: "TestExcel.csv" (Comma Delimited Text )
 If Variable %T1% = variable %T3%
   Break
 End If
ASCII File End Process
// If T1 never = T3, then T3 is not yet in my list
If Variable %T1% <> variable %T3%
 Variable Set String %T2% ""
 Variable Set String %T2% from Prompt
 // Right here I want to be able to append T3 to Column A and T2 to Column B.
 // So that next time I search for T3 I won't have to input T2 by hand again.
End If
Variable Modify String: Append %T3% to %T2%
Variable Set String %T3% "%T2%"
Text Box Display: T3

 

Direct Editor Script

 

<REM2:For this example, 972 is not in my list yet.  I need it to be.><REM2:The number needed in Column B is 801 (it is not there yet, either).><TVAR2:03:01:972><REM2:The following Process puts Column A into T1 and Column B into T2, line by line.><ADFBEG:F10:001:000001:000000:C:\TestExcel.csv><IFVAR2:4:01:1:T3><BREAK><ENDIF><ADFEND><REM2:If T1 never = T3, then T3 is not yet in my list><IFVAR2:4:01:2:T3><TVAR2:02:01:><TVAR2:02:02:FWhat number goes with %T3%?FFCenter:Center><REM2:Right here I want to be able to append T3 to Column A and T2 to Column B.><REM2:So that next time I search for T3 I won't have to input T2 by hand again.><ENDIF><TMVAR2:08:02:03:000:000:><TVAR2:03:01:%T2%><TBOX4:T:1:CenterCenter000278000200:000:T3%T3%>

 

If more information is needed, or if I've been unclear about anything, please let me know.

 

Thanks!

[stevecasper]

Well, I guess I'm just impatient

 

After much trial and error, and no immediate response, I think I've figured out how to do it. I don't know if it's the most elegant way (though it's better than a lot of my earlier attempts) so if anybody knows or thinks of a better way to do this, please let me know.

 

For anybody who is looking to do the same thing (or something similar), here's what I did:

 

Scripting Editor:

Variable Set String %T3% "972"
// Here I create a .txt file with my .csv data (the information ends up separated by a comma)
If File Exists "TestExcel.csv"
 Copy File or Files: "TestExcel.csv"
End If
// The following Process puts Column A into T1 and Column B into T2, line by line.
ASCII File Begin Process: "TestExcel.csv" (Comma Delimited Text )
 If Variable %T1% = variable %T3%
   Break
 End If
ASCII File End Process
If Variable %T1% <> variable %T3%
 Variable Set String %T4% ""
 Variable Set String %T4% from Prompt
 Variable Set String %T2% "%T4%"
 Variable Modify String: Append ",%T4%" to %T3%
 Variable Modify String: Append %T4% to Text File
 // Here I replace my .csv file with the .txt file where I just appended the new info.
 Copy File or Files: "TestExcel.txt"
End If

 

Direct Editor:

 

<TVAR2:03:01:972><REM2:Here I create a .txt file with my .csv data (the information ends up separated by a comma)><IFOTH:01:2:C:\TestExcel.csv><DOFILE:05:NN:C:\TestExcel.csv>C:\TestExcel.txt><ENDIF><REM2:The following Process puts Column A into T1 and Column B into T2, line by line.><ADFBEG:F10:001:000001:000000:C:\TestExcel.csv><IFVAR2:4:01:1:T3><BREAK><ENDIF><ADFEND><IFVAR2:4:01:2:T3><TVAR2:04:01:><TVAR2:04:02:FWhat is the correct number for Column B?FFCenter:Center><TVAR2:02:01:%T4%><TMVAR2:07:03:00:000:000:,%T4%><TMVAR2:20:04:00:000:000:C:\TestExcel.txtF><REM2:Here I replace my .csv file with the .txt file where I just appended the new info.><DOFILE:05:NN:C:\TestExcel.txt>C:\TestExcel.csv><ENDIF>