Automating Excel from VB

[mcc]

Hi,

We are facing some problem while automating excel application with Visual Basic. We are addressing the problem below and want to share your expertise to overcome the problem.

The requirement was: We need to write to an existing excel file (different sheets) programmatically from database tables and open the excel for view.

What we did: We have a VB application through that we are trying to write to an existing excel file. The excel file is connected through a DataControl objects with datasource as the excel file and RecordSources as the required sheet names. Now we are writing these RecordSets by invoking edit method. This is writing to the excel file fine. But the problem is we are unable to write to the RecordSets ( for excel) without opening the excel file. So we are opening the excel file using Shell Function. So the processing cycle now became:

1. Opening the excel file using SHELL.
2. Loading the form that has excel updating routine. (Excel application goes to the back)
3. Running the creation process to update excel file
4. Updating complete
5. Unloading the Form
6. Excel application comes to the front
7. User saves it and closes
8. Return to main menu

The process runs fines, but some times even if we close the excel (step 7) it remains in the task and machine hangs.


dated 14/08/2002
With the above problem if i can do the editing job without opening the excel file physicvally then there is no question of excel getting hanged.
Is this possible?