[dba-VB] C# Excel automation

Is this VBA or VB.Net?

John W. Colby
www.ColbyConsulting.com

On 5/9/2011 1:02 PM, Dan Waters wrote:
> 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
>
>
>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>
>