DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.
Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John
|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John
|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John
|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John
|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c
|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was t
o
> use the Delete option in the Transformation window of the wizard. I rebui
lt
> the DTS based on that idea, but get an error message about "Deleting data
in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in th
e
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that usin
g
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way
to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes.
I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is wha
t
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in
the
> range name. Subsequent runs of the DTS appear to be using the last line o
f
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the ol
d
> data. The old data is cleared leaving blank rows, but the new data is sti
ll
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part o
f
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I don't think it is the lack of excel that does this, as this also occurs
on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and
if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/micr...b95633f7391968c|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part
of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
Thanks
Hi
There are a couple of mentions for your error message
http://search.microsoft.com/search/r...=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the end
of the cells you have previously cleared.
John
"Stephen" wrote:

> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
ThanksHi
There are a couple of mentions for your error message
http://search.microsoft.com/search/...a=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the en
d
of the cells you have previously cleared.
John
"Stephen" wrote:

> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>