an EXCEL problem

an EXCEL problem

Post by Cai LiQin » Tue, 09 Jun 1998 04:00:00




Subject: [Visual Basic] a EXCEL problem
Newsgroups: comp.lang.misc
Organization: Chua's Family
Summary:
Keywords:

1) how to save the edited excel spreadsheet from the OLE object after
being automated?

2) how to display the microsoft excel application inside an OLE container?
        that is when the spreadsheet is clicked to be edited, the excel
        toolbars and menu will appear inside the ole container?

thanks and pls reply to the above address..

--
  ooooo       ooooo   ooooooo   ooooo oooo   oooo   ooooooo8
   888         888  o888   888o  888   8888o  88  o888    88
   888         888  888     888  888   88 888o88  888    oooo
   888      o  888  888o  8o888  888   88   8888  888o    88
  o888ooooo88 o888o   88ooo88   o888o o88o    88   888ooo888
                           88o8

--
  ooooo       ooooo   ooooooo   ooooo oooo   oooo   ooooooo8
   888         888  o888   888o  888   8888o  88  o888    88
   888         888  888     888  888   88 888o88  888    oooo
   888      o  888  888o  8o888  888   88   8888  888o    88
  o888ooooo88 o888o   88ooo88   o888o o88o    88   888ooo888
                           88o8

 
 
 

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.

Gerald

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