Excel Tables in Quark 6.1

March 24, 2004 - 3:00am ||| 0 Comments | Add new

So I installed the QuarkXPress 6.1 update recently…

…and then spent a few fruitless minutes in the "Get" dialog trying to import an Excel file as a table, one of the new features of this update.

I found the wily wascal hiding in the Table Properties dialog. Here's how to do it, along with some tips on how to work with Excel tables after you've got them in there:

1. Choose the Table tool (below the Picture Box tool) and drag out a rectangle with it, defining your table's initial width and height. As soon as you release the mouse, the Table Properties dialog pops up. Pay attention here, because you'll never be able to recall this dialog again for this particular table.

2. In Table Properties, ignore the fields for number of columns and rows, because importing the Excel workbook sheet will override your entries. Just click the checkbox next to "Link to External File."

3. In the resulting Table Link dialog, in Source, choose "Excel" for Type. Want to see what other types of files can be linked to a Table? Press and hold on the Type: drop-down menu. (I'll save you some trouble: Excel is the only choice. But let's be optimistic that other Types will be added in future releases, otherwise it wouldn't be a drop-down, right?)

4. Click the Browse button and navigate to the .xls file (the Excel worksheet) you want to import as a table. Select it and choose Open.

5. Now you're returned to the Table Link dialog, with the filename entered in the Source: Name field. Below that, two new areas are enabled: Table and Options. Some explanation and a couple tips:

In the Table area, you can choose a particular Sheet, if the Excel file has more than one; and you can specify a Cell Range, which is quite handy if you only need certain rows or columns, not the whole thing.

In the Options area, you can choose to include or ignore hidden rows and columns, format, geometry, and style sheets. (I didn't even know Excel had style sheets. I'm not a big Excel user, though.)

A tip here is to turn OFF "Include Geometry." That way, Quark will shoehorn your Excel data to fit inside the initial boundaries of the Table you drew out back in Step 1. You can always resize the table and its rows and columns later to get everything to fit.

If you keep Include Geometry turned on (the default), and you have a large spreadsheet, Quark will likely as not churn for awhile, thinking about it, and then give you an alert, "Item cannot be positioned off the pasteboard." In that case you're returned to your document and you have to start over.

6. When you're done with the Table Link dialog, click OK. Your Excel data is imported as a table into the Table boundary you drew out in Step 1.

A New Type of Link

Remember the key language in that Table Properties dialog … "Link to External File". When you import an Excel file, you not only import the data, you set up a link to that external file.

In fact, if you go to Utilities -> Usage, you'll find a new tab there (in addition to Fonts and Pictures and a couple others) called Tables. Any Excel files you imported as tables will appear here, with the familiar status of OK, Modified and Missing.

That means if someone updates the Excel file, the Usage -> Tables area will show the status of the linked file as "Modified." If someone renames it or moves it, the status will be "Missing."

I haven't done a lot of testing of this, but I've noticed a few things so far:

- If you choose to Update an imported Excel table (because its status is Modified or Missing), you lose any text formatting you applied in Quark to the table. However, any changes you've made to the row or column dimensions — the "geometry" — are maintained.

- If you Undo the update, the Status for the file in the Usage dialog stays at "OK" instead of going back to "Modified" or "Missing." I don't know if this is a random bug in my particular installation or not. But I've yet to get it to work correctly after an Undo Update Table.

- When you Collect for Output, Quark doesn't also collect the external Excel files, in case you were wondering. But when you choose this command, or when you Print, Quark checks to make sure these tables are up to date. If they're not, you get the usual alert: "Some disk files for the linked tables in the layout are missing or have been modified. Continue?"

- There is no menu option or preference setting that I can find which will force Quark to embed the file in the document — that is, to break the link — as there is in InDesign. But see the next section for a workaround.

So at this point, I'd recommend that if you import any Excel files into Quark tables, and want to take advantage of the linking feature so your table data is always up-to-date, that you avoid formatting the table's text until you're sure you have the final version of the data. And be careful of Undoing an update!

Breaking the Link

To embed the .xls file into your Quark layout, breaking the link to the external file and removing its entry in the Usage -> Tables dialog, do this:

1. Follow the steps above to import the spreadsheet into a Quark table.

2. Select the imported table with the Item tool, go to the Item menu, and choose Table -> Convert Table to Text (or choose it from the contextual menu). Tun on "Delete Table" and click OK. Quark converts the table into a text box filled with tab-delimited text. You'll probably see an overset text icon, don't worry about it.

3. Switch to the Content tool, click inside the text box, and choose Edit -> Select All. This selects all text in the box, including overset text.

4. Go back to the Item menu and choose Convert Text to Table. Accept the defaults it suggests for the number of rows and columns, and click OK.

Quark creates a table for you that looks exactly like the table it created in Step 1, except it's now unlinked to any external file. If you go to Usage -> Tables, you'll see the link to the Excel file is gone.

And there ya go.

Post new comment

The content of this field is kept private and will not be shown publicly.
By submitting this form, you accept the Mollom privacy policy.