Excel Templates Quote/Invoice Maker for ACT! - Sage ACT! add-ons
Contents
1. Arial 10 H 7 ACT C60 hA f amp AVERAGE Name Box R In this case the cell name is ACT C 60 which means the cell was mapped to the field with 60 for internal ID ie User11 Note this name and go to Excel Insert Name Define window In the list of names select the name displayed in the Name Box and click Delete If you used the Insert a descriptive comment in cells with an Excel gt ACT field option do a right click on the cell and select Delete comment to get rid of it Excel Templates for ACT User s Manual 9 Creating a merged document To merge a template with data from the current contact run Excel Templates for ACT then select your template and click OK Excel Templates for ACT will open the template in Excel and replace the field codes with the corresponding field values of the current contact Saving the document To save your merged document use the File Save As and Update ACT History menu Microsoft Excel Invoice Basic1 View Insert Format Tools Data Window Ctrl M Open Chrl 0 Close gave Ctrl 5 Save s and Update ACT History Save AS Save as Web Page Excel will then save your document under a name of your choice merged documents are saved with the standard XLS extension and the following window will open Create History Mote Attach document to history IF both items are checked the i p document will be attached to the M Add a Letter sent history recor2. Units oPrice Row2 S 4mount Row S Product RowS Si Units Row3 S Price Rowas S 4mount Rows 5 Product Fow oilnts Row4 S Price Row4 5 Amount Rows S Product Fow5 oUnits Row5 S Price Row5 S 4mount Rows gt Product Row oUnits Rowb S Price Rowb S Amount Row Excel Templates for ACT User s Manual 14 NOTE The program will automatically assign the next index to your field so you don t have to worry about indexing manually Creating a merged document Once again it works the same way as described in the preceding chapter with one difference When Quote Invoice Maker for ACT detects that there is one or more sales field in the merged document it prompts you to choose which sales opportunities from the current contact you want to include in the document J List of the currently visible Sales Opportunities for the current contact Select one or more Sales Opportunities to be included in the document 11 Aug 03 Smoked Meat Special order Uy ial D3 Balloon Bou Special order Hold the CTRL while clicking to select multiple items Cancel 1 You may change the order in which the sales opportunities are listed using the blue arrows as this order will be reflected in the document 2 Select the sales opportunities you want to include in the document by clicking them If you want to select more than one opportunity hold the CTRL key while clicking 3 Press
3. have Excel perform calculations and import the results of these calculations in ACT Installation procedure Download the program file from our download page and double click it to start the installation Follow the instructions and once the installation is completed run ACT and open your database Then click the Windows Start button go under Programs locate the Exponenciel ACT Add Ons group and click Excel Templates for ACT An icon will be automatically added to your Contact view toolbar In the event this installation process failed click the Windows Start button go under Programs locate the Exponenciel ACT Add Ons group and click Excel Templates for ACT The main window will be displayed Go to the Options Integrate with ACT menu and add icons or menu items to your ACT screens as needed When prompted accept that ACT be re started When ACT re starts whatever icons or menu items you asked to be created will be there to launch Excel Templates for ACT Excel Templates for ACT User s Manual Creating an Excel Template with ACT fields To create an Excel Template launch Excel Templates for ACT then choose File New Template in Excel Templates for ACT main window Excel Templates for ACT m a File Options Help New template hs Edit template Exit Invoice Basic sBT TA Invoice Detailed sB T Cancel m l Quote Basic BT EJ Documents and Settings Quote Detailed BT EJ Gilles Sy My D
4. type When you insert a field note that it won t actually insert a code in the cell itself but rather it will name the active cell with a coded name ACT C fieldID In fact you are not inserting a field but rather mapping a cell to a field in ACT If you check Jnsert a descriptive comment in cells with an Excel gt ACT field a comment will be inserted in the cell so that when you place your mouse over the cell it will pop up This may be useful to edit your templates EXCEL 4CT This cell is mapped to the ACT Company Field When your template is created make sure you save it using the File Save As Excel Templates for ACT template menu Excel Templates for ACT User s Manual 8 NOTE Excel Template for ACT does not import currency signs nor date formats Cell formatting is handled directly by Excel Use the Excel Format Cells command to properly format your data currency signs date format etc Removing an Excel gt ACT field As explained above when you insert an Excel ACT field nothing is inserted in the cell but the same is named using a particular syntax ACT _C_fieldID To remove an Excel gt ACT field you need to delete this name Here is how to proceed click the cell with the Excel ACT field you want to remove Look for the Name Box in the upper left corner of your Excel Window just left of the formula bar Microsoft Excel Calculations Numer File Edit View Insert Format To
5. Excel Templates amp Quote Invoice Maker for ACT Another efficient and affordable ACT Add On by http www exponenciel com Excel Templates for ACT User s Manual 2 Table of content Relationship between Excel Templates and Quote Invoice Maker ssseecccscssessecccosssssseesossso 3 PUEDOSC OL the add Om soosis e EEE EE E E 3 PriS Call AGO MOCO CUR eiorinn a A 3 Creating an Excel Template with ACT fields cccccccssssssssssccccccccsssssscsccccsscsessssccssssseeees 4 nserins a field macel arria il acceded cece sitet cesccc anc aeenoncccaaee nex erconeestaae OR 5 Wat ir the CeMllas Ol enp y ace 5e 8ec ces saa a alm owes e star em eee 6 Creatine bi directional template siscsisccicsccesssccasasstsecsiussacecsscseaseoacatssseueeasecadsasssaceusaseaseesasesceuseoess 7 Adding an Excel AGT Aod A ema asad eeeasoecnas 7 Removine an Excel DA CU Field aea e a E NE 8 Creatine a merged document s56icceccccacasiaccaccecesznecssndcdactaelztestasesecdedestiostasidheveaettheaseseuasoeceteiaceesiGnst 9 Savine t hedocumenters iaraa A ate denen eames 9 Updating ACT eld eni een E EA 10 Automaton OPCIONS sesiesusscccacsessaiastaccsavesSiccieucesea iui lodeseustscealeliccatslesteseseed ccsiussiiveleneesdetiaastecussides 10 Running in fully automated MO Caaiissscccussscsscsckcscsscivsvcaesccsecesdaveceasstasseeddsuvestevatsecesiavecsavedisoeessuse 11 Naming The mersed COCUMENE siiessciissesecssevvererssasdsuesuoscaiussaustevveas
6. OK NOTE This window lists all sales opportunities currently displayed under the Sales Opportunities tab allowing you to use the tab filter to filter out records Here is the merged document resulting from the above selection Excel Templates for ACT User s Manual 15 DESCRIPTION UNIT PRICE AMOUNT 12 00 25 50 f 306 00 o 00 12 50 100 00 SUBTOTAL TAX RATE SALES TAX TOTAL Hiding errors in cells Quote Invoice Maker scans your document before opening it to figure out if it contains Sales fields or not If a cell of your template contains a formula which returns an error message when no data is entered in the template Quote Invoice Maker s scan might stop too early So you should hide the error message Here is how to proceed In the cell type 1fUSERR your_ formula your _formula With this syntax if the formula returns an error the cell will be blank and 1f not it will contain the result of the formula Registering your product To continue using your product passed the trial period please buy a license at www exponenciel com Licenses are issued for one ACT username and will only work for that username You may upgrade at any time from Excel Templates for ACT to Quote Invoice Maker for ACT for a small upgrade fee Support For support contact support exponenciel com Rev September 15 2003 V 1 1 2
7. d Veep SE A Regarding Invoice 4003 02 01 coe e Ifyou check Attach document to history alone the document will be attached to the current contact e Ifyou check Add a Letter sent history record alone a Letter sent history record will be added with the regarding information that you typed in the appropriate box e If you check both the Letter sent record will be created with the document attached to it Excel Templates for ACT User s Manual 10 Updating ACT fields If your template is bi directional you may manually update ACT with the values of the cells you mapped by going to Excel Data menu and choosing Transfer Mapped Data to ACT For automating this process see below Automation Options 124 iz window Sort Filter Form Subtotals Validation Table Text to Columns Consolidate Group and Outline PivotTable and PivotChart Report Import External Data ER Transfer Mapped Data to ACT When creating a merged document ACT Excel only you have two automation options to choose from in Excel Templates for ACT main window if you check Do not open the document Simply Attach it to the current contact the new document will be created in the background and automatically attached to the current contact If you check Do not open the document Simply Update the current contact if the template is bi directional the new document will be created in the backgro
8. d list a field named Date and Time allows to insert the current date and time it will not update itself automatically once inserted Use Excel Format Cells menu to remove the date or time as needed When your template is created make sure you save it using the File Save As Excel Templates for ACT menu Excel Templates for ACT templates are saved with the XBT extension Microsoft Excel Invoice Detailed XBT View Insert Format Tools Data Ctrl M Open Chrl 0 Close cave Cbrl 5 Save 46 Excel Template For ACT h Save S Save as Web Page Excel Templates for ACT User s Manual 7 Creating a bi directional template The basic same principles apply for creating a bi directional template By combining both ACT gt Excel and Excel ACT fields in one template you can export contact information to the spreadsheet which can then compute the data and send computed data back to the same or other ACT fields Adding an Excel ACT field To create a template follow the same procedure as described above To insert Excel gt ACT fields make sure you select Excel gt ACT in the dropdown box of the Insert Field window Mail Merge Fields Excel gt ACT 2nd Contact 2nd Ph f Contact Field mite rite 3rd Contact 3rd Phone 3rd Title Address 1 Address 2 Address 3 Alt Phone Auto Select Next Insert Close lf Insert a descriptive comment in cells with an Excel gt AcCT Field Field
9. ed in the current cell Mail Merge Fields ACT Excel RH ERE Address 2 Address 3 Contact Field Alt Phone Assistant My Record Field Birth Dake City Company Contact Country Sales field Fax Home Address 1 W Auto Select Next Auto Move 1 Cell Right Field label Suto Move 1 cell Down pedas The code should be familiar to you if you have created word processor templates before Excel Templates for ACT User s Manual 6 What if the cell is not empty If the current cell is not empty you will be prompted to choose if you want to overwrite the current cell content or append it Excel Templates for ACT A Do you want bo overwrite the current cell Click Yes bo overwrite or Mo to append to the cell content Ves Mo Cancel Click Yes to overwrite No to append and Cancel to cancel This feature is particularly useful for concatenating fields You can easily create a cell containing City State Zip NOTE To speed up the creation of your templates we added these options e The Auto Select Next option will automatically select the next field down in your field list once you click Insert e The Auto Move 1 Cell Right will automatically make the cell immediately to the right of the active cell active once you click Insert e The Auto Move 1 Cell Down will automatically make the cell immediately under the active cell active once you click Insert e At the bottom of the ACT gt Excel fiel
10. eraissacawedsissusievensesevesssdoeusneseseues 12 Quote Invoice for ACT Additional Features ccsscccsssccssssccssssccssscccssscccsssccssscccsssccesscoees 13 Creating a template with Sales Opportunities fields c ccc cccccccseeeeeeeeeeeeeeeeseeeeeeaeeaeaeees 13 Cieatine a mMersed COCUIMCI oaren taak his eles esac etek ald lea th sak a ll 14 FG C rt OFS NEE S fs coir es OG teria O ue cancun ca ciaeaiaae aie a 15 REGISCERING your PEOUUCE sionin O aa aai 15 PUD LI a EAEN A T AEE T E AT N OEN EE I EAEE EAE E A EN 15 Excel Templates for ACT User s Manual 3 Relationship between Excel Templates and Quote Invoice Maker Quote Invoice Maker for ACT brings additional features to Excel Templates for ACT Whereas Excel Templates for ACT allows to merge contact fields in your Excel documents Quote Invoice Maker for ACT also allows to merge sales opportunities fields a feature particularly useful to create quotes or invoices directly from your ACT sales opportunities Purpose of the add on Excel Templates for ACT allows to create a bi directional link between ACT and Excel You may create MS Excel templates the same way you create MS Word templates in ACT ie by inserting fields in an Excel document Excel Templates for ACT also allows to make use of Excel powerful calculation features to update fields in ACT by allowing you to map cells from your Excel spreadsheet to your ACT fields So you could export data to Excel
11. noTransfer for no Data transfer IMPORTANT NOTE FOR SETTING UP A TRIGGER IN ACT If your command line argument i e the path of the template contains spaces ACT will return an invalid path error when trying to launch the program So we provided a placeholder to replace any space in the path of the template the interrogation mark Ex This command will not work because of the space between My and Documents in the argument C Program Files Exponenciel Excel Templates for ACT ExcelTemplates exe C My Documents template XBT Use this command line instead C Program Files Exponenciel Excel Templates for ACT ExcelTemplates exe C My Documents template XBT Be careful Do not replace spaces in the path of the program itself C Program Files Exponenciel Excel Templates for ACT ExcelTemplates exe just in the command line arguments Excel Templates for ACT User s Manual 12 Naming the merged document Excel Templates for ACT also helps you automatically name your documents which is particularly useful if you want to include the name of the company of the contact or any information contained in a field To define the naming convention go to the Options menu and choose Preferences 2 Preferences Default Template Folder 7 l OF C Proaram Files Exponenciel E scel Templates for ACT emplates yA Cancel Default Document Location CAD ocuments and SettingsGilles My Documents 4CT Documen
12. ocuments EVB J EscelT emplates E Templates Do not open the document Simply Attach it to the current contact ke Update the current contact if the template is bi directional Excel will launch and a popup window with the fields names will automatically open see below You may close this window To re open it at any time use the Tools Insert ACT fields menu Mail Merge Fields ACT gt Excel Field type Pond Contact 2nd Phone f Contact field nd Title 3rd Contact C My Record Field 3rd Phone 3rd Title Field label o Address 3 Alt Phone Assistant W Auto Select Mest Auto Move 1 Cell Right Auto Move 1 cell Down Insert Close Excel Templates for ACT User s Manual If the window does not popup or if you closed it you can open it using the nsert ACT Mail Merge Fields menu Microsoft Excel Project Details XBT File Edit wiew Insert Format Tools Data Wind Rows Pea a oe i Columns Worksheet Chart Symbol Function Mame Comment Picture Diagram Qbject Hyperlink Cbri k ACT Mail Merge Fields The Mail Merge Fields window has a dropdown list on top of the fields list Make sure ACT gt Excel is selected Inserting a field in a cell To insert a field in a cell click that cell but don t enter edit mode or you won t be able to continue then select the field in the list and click Insert The field will be insert
13. t ie Merged Document Naming Convention fi emplateN ame 25 Ex Templateh ame 25 will add the company name to the template name Documents are automatically incremented if another document with the same name already exists Replace potential illegal characters with Leave blank to just skip legal characters The name can be defined using any character allowed in a file name as well as two types of codes TemplateName will insert the name of the template field_ Id will insert the value of the field which ID is in between brackets As en example if your Naming convention is TemplateName 25 and you are creating a new document based on the Invoice XBT template for a contact with the ABC Design company the merged document will be automatically named Invoice ABC Design XLT If you are in manual mode you will have a chance to rename the file when saving it 1f the name created does not suit you NOTES e This feature is particularly important when running in automated mode see ablove as you won t get an opportunity to manually name the file before it is saved e To get the list of field IDs run the actdiag exe file located in your ACT folder go to the Reports menu and choose Field Report Each field is assigned an ID Company is 25 Contact is 26 etc o Files are automatically incremented as in Document1 XLS Document2 XLS etc so that you never risk to overwrite an existing file Excel Templa
14. tes for ACT User s Manual 13 NEW IN V 1 1 As the field data used to create the name for the Excel file may contain invalid characters for file naming you now have the option to automatically replace these characters with a character of your choice Quote Invoice for ACT Additional Features As we said earlier Quote Invoice for ACT gives you the ability to merge sales opportunities fields in your documents Creating a template with Sales Opportunities fields Creating a template works exactly the same way as described in the preceding chapter You just have one more option in the Mail Merge Fields window Sales field Mail Merge Fields ACT gt Excel Field type Amount Close Date f Contact Field Creston Dake Details 0 My Record Field Forecasted Amount Forecasted Units Main Competitor Price Probability Product Reason El i Auto Select Next Auto Move 1 Cell Right Field label Auto Move 1 cell Gown Insert Close The principle is the same you select the field you want to insert and the proper code is inserted in the current cell The difference here is that there may be several Product Type Amount etc fields since you may want to include several sales opportunities in the same document So the code will be slightly different it is appended with a row index DESCRIPTION UNIT PRICE AMOUNT S Product Row Units oPrice Row S 4mount Row Product Row
15. und and if required data will be transferred back to the current contact You may check both options so that your ACT fields are automatically updated and the document is attached to the contact Excel Templates for ACT User s Manual l1 Running in fully automated mode Excel Templates for ACT may be run in fully automated mode You could create an icon on your ACT toolbar or add a trigger to one of your fields to have the program run automatically All that is needed is that you provide the path of a template preceded with a as a command line argument So if you create a shortcut with the following syntax it will automatically create a merged document based on the template X BT C Program Files Exponenciel Excel Templates for ACT ExcelTemplates exe C Templates template XBT NOTE When in automated mode by default all automation options are automatically activated therefore the document will be saved attached to the current contact and if any Excel cells are mapped to ACT fields these fields will be updated Excel will be closed automatically if any unsaved documents are open you will be prompted to save them though NEW IN v 1 1 Jf you wish to either not attach the document i e you only want to transfer data back to ACT or not transfer any data back to ACT to update fields ie you only want to attach the document add the following switch at the end of your command line noAttach for no attachment or
Download Pdf Manuals
Related Search