FAQ

Frequently Asked Questions About XLhub

FAQ

Frequently Asked Questions About XLhub
What is a hub?
A hub connects multiple spreadsheets together. It is the place on the network to which data from the spreadsheet can be saved. Other spreadsheet users can retrieve and also save their data to this central location.

In technical terms, the hub resides in a database and uses the database program’s security and data storage features.

How does XLhub work?
XLhub establishes a two-way connection between Microsoft Excel and a SQL Server database. With the two-way connection, data that is in an Excel table can be saved to the database and data that is in the SQL Server database can in turn be retrieved into an Excel table. Reports from all users’ data can be created easily because all of the data is stored together in the database.
Do you have a free trial?
As of Jan 29, 2014, we have discontinued the Free Trial Download and significantly lowered the price or XLhub. The software is offered as is. Free technical support is available if there are any issues during your use of XLhub.
What are the system requirements for using XLhub?
Before you can use XLhub, you need to make sure your computer meets the following requirements:

  • Windows XP, Windows Vista or Windows 7
  • Microsoft Excel 2007 or Excel 2010
  • Local Administrator Permission for installing XLhub on your machine
How do I check which version of XLhub I am using?
To check which version of XLhub you are currently using, open Excel and click on the XLhub ribbon. Click the Help button in the Admin group. The version number is displayed in the dialog box.
How do I install a new version of XLhub on my PC?
To install a new version of XLhub on your PC, go to the XLhub ribbon. Under the Admin section click Utilities and then click Check for Updates. A dialog box telling you what version of XLhub you are using will appear. At the bottom of this box there is a “Check for latest updates” button. Click that button. If there is an update a box telling you the most recent version and your current version will appear. This box will ask if you would like to install the new version now. Click “Yes.” The new version will be downloaded. You will have to restart Excel to use the new version.
Is XLhub installed for single users or all users on the computer?
XLhub is installed for all users on the computer.
How can I find out which hubs have been created?
To find out which hubs have been created go to the XLhub ribbon, under the Admin section click Utilities and then click Hub Administration. A dialog box called Hub List will appear. This box shows all of the hubs that have been created, their Excel table name, the database server name and the database name.
Why has my XLhub ribbon disappeared and how do I get it back?
If your XLhub ribbon has disappeared you may have changed your Excel Options Add-Ins settings.

To check the Add-Ins settings in Excel 2007:
In Excel, click on the Microsoft Office Icon in the upper left corner of the screen.
At the bottom of the drop down menu, click Excel Options.

On the left, click Add-Ins.
Scroll to the bottom of the window and check for XLhub under the disabled hub add-ins. Double click XLhub and enable it.
Save the setting, close and re-open Excel. Your XLhub ribbon should be restored.
To check the Add-Ins settings in Excel 2010:
In Excel, click on the File tab. It is the first tab at the top of the Excel window.
In the left hand side-bar, choose Options.A dialog box will open called Excel Options.
On the left hand side-bar click on Add-Ins. In the Manage drop down at the bottom of the box choose COM Add-Ins and click Go.

Another dialog box called COM Add-Ins will open. Locate XLhub in the list of Add-Ins available and be sure that the check box next to it is selected.

Click OK to enable XLhub.

Save the setting, close and re-open Excel. Your XLhub ribbon should be restored.

Why can’t I hub my table?
If you cannot hub a table, please check to see if:

You have permission as a user to hub a table. Please contact your Administrator to find out your permissions.
The range has been converted to a table. (?) If this is the problem, with the cursor inside the desired table area, press CTRL + t to convert the range to a table.
The tablename provided has been used for another hubbed table. (?) If the tablename has been used before, change the tablename and save the Excel file to hub the table.
The cursor is inside the table that you wish to hub. If the cursor is outside the table range, move the cursor inside the table and hub the table.
If none of the above are reasons you cannot hub your table, please contact your Administrator or Metric-X for troubleshooting.

What is the “Row Level Security” checkbox?
Row Level Security is used to prevent other users of the hubbed from seeing each other’s data. When using Row Level Security, XLhub limits the data visible by looking up the database security privileges of the user. A user is only able to retrieve versions of the hubbed table saved by themselves when Row Level Security is enabled. When they create reports, they are able to see all users’ saves. The hub creator can modify the permissions given to each user in SQL Server.
How can I protect my XLhub data from other users?
The XLhub team recommends that you protect your data. The following security measures can be taken before you hub a table to ensure protection:

Lock only the column headers.
Highlight the entire worksheet. Go to Format Cells, click Protection and uncheck Locked. Click OK.
Now highlight only the row containing the column headers. Go to Format Cells, click Protection and check Locked. Click OK.

Give users permissions only to modify rows.
This should be the last step you complete before you hub your table. Be sure that the tablename is what you need it to be before proceeding.
In the Review tab click on Protect Sheet. A dialog box containing many options will appear.
Check the boxes so the settings match those shown in the figure below.

Click OK. The worksheet will now be protected.
When you create the hub, be sure to check Enable Sheet Protection in the first XLhub Wizard box. You will be prompted to enter a password. If you do not share the password with other users, they will only be able to do the tasks indicated in the Protect Sheet box.

When I save the Excel file does XLhub automatically save the data to the hub?
XLhub never automatically saves your data to the hub. Saving the Excel file and saving to the hub are seperate actions. In order to write the data from the Excel table to the hub you must click the save button in the XLhub ribbon and provide a description. If you want to save the file you must use the Excel save button.
The Save and Retrieve buttons for my hubbed table are dim. Why is that?
There are two main reasons why your XLhub icons (Save, Retrieve, Reports buttons) are dim:

Your cursor may be outside the table range. In order to use the XLhub features for that table the cursor must be inside the table.

