1. Excel to Excel problem
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.
2. update through view
3. problem with excel - problems obtaining data
4. Handling Errors Inside Stored Procedure
5. Excel Problem
6. Entity types and supertypes?
7. Export to Excel - problem with NULL
8. IIS, Database access and local (CD Rom) Help!?
9. Excel Problem
10. Dimension and Excel problem
11. DTS Transform Data Task to Excel Problem
12. Newbie Excel Problems
13. SQL importing from Excel problem