Jump to content
Macro Express Forums

Appending T-Var to Excel


stevecasper
 Share

Recommended Posts

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!

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...