Automating Excel from VB

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?

I'm not even clear on what your question is, but here's a way to get at excel that has never caused me any problems. give it a try.

Try this one ...

VB Code:

' Needs a reference to the "Microsoft Excel x.0 Object Library"
' from the Project menu > References options.
Private objXLApp As Excel.Application
 
Private Sub Form_Load()
 
    ' Use Late Binding Automation rather than the shell statement.
    ' This creates a "behind the scenes" instance of MsExcel.
    Set objXLApp = New Excel.Application
 
    With objXLApp
        ' Add a new workbook, then select the first sheet.
        .Workbooks.Open "C:\Path\File.xls"
        .Workbooks(1).Worksheets(1).Select
 
        ' Using the CopyFromRecordset() procedure built into Excel, paste
        ' your table into the first cell of the first worksheet.
        .Worksheets(1).Range("A1").CopyFromRecordset YourRecordsetVariable
        
        ' Now show MsExcel to the user.
        .Visible = True
        
        ' If you want, you can now save the workbook:
        .Workbooks(1).SaveAs "C:\path\File.xls"
    End With
    
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
 
    ' If the instance of Excel we made is still open when this program is exited,
    ' close it & unload it's reference taken up in the computers memory.
    If Not (objXLApp Is Nothing) Then
        Set objXLApp = Nothing
    End If
 
End Sub