FAQs
1. “Firebird/Interbase Client DLL is Not Installed” – I get this error when opening QueryCell! What now?
QueryCell uses the Firebird database in order to provide SQL access to Excel Data.
If you recieve the error described some possible fixes are described below. Also please feel free to contact us at [email protected].
Make sure you close all Microsoft Office applications (Excel, Word, Access etc) before installing QueryCell
If Microsoft Office applications were running when you installed QueryCell please uninstall it (via [Control Panel] -> [Add or Remove Programs) and then re-install it making sure all Microsoft Office Applications have been closed.
Make sure all required files have been copied to the Excel directory
The QueryCell installer should copy the following files to the QueryCell installation directory and to the Excel directory.
If they are present in the querycell directory (eg. C:\Program Files\QueryCell) but not the Excel directory (eg. C:\Program Files\Microsoft Office\Office12) you can manually copy them to the Excel directory and restart excel to see if that addresses the problem.
If files with the same name already exist in the Excel directory you should not overwrite them.
The Installer obtains the Excel directory by looking at the registry key “SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe” in the HKEY_LOCAL_MACHINE section.
Microsoft.VC80.CRT.manifest – Microsoft Visual C++ runtime related
fbclient.dll – Firebird Client Related
icudt30.dll – Firebird Client Related
icuuc30.dll – Firebird Client Related
icuin30.dll – Firebird Client Related
firebird.conf – Firebird Client Related
firebird.msg – Firebird Client Related
msvcp60.dll – Microsoft Visual C++ runtime related
msvcrt.dll – Microsoft Visual C++ runtime related
msvcp80.dll – Microsoft Visual C++ runtime related
msvcr80.dll – Microsoft Visual C++ runtime related
2. Table Names – Single character table names ? I want real names!
You can change the table name of any of the regions in the regions grid.
Click on the Name in the Name column of the regions Grid, backspace the existing table name and type in the new name you want for this table.
Please note that if you click on the Refresh Regions Button QuerCell will re-define all the regions by searching through the open worksheets, this will mean that your changed table name is lost! You can overcome this by marking the region as a ‘custom table’. You do this by checking the Custom Table Checkbox in the regions grid.
Once a region is checked as a Custom Table QueryCell will not remove or modify that region when you hit the Refresh Regions button. Also QueryCell will assume you are manually looking after that region, it won’t change column names of that table if they change in the excel data.
3. Query Results Replaced – Every time i run sql the rows from the last run are replaced. Is there any way to save the last rows?
By Default all query results are placed in a worksheet named ‘QueryResults’.
Each time you successfully run a query this worksheet will be cleared and then used to store the new result set.
There is an option called “Output results to a new Worksheet’. If you select this option then each new query will create a new worksheet to contain it’s result set. So you will not lose the result set of the last query.
You can get to this option by clicking the ‘Options’ menu item.
4. SQL Inserts – How can you create SQL Insert Statement ?
To get data you have in excel into a relational database you have a few options. One of the simplest options is to generate SQL insert statements for the data, you can then copy and paste the insert statements into your database client. QueryCell makes this easy by providing the SQL Generator.
The SQL Generator is also demonstrated towards the end of the QueryCell.com homepage Introductory Video.
This Microsoft Webpage provides some alternative methods for getting Excel data into a Relational Database.
The SQL Generator simplifies the process of generating SQL insert statements a lot, but here is some additional information that may help you generate SQL insert statements using SQL itself.
Here is a simple example of a SQL statement you can run that will produce SQL insert statements.
SELECT
‘INSERT INTO ORDER_ADDR(TOWN_NAME, CUST_NUM, RE_AMOUNT, FOUNDING_CUST) VALUES (”’ ||
SUBURB || ”’ , ”’ ||
A.CUST__ || ”’ ,’ ||
A.RE_AMT || ‘,” ‘ ||
A.ORIGINAL || ‘ ”);’ FROM A
Things to note :
i – The column names of the database table you wish to insert into are hard-coded in this query. TOWN_NAME, CUST_NUM, RE_AMOUNT, FOUNDING_CUST
ii – The column names you are selecting from can be, and in this example are different from the names of the columns in the target database tables
iii – This query uses the string concatenation operator || (a double pipe) to join strings and columns together
iv – The Most complicated thing in this query ? Escaped single quotes! The single quote character is used to mark the start and end of string literals, since we need to include this character itself in our query we escape it, to escape a single quote character simply add another.
Example : ‘john wanted Angela’s attention’ would not work as a string, because we are using single quotes to mark the start and end of the string but the string itself contains a single quote character. The single quote in the word Angela’s doesn’t mark the end of the string, it is merely part of the string.
In order to overcome this when a single quote exists inside a string it should be escaped.
You escape a single quote by adding another single quote.
‘john wanted Angela”s attention’ is correct.
5. Delete SQL Templates – How do I delete an entry in the SQL Template Library ?
i Click the SQL Template Library entry you wish to delete
ii Press the keyboard delete button
6. Multiple Workbooks – Working in one workbook can I reference data in one or more other workbooks?
Yes you can. Simply refer to the regions of data in any worksheet by the table name listed in the Regions Grid.
7. Closing Workbooks – Can those external workbooks be closed? Or must they be open?
They must be open. You cannot query regions of data that are no in an open Workbook.
Closing an excel file will result in it’s data regions being removed from the Regions Grid, and you will no longer be able to refer to them in your SQL Queries
8. Pivot Tables – Can I use your product against relational sources? That is, can I use your product rather than using PivotTables?
Yes, in some circumstances you can achieve the same result by using a PivotTable in excel or by writing a SQL statement and running it using QueryCell.
9. Excel Array Forumlas – Having created a table with my SQL, do you offer functions that return single values to cells in my spreadsheet? Or do I continue to use array formulas, SUMPRODUCT, SUMIF, SUMIFS, etc?
If you wanted to easily output a single value in a cell or use it in a formula in a cell you would continue to use array formulas.
10. Distribution of Reports – Does Everyone I send My Report To Need QueryCell?
No. When you query and return data using QueryCell it is stored in the Excel file just like any other data. You can then save that file and send it to people without QueryCell installed. They will be able to view and manipulate that data you have created using QueryCell just like any other Excel data.
11. Can I run QueryCell on Mac Computers?
No, QueryCell is only available for computers running Windows. If you currently transfer Excel files from Windows to Macs however you will be able to transfer files containing data generated by QueryCell in exactly the same way.