You may be disconnected from the hub. Go to the Table Management button and be sure that the Disconnect from Hub option is active. If the Connect to Hub option is active, click on it to connect to the hub. After the connection to the hub is made you should be able to Save and Retrieve your hubbed table.

I have made a change to my hubbed table and now I get an error message when I try to save or retrieve. How can I use the hubbed table again?
You are unable to Save and Retrieve because of the following XLhub stipulations:

The Excel table does not match the structure of the table stored in the server.
An invalid data type has been entered into a cell.
The XLhub error message contains very important information for helping you return the table structure to the original format.

In the below error message you can see that a column has been added or repositioned and the name of the column that was changed is Column1. Since you have this information you can delete the column that was added.

If you get a similar error message after you have deleted the offending column(s), follow the string at the bottom of the error message to see what the original table layout was and modify the table to match what is indicated there. You may need to restart Excel in order to regain use of XLhub features.

If an invalid data type has been entered into a cell you will see an error message similar to the one below.

This error message tells you which column has the data type mismatch and what data type was expected. You can use this information to adjust the table to match XLhub’s expectations.

I have received a shared hub but I can’t save or retrieve any data. How can I fix this?
XLhub could be missing some necessary information about your hub. Be sure that you have imported the hub. Open the file containing the shared table. If the import hub dialog box appears, complete the import process. If the import hub dialog box does not appear, click the Connect to Hub option under Table Management in the Admin group of your XLhub ribbon. When you save, close and reopen Excel, you should be able to Save and Retrieve.
How are reports generated by XLhub?
Reports in XLhub are generated by creating workbook connections between Excel and SQL Server. Traditionally to create reports from SQL Server you would have to create a connection manually using SQL Server Execute statements. XLhub does this internally saving you time and simplifying the overall process.
What size tables can XLhub handle?
There is no theoretical limit as to the size of an Excel table that can be connected using XLhub. We have tested the performance using tables as large as 100 columns and 100,000 rows. The smallest table XLhub can connect to a database is 1 row and 1 column wide.
How much content can I enter in each cell when I am using XLhub?
XLhub can hub tables with cells with as much content as Microsoft Excel allows. It can handle numbers with up to 15 digits, and character entries with up to 32,000 characters. The limit for formulas is currently 8,000 characters. However, once a table becomes an XLhub hub its maximum length (for text entries) becomes fixed. This maximum length is determined as part of the hubbing process and default to the maximum length of the longest entry in for each column at the time when the table is hubbed. If necessary the maximum length for a column can be adjusted in the column definition dialog that appears during the hubbing process.

Once the table is hubbed it will block all entries that exceed the maximum precision or length for each column. Any entries exceeding the pre-specified maximum length cannot be saved to the hub. XLhub imposes this restriction to ensure that your data is consistent and stored accurately in our database.

Why do formulas have to be consistent for a column when using XLhub?
Excel has a built-in feature for detecting and “correcting” inconsistent formulas. Normally, Excel displays an “inconsistent formulas” warning, and the user can choose to ignore it.

However, when data is Retrieved from the hub, Excel “corrects” the inconsistent formulas in any column. As a result of this action, the formula that is in the top row of the column will be written into every cell of that column.

If the formulas were inconsistent when the data was Saved to the hub, XLhub preserves those original (inconsistent) formulas for each cell. It Retrieves those formulas back into Excel, however, immediately upon Retrieve, Excel overwrites the formulas if it detects an inconsistency.

Why is it suggested that I use table formulas when using XLhub?
It is highly recommended that table formulas are used at all times because table formulas are the only type of formulas that will not alter the data if the table is moved or sorted.

XLhub Saves the formulas in the database, and then repopulates the formulas in the cells when the data is Retrieved.

For example, assume there is a table that begins with the top-left cell in A10, and cell C11 contains the formula “=A11+B11”, and the following happens:

Data is Saved to the hub
The table’s location is moved (for example, a five rows are inserted above the table), and now the table’s top-left cell is A15.
Now, the cell C16 contains the formula “=A16 + B16”
The previously saved version is Retrieved from the hub, and the existing data in the table is over-written
When the previously saved version is Retrieved, cell C16 will contain the formula “=A11 + B11” and this is not the intended result.

Why are numbers changed into dates in the table?
If a number is entered into a cell that is formatted as a date cell, Excel formats that cell as a date. Excel stores all the dates in number format and counts all the dates from 01/01/1900, so if you enter the number 21 in the column, Excel calculates it as the number of days since 01/01/1900 and displays 01/21/1900. To prevent Excel from changing the number to a date, highlight the cells that you wish to change and navigate to the Home ribbon. In the Number section use the drop down window to select the desired format in the Number group. You will now be able to enter numbers into the highlighted cells.
I already have a SQL table. How can I read/write to it from Excel?
There are at least two possible ways ….

a) Create an identical SQL table using the “Create New Hub” functionality, and move the data over to the new empty table

b) Customize a stored procedure.

Without any custom work, XLhub only reads from a SQL table that was created by XLhub. However, you can modify stored procedures to go beyond the basic functionality.

Create your first hub (“Hubname”), and take a look at how this stored procedure works: mxXLhub_Hubname_Get.

You might be able to customize it to suit your requirements. Don’t do this unless you clearly understand the XLhub metadata and how the save / retrieve functionality works in the database.

I have more questions or comments about XLhub. How can I contact someone about XLhub?
We welcome your comments, suggestions, and questions. Here is our contact information:

Send an email to xlhub@metricx.com
You may also call us at 1-248-690-5250

LIVE CHAT

Contact

Phone

248.690.5250

Mail

contact@metricx.com

Address

Metric-X, LLC Rochester, Michigan 48307