Close Excel and re-run setup.
In Excel, click Tools->Add-Ins…, click Browse… and select C:\Program Files\Event 1\Office Connector\OfficeConnector.xla.

When I try to run the self extractor file that I downloaded from your web site, I get a message saying that it is not a valid Win32 or WinNT image.

This symptom indicates a corrupted download. Download the image again; when prompted, be sure to click Save, not Open. Pick a new location and try opening the file from there.

Re-downloading might not resolve the issue if the corrupted file is in your Internet cache. You can tell if this is the case because the download will complete very quickly (within a couple of seconds). If this is the case, you will need to clear your Internet cache before downloading again, or try downloading from a different machine.

How do I verify that ODBC is installed?

To verify your ODBC status, go into TS-Main and select "Tools / Change registration name or uses". Select "Number of uses" and click Next to display a list of all applications and their activation codes (the same one you see when you install the software). ODBC is listed in the lower right corner. Note that this window can only be used when no one else is logged in to Sage Timberline Office.

How Do I turn Office Connector off for a particular user under Citrix?

The following procedure will prevent the Office Connector toolbar from showing up for a particular user.

Click Start->Run, type regedit and click OK:
Navigate to “HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options”. The “11.0” is for Office 2003. Previous versions will have a lower number there.
Right click “Open” and pick Modify from the popup menu.
Delete the value that contains the path to your OfficeConnector.xla file.

Setup says that my system files are out-of-date and it must restart. I restart, but setup continues to tell me that my system files are out-of-date.

Symptom:

User was getting the message "System files are out of date and must be updated." when starting the setup program. Clicking <OK> resulted in a reboot and when they attempted the install again, it gave the same message. When asked if anything else was running, the user indicated that she had closed all programs before starting the install.

Resolution:

There were numerous programs in the system-tray. Several were fairly common (such as AOL Instant Messenger, Norton Anti-Virus) but two were not. One was a screen-capture utility and I never got an explanation of the other "EMS II" something. I closed all programs that were running in the system tray and re-started the install. That resolved the issue. I suspect that one of the programs had files in-use that OC uses (such as the VB6 runtime or something).

Workbook Function Topics

How do I reference a cell from an Office Connector workbook function?

The quick answer is “Replace the existing value with a cell reference using the following syntax

“ & <cell address> & “

Following is a more detailed explanation: Start by creating the workbook function and using a hard-coded value for the filter (see the screen shot for JC transaction example below). That will create a function like this (the bold part represents the filter argument):

=TSSum("CURRENT_JCT_RECORD_1","TAMOUNT","((TACTDAT >= '01/01/2003') AND (TACTDAT <= '12/31/2003'))")

Now modify the filter argument by replacing the date values with cell references. You need everything except the date value itself. Supposing you had the begin and end dates in cells C2 and D2 respectively. Your filter argument will go from this:

"((TACTDAT >= '01/01/2003') AND (TACTDAT <= '12/31/2003'))"

To This:

"((TACTDAT >= '” & C2 & “') AND (TACTDAT <= '” & D2 & “'))"

The “trick” here is to get the syntax correct for joining strings together. The & operator concatenates strings, and the double quote defines the beginning or end of a string. Above we use the double quotes to break the filter string into pieces, replace the date value with a cell reference and use & to concatenate the results back into a single string.

How do I make a workbook function show the correct data for each row?

You need to use cell references in your function for the data that changes on each row. See How do I reference a cell from an Office Connector workbook function.

When Should I Use Filter vs Where?

Excel queries only support the Where technology, so this discussion only applies to the Office Conector workbook functions (TSLookup, TSFind, TSSum, TSCount). By default, Office Connector workbook functions use Microsoft’s Filter technology to apply criteria. First, all of the data is loaded, and then the filter is applied. Once you have paid the initial time penalty for loading the data, using Filter technology to apply the criteria to many formulas is fast. In a case where you only need one or two results from a given table, it can be faster to override this default behavior and pass the criteria directly to the Sage Timberline Office ODBC driver.

Here’s an example to illustrate the difference. Suppose that you have inserted a query that lists 300 of the jobs in your database, and now you need to sum the transactions for each job. You use the TSSum wizard to create the first workbook formula, which results in an agonizing 5-minute wait while the transaction table is pulled across the network. To speed things up, you insert the word “Where” in front of the Filter argument and click the Refresh button. Using the Where technology, your TSSum function now returns a result in 4 seconds. Not being really great at math, you say to yourself, “Now that’s like a billion times speedup!”. Next you fill down the formula for every row in your job query, and it looks like Excel has locked up. Twenty minutes later you get to see your results. Twenty minutes is far worse than five, so you remove the word “Where” from the filter argument and fill down the formula again. You encounter the 5 minute penalty again, but then you see the results almost immediately.

