Which version of Excel are you using. I am using 2000 and could import a
spreadsheet whilst I also had it open

---------------------------------------
HOW TO: Transfer Data to Excel Using SQL Server DTS

----------------------------------------------------------------------------
---
The information in this article applies to:

- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server version 7.0
----------------------------------------------------------------------------
---

IN THIS TASK

- SUMMARY

- Requirements
- Choosing a Destination File
- Choosing a Destination Table
- Pitfalls

- REFERENCES

SUMMARY
=======

SQL Server Data Transformation Services (DTS) is a very powerful tool that
makes
it easy to transfer data between OLE DB data sources, transforming that
data if
necessary in the process. This article discusses the use of the DTS
Import/Export Wizard to export data from SQL Server or another data source
to a
Microsoft Excel worksheet, and the pitfalls to avoid. Many of the same
considerations will apply if you are configuring your own "Transform Data
Task"
in the DTS Designer.

Requirements
------------

The following list outlines the recommended hardware, software, network
infrastructure, and service packs that you will need:

- Microsoft SQL Server 2000 or 7.0 installed on a compatible Microsoft
Windows
operating system

- Microsoft Jet 4.0 and its related files, as installed by MDAC version
2.1 or
2.5, or by another product

This article assumes that you have at least basic familiarity with the
following
topics:

- SQL Server

- Data Transformation Services

- Excel worksheets

Choosing a Destination File
---------------------------

1. Launch the DTS Import/Export Wizard and select a data source on the
"Choose a
Data Source" tab. You will then come to the "Choose a Destination" tab.

2. In the "Destination" dropdown list, select "Microsoft Excel 97-2000" as
the
destination database type. Use this same type for Excel 2002 (Office XP).

3. In the "File name" textbox, click on the ellipsis and navigate to an
existing
Excel workbook file. This file must not be open in the Excel application
as
you step through the wizard. If Excel is installed on your computer, you
can
create a new Excel file at this point without leaving the wizard by
right-clicking in the "Select file" dialog and choosing "New - Microsoft
Excel Worksheet".

Choosing a Destination Table
----------------------------

1. After the "Choose a Destination" tab discussed previously, and the
"Select
Table Copy or Query" tab, you come to the "Select Source Table(s) and
View(s)" tab.

2. In the "Source" column, select the table(s) and view(s) you want to
export to
Excel.

3. In the "Destination" column, by default the wizard fills in a destination
table with the same name as the source table. (In fact, this creates
both a
worksheet and a named range with the same name in the destination
workbook;
however DTS uses the named range in most circumstances.) You can also
choose
an existing worksheet or named range; the names followed by a $, such as
Sheet1$, are worksheet names.

4. In the "Transform" column, when you click the ellipsis (...), will open
up an
additional dialog where you specify whether you want to:

- Create the destination table (the only available option if it does
not yet
exist, and unavailable if it already exists), with the additional
option
to drop and recreate the table;

- Delete and replace the existing destination rows (however this option
will
fail with Excel and cannot be used); or,

- Append the new rows to the existing rows.

5. The remaining steps in the wizard allow you to save and to run the DTS
Package. If you want to export new or changed data on a regular basis,
save
and optionally schedule the package before leaving the wizard.

Pitfalls
--------

Pitfalls in Selecting the Excel File:

- The workbook must not be open in Excel while you work with the DTS
wizard.

Pitfalls in Selecting the Excel Table:

- If you choose the "drop and recreate the destination table" option, the
"drop" step will fail the first time you run the package, because the
table
does not exist; however the export will succeed.

- If you choose "create the destination table" without the "drop and
recreate"
option, the "create" step will fail on subsequent executions because the
table already exists; however the export will succeed.

- A CREATE TABLE statement executed against Excel, such as the statement
generated by the wizard, creates both a worksheet and a named range with
the
same name. However, DTS works with the named ranges unless you specify
otherwise. You can view these named ranges in Excel by choosing "Insert -
Name - Define" on the Excel menus.

- You cannot choose to delete and replace existing rows in the Transform
dialog, because Excel worksheet rows cannot be deleted through OLE DB.

- If you manually blank out the exported data in the destination worksheet,
then export the data again, the new data will be appended below the blank
rows, because the driver is looking at the saved definition of the named
range and expanding it for the new rows. If instead you delete the entire
rows of old data in the worksheet, this will not occur, because deleting
the
rows changes the saved definition of the named range. However it is
preferable to use the "drop and recreate" option to replace the existing
data.

REFERENCES
==========

For additional information on issues encountered when using Excel as a
database,
please see:

Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA

For additional information on known issues encountered when using Excel
with DTS,
please see the following articles:

Q236605 PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data

Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error

Q231180 BUG: Import Wizard Fails if Excel File Open During Import/Export

Q207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns

For additional information on using the SQL Server DTS feature, see the
following
Books Online articles:

Complex Transformation Sample from SQL Server to Excel
Creating a DTS Package with the DTS Import/Export Wizard
DTS Driver Support for Heterogeneous Data Types




--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Karen Grube" <klgrube (AT) aol (DOT) com> wrote