incrementing variables based on times looped

[RNIB]

I'm trying to write something that reads a list of book titles and catalogue numbers from a spreadsheet and then for each title it extract various bits of information stored in a different program called RMS and then create a folder for each book and inside create a text file into which it pastes the information it has collected. Rather than repeat each step for every book I want it to do each step for all books before moving on to the next. i.e. it extracts the data from RMS for every title, then creates the folder for every title and creates the text file for every book.

This is my code so far. It's very rough and ready and hasn't been tested and probably some parts are in the wrong sequence.

Code: Select all

//Locate Spreadsheet of DMO Titles
//ask for location of spreadsheet.
Input>file_name,Please Browse To The Spreadsheet Containing The Headings,

//get the file name on its own
Separate>file_name,\,parts
Let>xls_name=parts_%parts_count%

//get number of titles. Spreadsheet must be open!
DDERequest>Excel,xls_name,R1C5,title_no,60

//Remove invisible characters from spreadsheet value. NEEDED!
StringReplace>title_no,tab,,title_no
StringReplace>title_no,cr,,title_no
StringReplace>title_no,lf,,title_no

//Extract TB Numbers & Titles from spreadsheet
Let>k=0
Repeat>k
		Let>k=k+1
		Let>row=k
		DDERequest>Excel,xls_name,R%row%C1,TB_no_%k%,60
		DDERequest>Excel,xls_name,R%row%C2,Book_Name%k%,60
Until>k=title_no
//Let>Loop=0
Repeat>Loop
//Start with RMS
SetFocus>Recording Management System
Wait>1
//Is View/Title Record Window Open?
Press Alt
Release Alt
Send>F
//C only does closes the window if the view/title window is open.
//This is needed to ensure the cursor is in the correct point to find the tab order
//If the window isn't open then Pressing Alt returns back to main menu.
Send>C
Press Alt
Release Alt
Press Alt
Release Alt
Wait>1
//Open View/Title window to begin search
Press Enter
SetFocus>Recording Management System
Wait>1
//Now begin search
Press Alt
Release Alt
Send>A
Wait>1
Send>S
Wait>1
Press Enter
WaitWindowOpen>Search Titles
SetFocus>Search Titles
Wait>1
Press Tab*2
Press Down
Press Tab*2
Press Up
Wait>1
//Enter TB . This needs to be changed to enter each variable
Send>009472
Wait>1
Press Enter
Wait>1
Press Tab
Press Shift
Press Enter
Release Shift
//Start Collecting Metadata Info

//Select Author
Press Tab*3
Wait>1.5
Press CTRL
Send>c
Release CTRL
WaitClipBoard
//Goto the Restructure subroutine
GoSub>Author_Name_Restructure
GetClipBoard>Author_Name
SRT>Author_Name_Restructure
VBSTART
VBEND

//example here:
//Let>name=COOPER, Mandy

//Get the copied name from the clipboard
GetClipBoard>name

Let>comma=,
Separate>name,comma,parts

Let>firstname={lower(%parts_2%)}
Let>surname={lower(%parts_1%)}

VBEval>Trim("%firstname%"),firstname
VBEval>Trim("%surname%"),surname

Let>firstname={upper(copy(%firstname%,1,1)) + copy(%firstname%,2,length(%firstname%))}
Let>surname={upper(copy(%surname%,1,1)) + copy(%surname%,2,length(%surname%))}


Let>fullname=%firstname% %surname%
//MessageModal>fullname

//NEED TO CHANGE THIS TO SOMETHING LIKE LET>FULLNAME=AUTHOR_LOOP NO
//Put the restructured name back onto the clipboard for pasting
PutClipBoard>fullname
END>Author_Name_Restructure



//Select Narrator
Press Tab
Wait>1.5
Press CTRL
Send>c
Release CTRL
WaitClipBoard
//Goto the Restructure subroutine
GoSub>Narrator_Name_Restructure

SRT>Narrator_Name_Restructure
VBSTART
VBEND

//example here:
//Let>name=COOPER, Mandy

//Get the copied name from the clipboard
GetClipBoard>name

Let>comma=,
Separate>name,comma,parts

Let>firstname={lower(%parts_2%)}
Let>surname={lower(%parts_1%)}

VBEval>Trim("%firstname%"),firstname
VBEval>Trim("%surname%"),surname

Let>firstname={upper(copy(%firstname%,1,1)) + copy(%firstname%,2,length(%firstname%))}
Let>surname={upper(copy(%surname%,1,1)) + copy(%surname%,2,length(%surname%))}

Let>fullname=%firstname% %surname%
//MessageModal>fullname

//NEED TO CHANGE THIS TO SOMETHING LIKE LET>FULLNAME=AUTHOR_LOOP NO
//Put the restructured name back onto the clipboard for pasting
PutClipBoard>fullname
END>Narrator_Name_Restructure

//Set First Published Date
Press Tab*5
Wait>1
Press Ctrl
Send>A
Release Ctrl
Wait>0.5
Press Ctrl
Send>C
WaitClipBoard
GetClipBoard>first_published

//Set Edition Used
Press Tab
Wait>1
Press Ctrl
Send>A
Release Ctrl
Wait>0.5
Press Ctrl
Send>C
WaitClipBoard
GetClipBoard>edition_used



//End of Loop
Let>Loop=Loop+1
Until>Loop=title_no

What I'm struggling with is how I get the information from the clipstore at each stage to be saved as a variable which has a name that increments with the number of times the macro has looped. Any ideas?

[Marcus Tettmar]

Let>FullName=%Author_Name_Restructure%_%Loop%