I use Property Management and I need to find the current total square footage of a unit.

The trick here is to use a combination of TSCount and TSFind to locate the last square footage change. The TSFind function has an optional parameter for the "offset", which is a number of records to skip over. Zero is the default, and it means "don't skip over any records". If you have three records, then you need to skip over two of them to get the last record. In general, you skip over Count-1 records to find the last record.

Since the square footage change records are stored in chronological order, the last record has the most recent effective date. That’s the record you’ll need, so the above technique can be used.

For example, suppose that column B contains "Property" and column C contains "Primary Unit". You could paste the following function into column D to count the changes made to the total square footage of the unit:

· =TSCount("PROPERTY_PMP_RECORD_19","((UFPROP = '" & $B2 & "') AND (UFUNIT = '" & $C2 & "') AND (UFSFTYP = 'Total'))")

Then you could paste the following function into column E, which uses the TSCount in column D (minus one) to locate the total square feet:

· =TSFind("PROPERTY_PMP_RECORD_19", "UFDATE","((UFPROP = '" & $B2 & "') AND (UFUNIT = '" & $C2 & "') AND (UFSFTYP = 'Total'))", $D2-1)

My Office Connector formula does not return the correct results when the ID field has a space in it.

This can be caused if you forget to enclose the ID in single quotes (this applies to text IDs). For example:

“(TPAYID = ‘01’) AND (TEMPID = “ & A2 & “)”

Should be

“(TPAYID = ‘01’) AND (TEMPID = ‘“ & A2 & “’)”

Query Topics

How can I filter a JC transaction report for a date range in Office Connector?

Pick your table and fields as usual. When you get to the Where Clause panel, start by entering hard-coded values for the fields of interest. For example, in Job Transactions, you would be interested in a date range:

Then click the Show Advanced View checkbox so you can edit the Where text. Replace the data value “{d '2003-01-01'}” with a question mark “?”. The following example shows one of the dates replaced and one about to be:

How can I make a JC Transaction query return only cost transactions?

When you get to the WHERE panel of the Query Wizard, click the “Show Advanced View” box and enter the following text::

Where (TJOB = ?) AND (TRANTYP In ('AP cost','PR cost','JC cost','EQ cost','IV cost'))

Then click Finish and Excel will prompt you for the parameters. Click the cell you want to be the source of the data (alternately you can just type in the cell address). Then check the boxes so Excel will continue to use that cell and automatically refresh the query results when the cell value changes:

How do I modify a QueryTable after it has been inserted into Excel?

Currently, the only way that Office Connector supports the ability to modify a QueryTable is by allowing the user to edit the SQL text directly. In order to edit the SQL text for a query, you must have the Tech Support toolbar turned on. To turn on the Tech Support toolbar, click the Edit Preferences toolbar button:

That brings up the Preferences dialog box. Check the Display technical support toolbar buttons checkbox and click OK:

Now your toolbar should have the SQL editor button and other additional toolbar buttons:

If you click the SQL editor button, it will bring up the Select QueryTable dialog:

Double-click the QueryTable whose SQL text you wish to modify to show the SQL editor dialog:

Edit the text how you see fit and click Save. You should see the query immediately update in Excel. If the query goes blank, then you probably have a syntax error in your SQL text. Bring up the editor again and look for inconsistencies.

I refreshed my query and now it has jumped a few columns over to the right. What’s going on?

This is usually an indication that you have more than one query occupying the same space. When you refresh the data, Excel is pushing second query over to make room for the data in the first query. To resolve this issue, you need to delete the older query or queries. Here’s how:

Select the query from the Name Box drop-down list:
Right-click inside the selected area and pick Delete from the popup menu.
If you are working on a copy of the spreadsheet from before the column shift, then pick “Shift cells up” when prompted, otherwise pick “Shift cells left”.
After that, it’s a good idea to delete any data from below the top row of functions and refresh the workbook.

I get error 438 - "Object doesn't support this property or method" when refreshing data.

This can happen if your spreadsheet contains a query and the query contains one or more parameters and you do not have MS Query installed. To resolve the issue, go to Add/Remove programs and reinstall Office choosing the Custom install option and making sure to check the MS Query option.

How do I make an Office Connector query show records where a date field is blank?

Date fields in queries are special in how they handle blank values; you must use the Is operator (instead of =) and the special value NULL. For example, to show a list of active employees, you want to show records where the termination date Is NULL. In the Query Wizard, click the advanced view and replace the comparison operator and date value from

Where ((ETERM = {d '2004-06-13'}))

Where ((ETERM Is NULL))

Table of Contents

Installation Issues

Do I Need to Uninstall Office Connector Before Upgrading?

I installed Office Connector. It shows up in Add/Remove Programs, but it does not show up in Excel. What happened and how do I fix it?