[dba-VB] C# Excel automation

Hi John,

I've had this issue too.  It's almost certainly that somewhere in your code
you are missing an Excel object qualifier.  Also take a look at the sequence
of opening and closing the Workbook and the instance of Excel.

Another issue is that if the PC already has Excel open, then the workbook
you create will be contained within the instance of Excel, and it turns out
that it's about impossible for your code to loop through the different
workbooks.  The best plan is probably to look for an instance of Excel and
close it with save = true.  Then open your workbook and you should be fine.
You'll need to tell your users what's going to happen (and maybe ask by
message if they want to continue), and/or put up a message if you need to
close/save an already open instance of Excel.

See this code:

'-----------------
Private Sub FormatSpreadsheetDemo(stgPath As String)

    Dim objExcel As Object
    Dim objWorkbook As Object
'    Dim objExcel As Excel.Application  '-- TEST
'    Dim objWorkbook As Excel.Workbook  '-- TEST

    If IsNull(stgPath) Or stgPath = "" Then Exit Sub
    
    Set objExcel = CreateObject("Excel.Application")

    '-- for troubleshooting
'    objExcel.Visible = true  '-- TEST
'    objExcel.ActiveWindow.WindowState = xlMaximized  '-- TEST

    Set objWorkbook = objExcel.Workbooks.Open(stgPath)
    
    '-- Format
    objExcel.Rows("1:1").Select
    objExcel.Selection.Font.Bold = True
    objExcel.Cells.Select
    objExcel.Selection.Font.Name = "Tahoma"
    objExcel.Selection.Font.Size = 10
    objExcel.Cells.EntireColumn.AutoFit

    '-- Freeze rows
    If InStr(stgPath, "Backups") = 0 Then
        objExcel.Range("A2").Select
    Else
        objExcel.Range("B2").Select
    End If
    objExcel.ActiveWindow.FreezePanes = True
    objExcel.ActiveWindow.WindowState = xlMaximized
    
    '-- Change column format
    If InStr(stgPath, "Activity") <> 0 Then
        objExcel.Columns("E:E").Select
        objExcel.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
        objExcel.Columns("G:G").Select
        objExcel.Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
    End If
    
    '-- Select upper left cell
    objExcel.Range("A1").Select

    '-- Save & Quit
    objWorkbook.Save
    objWorkbook.Close
    objExcel.Quit

    Set objWorkbook = Nothing
    Set objExcel = Nothing

    Exit Sub
    ErrEx.Bookmark = BOOKMARK_ONERROR

End Sub
'------------------

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, May 09, 2011 10:44 AM
To: VBA
Subject: [dba-VB] C# Excel automation

We are trying to automate Excel from C# but we are unable to correctly close
the excel object itself.  IOW it is still seen in the process list.

Paul has found information about marshal.releasecomobject and is doing that
for every object he is even touching but something is still not working.

Can anyone provide a simple example of opening a workbook, and reading a set
of cells out of a worksheet, then cleaning up and closing Excel.

TIA for your assistance.

--
John W. Colby
www.ColbyConsulting.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com