Using Excel

In This Topic

Microsoft Excel is one of the common non CabMasterPro tools used to edit tables.

Using MS Excel is a powerful tool but care must be taken to ensure the integrity of the table data.

Before making any change always take a copy of the table.
Then after making changes open the table with the Table Editor to make sure it can read it without issue and that the information looks correct.

MS Excel can be useful particularly if you want to use macros or other advanced features of XL. But some of these macro style activities are supported in the Lookup Table Editor, so it is a good idea to check there first. If you can do it there, then the risk of breaking the table structure is removed.

You may still may decide, however, that you need to use MS Excel because ...

price tables already exist in this format and the data can be more easily copied;
some of the tools that offered in these utilities need to be used (such as increasing all prices by x%). See Converting Tables For Excel.

It is strongly recommended to use the Table Editor to open QLT files. See Open Table Editor As Default.

If you do resort to using Excel to edit directly it is a very good idea to open the QLT using the Table Editor after you are done, because that will do some basic checking and even correct some small problems after warning you.

Step 1 Open table

Open the table you wish to edit (see notes below), in this case, the Customer Details.qlt.

The table in MS Excel will look something like the following...

Excel Eg1

Table comment - Cell A1 contains the table comment (a descriptive name of the table).

In this example, "Customer Listing"

Rows - Cell A2 contains the number of data Rows in the table (not including the header row).

In this example, there are 2 rows (highlighted in yellow).
It is important that if you change the number of rows then this is correctly updated.

Columns - Cell A3 contains the number of data Columns in the table (not including the header column).

In this example, there are 12 columns (highlighted in purple).
It is important that if you change the number of columns then this is correctly updated.

Step 2 Make changes

Be aware of the following considerations...

Some of the information in CabMasterPro tables is surrounded by double quotes. It is important to maintain this structure. If you leave a cell empty, then no value will be provided for that property. If you want to create a property with an empty value you do that explicitly, by providing for a string or 0 for a number.
The formatting of cells by MS Excel may include the use of thousand delimiters in numbers (such as a comma). These are NOT desirable and should be avoided.
Any cell used outside of the table area (even for temporary calculations), must be deleted before saving, otherwise it becomes part of the table and can cause errors.

Step 3 Save and Exit

Click on the Save button. On exit you might be prompted you to save in excel format. This is not required.

More on Saving tables

Saving - it is vital that the table is saved correctly. Please follow the following process.

Once editing is complete click on the 'Save' button. You might get the following message or similar.

Click on the 'YES' button. We do NOT want to save the document as an Excel spreadsheet.

Then when exiting MS Excel it will think that the document has not been saved (as it is not an Excel spreadsheet). This will result in the following message.

Click on the 'No' button, because we do NOT want to save the document as an Excel spreadsheet.

If you have made changes to a materials table, by any method, a refresh needs to be done in the Catalog and Drawing Properties (although the catalog should refresh as it opens). See CabMaster Help topic Materials Refresh

Be aware that refreshing of drawings is not always desirable as you may not want the changes to be reflected in the current drawing (especially pricing).

There are a number of ways to Open a file with Excel.

One way is to use the right click menu "Open with" and locate your Excel app.
Another is to open Excel and File > Open > Browse for relevant file using [F10] File Location of Lookup Tables > All Files *.*
- This will provide a 3 step Text Import Wizard. Just leave the defaults i.e.
  1. File Type = Delimited, click Next
  2. Delimiters = Tab, click Next
  3. Column Data Format = General, click Finish

Tab Separated Text File

To create a tab separated text file, you can use File > Save As in Excel and select the format Text (Tab Delimited) like this...

Tutorials

Import Item List Tutorial