Excel to Excel problem

Excel to Excel problem

Post by res08d8 » Sat, 02 Feb 2002 15:16:55

I am trying to use a package I created in SQL Server DTS Designer and saved
as a VB bas file and then customized the VB. I am moving data from one Excel
spreadsheet to another. I have turned the DTS code into a VB procedure and
am calling it as part of a larger utility app. Basically, the app breaks up
a national Excel report into smaller regional Excel reports. I am passing a
strRegion argument to my DTS VB procedure and calling it from a loop outside
of the DTS code.The loop goes through each region and each time calls my DTS
VB procedure with the variable for that region number. The result of the
loop is a HoldBin.xls which contains a sheet for each regional report. Then,
my app moves those sheets into respective .xls's for posting on an intranet.

I am using Unitialize after Execute on the package at the end of each loop,
and I am also destroying my DTS objects at the end of each loop. And, I am
successfully moving the data and creating the separate sheets in HoldBin.xls
. The problem is that right after the DTS code executes, my app moves to a
formatting loop to format the new sheets one by one and then move them to
their respective regional xls's. It is in the beginning of this code that I
get an error when I try to do a Workbooks.Open ("HoldBin.xls") . I have
gotten several different errors here. I've gotten "Automation error - Server
threw back an error", and "Open method failed for Workbooks object", and
"RTE ... (80010108) ... The object invoked has disconnected from its
clients." The result is HoldBin.xls is corrupted. I cannot open it  because
of a memory conflict message.

I think that the problem lies in the fact that DTS is not letting go of the
xls file. I have tried the ReleaseConnection method for each of my
connections at after the Execute and before the Unitialize in my loop, but
it does not help. I have stopped my code and noticed that Excel.exe is
running in Task Manager after the DTS code is finished. What's even more
puzzling is that my code "as is" worked for several weeks!!! Maybe one out
of ten jobs failed with one of these errors, but 9 times out of 10, it
worked perfectly. However, two weeks ago, that reversed for some reason.
Now, rarely it works, usually when I first boot the machine.

Any ideas? There must be something about the DTS VB code that I'm missing or
could tighten up.



Excel to Excel problem

Post by Gerald Hopkin » Sun, 21 Apr 2002 14:58:59

I answered my own question. I found that the problem I was having was
due to Excel's use of named ranges. When DTS to Excel occurs, the DTS
not only creates a sheet with name x, it also creates a named range x
for the data that gets transferred to that sheet. I don't know why, but
it does. So, if you come back to that same workbook and try to DTS to
the same sheet with the same name for the sheet, an Excel error occurs
because of the duplicate named range. I got around this by using part of
the integer equivalent of a timestamp to add to the end of my target
sheet name to make every sheet name unique in my spreadsheet. This
eliminated my Excel DTS problem.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


1. viewing calculated members in Excel PivotT with Excel 2000 and Excel XP


I can see the calculated member in the dimensional drop-down in Excel 2000, but
when I select only this (deselect the others) the results are empty.  (I know
that number [measures] are there and non-zero because I can see them [for the
calculated member] in Analysis Mgr).

Installed Office XP on my machine (it installs over W2000 - doesn't require
Windows XP).  Not only can I see the same calculated member but it appears
automatically and all the numbers are there.

  Any ideas?  How does one see calculated members in Excel 2000 PivotT?


2. Using xp_regread

3. Pivot table in Excel 2000 vs Excel XP

4. Help !!!!! "Server is in an incompatible state"

5. Excel 2002 v Excel 2000 - Calculated Members

6. DTS Export to Excel with Detailed Excel Formatting

7. Saving Excel 97 for Excel 5.0

8. SQL Drillthrough in Excel again......in Excel 2003

9. import from excel with variable excel files

10. Excel Add-in and internal name of Excel Application

11. changing Excel version in an Excel-VB6.0-SQLServer7.0 applcation