LeonBambrick at 13:05, 10 June 2010

2010-06-10T13:05:38Z

LeonBambrick: New page: Multi-billion dollar companies using Excel for their most crucial tasks. And so can you. The following is a tutorial on how to use an excel file as the datastore behind your lookups. The...

2010-06-10T12:53:46Z

New page: Multi-billion dollar companies using Excel for their most crucial tasks. And so can you. The following is a tutorial on how to use an excel file as the datastore behind your lookups. The...

New page

Multi-billion dollar companies using Excel for their most crucial tasks. And so can you.

The following is a tutorial on how to use an excel file as the datastore behind your lookups.

The excel spreadsheet we're going to use is very simple and looks like this:

[[Image:Excel_As_Database_0.png]]

In TimeSnapper we're going to configure the application to use that spreadsheet as the source of our lookups.

On the options form select the 'integration tab' and look at the task lookup integration area.
You can set the connection string right there, or continue to details and paste it straight in, if you prefer.
[[Image:Excel_As_Database.png]]

Select the ellipsis beside the connection string, and you can use the 'Datalink properties' wizard to describe the connection you want to make.
[[Image:Excel_As_Database_2.png]]

(Alternatively, just write the connection string and paste it straight in. It may look something like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Leon\Documents\Excel_Example2.xls;Extended Properties=Excel 8.0;Persist Security Info=False

(Your file name and location may of course be different)

The provider we're using for excel is the 'Microsoft.Jet.OLEDB.4.0'

[[Image:Excel_As_Database_2_1.png]]

For the 'database name' enter the name and path to your excel spreadsheet.

I'm told that this provider can connect to both xlsx and xls files. Personally I haven't succeeded at connecting to an xlsx file, so i just saved as the older .xls file (Excel 97/2003 format).

[[Image:Excel_As_Database_2_2.png]]

The only real 'trick' to this procedure, is knowing that on the 'All' tab you have to give it an 'extended properties' value of 'excel 8.0'

[[Image:Excel_As_Database_2_3.png]]

Having done that, if you go back to the connection tab you should be able to press 'Test Connection' and have a success message.

One trap here: because excel locks any file that's open, you have to make sure the spreadsheet is not open in excel.

[[Image:Excel_As_Database_2_4.png]]

Now we're able to connect to our spreadsheet and use it for lookups.

We'll create one lookup called 'tasks' that will select from the first column of the first sheet in the spreadsheet.

[[Image:Excel_As_Database_3_0.png]]

Make sure the lookup is enabled, give it a caption of your choice, and in the 'Column from Query' textbox, tell it the heading name of the column whose value you want to display in the resulting lookup.

The query could just as well be 'Select * from [Sheet1$]' but we're going to be specific and name the column we're interested in.

With all that in place, we can test the query.

[[Image:Excel_As_Database_3_1.png]]

If everything has gone well then we'll see a small form that shows all of the resulting values from that column.

[[Image:Excel_As_Database_3_2.png]]

If we select one of the values, we'll see a preview of what has been returned.

(In this case what we selected and what we returned were the same thing. But trickier configurations can also be performed)

[[Image:Excel_As_Database_3_3.png]]

After that I went through and disabled the other five lookups that are available so they wouldn't clutter up the 'new flag' dialog.

Now, when you create with a flag type of 'Task' dialog there is one task lookup control, and selecting the ellipsis gives you a choice of the items in your excel spreadsheet.

[[Image:Excel_As_Database_3_4.png]]

@@ Line 1: / Line 1: @@
-Multi-billion dollar companies using Excel for their most crucial tasks. And so can you.
+Multi-billion dollar companies use Excel for their most crucial tasks. And so can you.
-The following is a tutorial on how to use an excel file as the datastore behind your lookups.
+The following is a tutorial on how to use an excel file as the datastore behind the lookups functionality in TimeSnapper.
-The excel spreadsheet we're going to use is very simple and looks like this:
+The Excel Spreadsheet we're going to use is very simple and looks like this:
 [[Image:Excel_As_Database_0.png]]

Excel lookups - Revision history

LeonBambrick at 13:05, 10 June 2010

LeonBambrick: New page: Multi-billion dollar companies using Excel for their most crucial tasks. And so can you. The following is a tutorial on how to use an excel file as the datastore behind your lookups. The...