I've written a macro that stores the data in multiple cells in an Excel spreadsheet in multiple variables and now I just need to get the macro to compose a document in Word by pasting in predefined text along with the variables. This is what I have at present:
Code: Select all
//First the macro extracts the relevant information from the spreadsheet
//Copy Transcription Officers Name from D3 and store as variable $tOfficer
DDERequest>Excel,C:\temp\calculator.xls,R3C4,$tOfficer,10
StringReplace>$tOfficer,CRLF,,$tOfficer
//Copy Date from I3 and store as variable $dDay
DDERequest>Excel,C:\temp\calculator.xls,R3C9,$dDay,10
StringReplace>$dDay,CRLF,,$dDay
//Copy Contact Name from D5 and store as variable $cName
DDERequest>Excel,C:\temp\calculator.xls,R5C4,$cName,10
StringReplace>$cName,CRLF,,$cName
//Copy Organisation from D7 and store as variable $cCompany
DDERequest>Excel,C:\temp\calculator.xls,R7C4,$cCompany,10
StringReplace>$cCompany,CRLF,,$cCompany
//Copy Word Count from D9 and store as variable $wCount
DDERequest>Excel,C:\temp\calculator.xls,R9C4,$wCount,10
StringReplace>$wCount,CRLF,,$wCount
//Copy Number of A4 Pages from G9 and store as variable $A4
DDERequest>Excel,C:\temp\calculator.xls,R9C7,$A4,10
StringReplace>$A4,CRLF,,$A4
//Copy Braille Master Copy Price from C16 and store as variable $bMaster
DDERequest>Excel,C:\temp\calculator.xls,R16C3,$bMaster,10
StringReplace>$bMaster,CRLF,,$bMaster
//Copy Braille Copy Price From E16 and store as variable $bCopy
DDERequest>Excel,C:\temp\calculator.xls,R16C5,$bCopy,10
StringReplace>$bCopy,CRLF,,$bCopy
//Copy Braille Total Price from G16 and store as variable $bTotal
DDERequest>Excel,C:\temp\calculator.xls,R16C7,$bTotal,10
StringReplace>$bTotal,CRLF,,$bTotal
//Copy Large Print Master Price from C17 and store as variable $lpMaster
DDERequest>Excel,C:\temp\calculator.xls,R17C3,$lpMaster,10
StringReplace>$lpMaster,CRLF,,$lpMaster
//Copy Large Print Copy Price from E17 and store as variable $lpCopy
DDERequest>Excel,C:\temp\calculator.xls,R17C5,$lpCopy,10
StringReplace>$lpCopy,CRLF,,$lpCopy
//Copy Large Print Total Price From G17 and store as variable $lpTotal
DDERequest>Excel,C:\temp\calculator.xls,R17C7,$lpTotal,10
StringReplace>$lpTotal,CRLF,,$lpTotal
//Copy Audio CD Master Price from C18 and store as variable $cdMaster
DDERequest>Excel,C:\temp\calculator.xls,R18C3,$cdMaster,10
StringReplace>$cdMaster,CRLF,,$cdMaster
//Copy Audio CD Copy Price from E18 and store as variable $cdCopy
DDERequest>Excel,C:\temp\calculator.xls,R18C5,$cdCopy,10
StringReplace>$cdCopy,CRLF,,$cdCopy
//Copy Audio CD Total Price from G18 and store as variable $cdTotal
DDERequest>Excel,C:\temp\calculator.xls,R18C7,$cdTotal,10
StringReplace>$cdTotal,CRLF,,$cdTotal
//Copy Audio Tape Master Price from C19 and store as variable $atMaster
DDERequest>Excel,C:\temp\calculator.xls,R19C3,$atMaster,10
StringReplace>$atMaster,CRLF,,$atMaster
//Copy Audio Tape Copy Price from E19 and store as variable $atCopy
DDERequest>Excel,C:\temp\calculator.xls,R19C5,$atCopy,10
StringReplace>$atCopy,CRLF,,$atCopy
//Copy Audio Tape Transfer Charge from F19 and store as variable $atTransfer
DDERequest>Excel,C:\temp\calculator.xls,R19C6,$atTransfer,10
StringReplace>$atTransfer,CRLF,,$atTransfer
//Copy Audio Tape Total Price from G19 and store as variable $atTotal
DDERequest>Excel,C:\temp\calculator.xls,R19C7,$atTotal,10
StringReplace>$atTotal,CRLF,,$atTotal
//Copy DAISY Master Price from C20 and store as variable $dMaster
DDERequest>Excel,C:\temp\calculator.xls,R20C3,$dMaster,10
StringReplace>$dMaster,CRLF,,$dMaster
//Copy DAISY Copy Price from E20 and store as variable $dCopy
DDERequest>Excel,C:\temp\calculator.xls,R20C5,$dCopy,10
StringReplace>$dCopy,CRLF,,$dCopy
//Copy DAISY Total Price from G20 and store as variable $dTotal
DDERequest>Excel,C:\temp\calculator.xls,R20C7,$dTotal,10
StringReplace>$dTotal,CRLF,,$dTotal
//Copy Electronic Master Price from C21 and store as variable $eMaster
DDERequest>Excel,C:\temp\calculator.xls,R21C3,$eMaster,10
StringReplace>$eMaster,CRLF,,$eMaster
//Copy Electronic Copy Price from E21 and store as variable $eCopy
DDERequest>Excel,C:\temp\calculator.xls,R21C5,$eCopy,10
StringReplace>$eCopy,CRLF,,$eCopy
//Copy Electronic Total Price from G21 and store as variable $eTotal
DDERequest>Excel,C:\temp\calculator.xls,R21C7,$eTotal,10
StringReplace>$eTotal,CRLF,,$eTotal
//Copy Translation Price from C22 and store as variable $tMaster
DDERequest>Excel,C:\temp\calculator.xls,R22C3,$tMaster,10
StringReplace>$tMaster,CRLF,,$tMaster
//Copy Translation Total Price from G22 and store as variable $tTotal
DDERequest>Excel,C:\temp\calculator.xls,R22C7,$tTotal,10
StringReplace>$tTotal,CRLF,,$tTotal
//Copy Final Total Price from G23 and store as variable $fTotal
DDERequest>Excel,C:\temp\calculator.xls,R23C7,$fTotal,10
StringReplace>$fTotal,CRLF,,$fTotal
// Next the macro switches to the Word document and pastes in the relevant information where needed
// Start Create Word Document
SetFocus>Quotation_English.dot - Microsoft Word
WaitWindowOpen>Quotation_English.dot - Microsoft Word
Press Enter
Wait>0.5
Press Enter
//Bold Text On
Press Ctrl
Send>b
Release Ctrl
Wait>1
Send>Transcription Services
Wait>0.5
Press Enter
Wait>0.5
Press Enter
//Bold Text Off
Press Ctrl
Send>b
Release Ctrl
Wait>0.5
Send>To: %$cName%
Wait>0.5
Press Enter
Wait>0.5
Send>Company: %$cCompany%
Wait>0.5
Press Enter
Wait>0.5
Send>Date: %$dDay%
Wait>0.5
Press Enter
Wait>0.5
Send>From: %$tOfficer%
Wait>0.5
Press Enter
//Bold Text On
Press Ctrl
Send>b
Release Ctrl
Wait>0.5
Send>Subject:
Wait>0.5
Press Enter
Wait>0.5
Press Enter
//Bold Text Off
Press Ctrl
Send>b
Release Ctrl
Wait>0.5
Send>Thank you for your recent enquiry regarding our transcription services. Please find an estimate for the work you require on the following page(s).
Wait>0.5
Press Enter *2
Wait>0.5
Send>This estimate is based on the number of words in the final document and will be calculated on an A4 page equivalent (an accurate word count will be carried out on the final document).
Wait>0.5
Press Enter *2
Wait>0.5
Send>There will be a minimum charge of £30 for the whole order.
Wait>0.5
Press Enter *2
Wait>0.5
Send>Please do not hesitate to contact me if you have any queries regarding this estimate.
Wait>0.5
Press Enter *2
//Bold Text On
Press Ctrl
Send>b
Release Ctrl
Wait>0.5
Send>Number of A4 Pages:
//Bold Text Off
Press Ctrl
Send>b
Release Ctrl
Wait>1
Send> %$A4%
Wait>0.5
Press Enter *2
Wait>0.5
If variable $bMaster=£0.00 than it means the client hasn't requested this item and the macro should then check to see if they have ordered the next product, $lpMaster.//Copy Braille Master Copy Price from C16 and store as variable $bMaster
DDERequest>Excel,C:\temp\calculator.xls,R16C3,$bMaster,10
StringReplace>$bMaster,CRLF,,$bMaster
//Copy Braille Copy Price From E16 and store as variable $bCopy
DDERequest>Excel,C:\temp\calculator.xls,R16C5,$bCopy,10
StringReplace>$bCopy,CRLF,,$bCopy
//Copy Braille Total Price from G16 and store as variable $bTotal
DDERequest>Excel,C:\temp\calculator.xls,R16C7,$bTotal,10
StringReplace>$bTotal,CRLF,,$bTotal
If however $bMaster is greater than £0.00 then I need the macro to paste into word some text followed by %$bMaster%. It should then check $bCopy. If $bCopy=£0.00 then they haven't ordered copies and it should then jump to the $bTotal bit.
If however $bCopy is greater than £0.00 then I need the macro to paste into Word some text followed by %$bCopy% and then it needs to go to the $bTotal bit.
The final bit is to then have some text pasted into word followed by $bTotal.
This then needs to be repeated for each of the 7 different products. So there are lots of If and Nested If statements and I'm very confused as to how best to write this. I've thought that maybe part of this could be achieved by using Labels for each product but I'm still not sure how to go about writing the nested If's.
