CabMasterPro User Guide
In This Topic
    Converting Tables For Excel
    In This Topic
    How to convert text in a lookup table to quoted strings using Excel

    See Using Excel

    When you need to read a string from a Lookup Table (QLT) for use in a formula, it must have quote marks around it so that CabMasterPro knows it is a section of text rather than a variable name. Converting an entire table of data (such as material names) to have the quote marks can be done easily by using macros in Microsoft Excel. A macro is simply a saved sequence of actions to make repetitive tasks simpler and faster.

    Excel Options

    The first step is to open Excel and then select File > Options and enable 'Show Developer tab' in the Excel Options as shown below.

    Click to Expand

    Click to Expand

    Developer

    Then on the Developer tab, select "Record Macro"

     

    On the presented window, enter details i.e.

     

    Then click Stop Recording straight away, which will effectively create an empty macro.

     

    Edit Macro

    Next we have to edit the macro so it will add quote marks to strings and because it is stored in a hidden file which holds your personal macros, do the following...

    Click to Expand

    Click to Expand

     Now click on Macros which will open a dialog, as shown, and click the Edit button with "AddQuotes" highlighted.

    Click to Expand

    Click to Expand

    Visual Basic Editor

    This will open the Visual Basic editor, which should have some text like in the example...

    Example

    No knowledge of Visual Basic is required. All you have to do is insert a few lines so that the section of code ends up looking like the following...

    The parts you have to add are the ones in bold black text.

    Make sure you get both sets of quadruple (4) quote marks and that none of the black lines appear green in the editor.

    There is no need to lay out the text nicely and capitalise keywords, the editor will do this all for you.

    Close the editor when finished (the macro will be automatically saved).

    Save in Excel

    Now all you have to do is click the "Save" in Excel, then select Hide from the Window menu to re-hide "Personal.xls" (see Edit Macro above).

    You can now open any Lookup Table

    Close Excel and if it asks if you wish to save "Personal.xls", click Yes/Save.