|
| |
Overview |
This tutorial will describe how to retrieve data from a Microsoft Excel spreadsheet.
There are two main options available:-
- Import a copy of the Excel spreadsheet and store it in a Group (Tutorial: Importing from Excel)
- Create an External Database Group that links directly to the data in Excel
Note:
- This option will allow the user to add new recipient(s) or edit data
from the existing recipient(s) but due to the limitation of Microsoft Excel, this option will not support removal of
recipient(s) i.e. If you want to delete recipient(s), you will need to delete directly from the Microsoft Excel
Spreadsheet
- If you would like to edit the Excel Spreadsheet within GroupMail, please
make sure that you have used "Optimistic (Default)" as the 'Lock Type'
Here, we will describe how to create an external group that links to the spreadsheet directly.
|
| |
Step 1: Sample of the Excel spreadsheet |
|
Let us assume that we want to link to a Microsoft Excel file that is stored at "C:\Customers.xls"
and its Worksheet is called "Contacts" as shown in Figure 1.
|
| |
Figure 1
|
| |
| Back To Top |
| |
Step 2: Create a new Group |
|
Open GroupMail 5 and click "Create a New Group" from the Home Workspace as shown in Figure 2.
|
| |
Figure 2
|
| |
|
Select "External Database - Recipient information is retrieved from an external Database system" and
click "Next" as shown in Figure 3.
|
| |
Figure 3
|
| |
|
Click "New" to create a new Database Connection as shown in Figure 4
|
| |
Figure 4
|
| |
| Back To Top |
| |
Step 3: Setup a new Database Connection |
|
On the 'Create a New Database Connection' screen, enter a descriptive name and click on "Wizard"
|
| |
Figure 5
|
| |
|
Select "Microsoft OLE DB Provider for ODBC Drivers" and click on "Next >>"
|
| |
Figure 6
|
| |
Select "Use data source name" and select "Excel Files" from the list
At point 3 'Enter the inital catalog to use:', enter the path of your spreadsheet. In the example
below, this is C:\Customers.xls
Click "OK" to return to the 'Create a New Database Connection' screen
|
| |
Figure 7
|
| |
Select "Static" from the 'Cursor Type' list
Select "Optimistic (Default)" from the 'Lock Type' list if you are going to make any changes to the
recipient's data directly from the group.
However, here we will select "Read-Only" as the 'Lock Type'. That means you will not be able to make
any changes of the recipient's data directly from the GroupMail group. If you want to edit the data, you will need to make
the changes directly from the Microsoft Excel Spreadsheet.When you are using this lock type, please make sure you have
closed down your linked worksheet before trying to view or send a message to this group.
Click on "OK" to return to the 'Create a New Group' screen.
|
| |
Figure 8
|
| |
| Back To Top |
| |
Step 4: Link the Database Connection to a Group |
|
On the 'Create a New Group' screen, select our newly created Database Connection and click on "Next"
|
| |
Figure 9
|
| |
|
As shown in Figure 10, "Specify records using SQL" will be selected by default. Click on "[SQL]" to
enter a query.
|
| |
Figure 10
|
| |
Enter a query into the text box area.
To specify a worksheet within this query, use the worksheet name followed by a dollar sign and
surrounded by square brackets. For example: Select * from [Contacts$]
Here, "Contacts" is the name of the Worksheet required.
|
| |
Figure 11
|
| |
Click "OK" to close the 'Advanced SQL Query' window and return to 'Create a New Group' screen
Click on "Next" to proceed to the next step
Select a worksheet column that contains the Email address for the 'Email field:' field and click
"Next" as shown in Fingure 12
|
| |
Figure 12
|
| |
|
Enter a name for the Group and click "Create" to complete the process
|
| |
Figure 13
|
| |
| Back To Top |
| |
Tips: How to Create a Spreadsheet that will be Compatible with
GroupMail |
Your spreadsheet should satisfy the following criteria:-
- The first row should contain a list of column names. These names will be used as a Field
Name within a group
- The column names should start with a letter and should not contain any spaces
|
| |
| Back To Top |
| |
Tips: Different Meanings of Microsoft Excel Error
Messages |
Cannot Open a Password-Protected Workbook
If the Excel workbook is protected by a password, you will not be able to open it for data access,
even by supplying the correct password with your connection settings. If you try, you will receive the following error
message:
Could not decript file.
|
| |
Specify a Worksheet
If you omit both the dollar sign and the brackets, you will receive the following error message:
The Microsoft Jet database engine could not find the
object...
If you use the dollar sign but omit the brackets, you will receive the following error message:
Syntax error in FROM clause.
If you try to use ordinary single quotes, you will receive the following error message:
Syntax error in query. Incomplete query
clause.
|
| |
Delete
If you try to delete an entire record at once, you will receive the following error message:
Deleting data in a linked table is not supported by
this ISAM.
If you try to delete the value in a cell containing an Excel formula, you will receive the following
error message:
Operation is not allowed in this context.
|
| |
| Back To Top |
