Question write to excel

[beginner_]

I have a refernce to the Excel 5.0 library set and see the code below.

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlRange As Excel.Range
        Try
            xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
            xlBook = CType(xlApp.Workbooks.Open(FileName), Excel.Workbook)
            xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
            xlRange = CType(xlSheet.Range("A1"), Excel.Range)
            xlRange.Value = "Test Value"
            
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Console.WriteLine(ex.InnerException)
        Finally
            xlBook.Save()
            xlBook.Close()
            xlApp.Quit()
        End Try

I prevously had different versions for this without the Ctype but the reuslt was the same -> eg file is not changed
(code is part of a dblclick event.)
xlBook =... is always the last line the code gets to.
Without the expection handling nothing happens, no error message.

Like this i get follwoing message:
Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))

A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
A first chance exception of type 'System.NullReferenceException' occurred in MyTest.exe

I'm pretty lost here. In vb6/vba this worked straight forward without any issues.

I also can't set xlapp = New Excel.Application
-> "New can't be used on an interface" message in the code window.
Strange is also that xlBook.Save() and xlApp.Quit() are underlined green in the finally part (but not xlBook.Close()).
-> used before assigned a value

Hope you can help me with this issue.

 

[cjard]

If youre trying to make an excel file, I can give you code to do that without excel needing to be installed (it makes an ExcelXML file from a dataset)

 

[beginner_]

Hi,

I would like to edit a already available excel file. (for office 2003 which is installed).

I also tried this with ado.net but there I get a different error.

 

[ggunter]

I'm kind of new to DotNet but I was able to access and manipulate existing Excel files using the "Microsoft.Office.Interop.Excel" objects in the Excel 11.0 library.

 

[beginner_]

I'm kind of new to DotNet but I was able to access and manipulate existing Excel files using the "Microsoft.Office.Interop.Excel" objects in the Excel 11.0 library.

Excel 11 did not work (yellow exclamation mark in Referneces after adding it). I had to partially reinstall office 03 to get this to work.

Now I have removed the Excel 5.0 reference and have the Excel 11 one.

I added the imports statement "Imports Microsoft.Office.Interop" so I can just type Excel.Application.

        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlRange As Excel.Range
' if i put xlBook =... in the try part xlBook.Save is marked green with the 'message variable used before it has been assigend a value. And I actually 'get that error. So therefore outside try. Why?

        xlBook = xlApp.Workbooks.Open(PathExcelFile) ' error here

        Try
            xlSheet = xlBook.Worksheets.Item("Sheet1")
            xlRange = xlSheet.Range("A1")
            xlRange.Value = "Test Value"

        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Console.WriteLine(ex.InnerException)
        Finally
            xlBook.Save()
            xlBook.Close()
            xlApp.Quit()
        End Try

I get following Error:
A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in MyProject.exe

or:

       Dim xlApp As New Excel.Application
        Dim xlBook As New Excel.Workbook ' seems to cause error (breakpoints)
        Dim xlSheet As Excel.Worksheet
        Dim xlRange As Excel.Range

        Try
            xlBook = xlApp.Workbooks.Open(PathExcelFile)
            xlSheet = xlBook.Worksheets.Item("Sheet1")
            xlRange = xlSheet.Range("A1")
            xlRange.Value = "Test Value"

        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Console.WriteLine(ex.InnerException)
        Finally
            xlBook.Save()
            xlBook.Close()
            xlApp.Quit()
        End Try

same error

hope you can help.

 

[JohnH]

When developing for the Office automation libraries make sure you have installed and use the official Primary Interop Assemblies, here's the 2003 PIA redist, this to prevent interop issues that may give errors like the one you just posted. If you check the project reference for Excel its path should be pointing to something like "c:\windows\assembly\Gac\ ....Interop.Excel.dll"

 

[beginner_]

When developing for the Office automation libraries make sure you have installed and use the official Primary Interop Assemblies, here's the 2003 PIA redist, this to prevent interop issues that may give errors like the one you just posted. If you check the project reference for Excel its path should be pointing to something like "c:\windows\assembly\Gac\ ....Interop.Excel.dll"

yes that is exactly where I got it from and i just ran the msi installer. the file path is ok too.

 

[JaedenRuiner]

       Dim xlApp As New Excel.Application
        [b]Dim xlBook As New Excel.Workbook ' seems to cause error (breakpoints)[/b]
        Dim xlSheet As Excel.Worksheet
        Dim xlRange As Excel.Range
[/QUOTE]


The above statement is the issue.

the Excel Application is the object that is core to everything.  Much like the VB XML library, which won't allow you to Create XmlAttributes or XmlElements on your own, you have to utilize shared functions and/or the document object that is creating the element.  the same is true with Excel/Word Interop.

the Excel Workbook doesn't exist on its own it is bound to the Application object, so in order to create a new workbook or work sheet, the application must come first.

so 
[code]
dim xlApp as New Excel.Application()
dim xlBook as Excel.WorkBook = Nothing
dim xlSheet as Excel.Worksheet = Nothing
dim xlRange as Excel.Range = nothing
try
   xlBook = xlApp.Workbooks.Add()
   xlSheet = xlBook.Worksheets.Item("Sheet1")
   xlRange = xlSheet.Range("A1")
   xlRange.Value = "Test Value"
catch ex as Exception
  Console.Writeline(ex.Message)
finally
  COMRelease(xlRange)
  CloseSheet(xlSheet)
  CloseBook(xlBook)
  CloseApp(xlApp)
end try

the referenced functions in there that you don't recognize are my clean up automatically for these circumstances, becuase VB (when paired with interop) is retar---- ahem...my apologies...challenged severely in recognizing that Close() and Quit() mean Now not later.

here is the code for them:

      Private Sub CloseSheet(byref sheet as Excel.WorkSheet)
         If sheet IsNot Nothing Then
            COMRelease(sheet)
         End If
      End Sub

      Private Sub CloseBook(byref book as Excel.WorkBook)
         If book IsNot Nothing Then
            Try
               book.Close(SaveBoolean)
            Catch
            End Try
            COMRelease(book)
         End If
      End Sub

    Private Sub CloseApp(byref app as Excel.Application)
         If app IsNot Nothing Then
            Try
               app.Quit()
            Catch
            End Try
            COMRelease(app)
         End If
      End Sub

   Public Sub COMRelease(ByRef o As Object)
      Try
         System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
      Catch
      Finally
         Free(o)
      End Try
   End Sub

   Public Sub Free(ByRef O As Object)
      O = Nothing
      GC.Collect()
   End Sub

Now, I tend to handle my Excel or Word automation in Wrapper objects, thus allowing for more simplified use for the specifics, like Application and Workbook are more or less likely to be a "Global" for the specific instance of excel automation.

Public Class ExcelWrapper
   private _app as Excel.Application
   private _wb as Excel.Workbook
   private _wks as Excel.Worksheet

     Public ReadOnly Property Opened() As Boolean
         Get
            Return (_app IsNot Nothing) AndAlso (_wb IsNot Nothing)
         End Get
      End Property

protected Sub StartApp(bShow as Boolean)
  if _app is nothing then
   _app = new Excel.Application
   _app.Visible = bShow
  end if
end sub

protected function Open(filename as string, bReadOnly as Boolean) as Boolean
  StartApp()
 If (_app IsNot Nothing) AndAlso (_wb Is Nothing) Then
            Dim books As Excel.Workbooks = Nothing
            Try
               books = _app.Workbooks
               _wb = books.Open(name, ReadOnly:=bReadOnly)
            Catch ex As Exception
               _ex = ex
               COMRelease(_wb)
               COMRelease(_app)
            Finally
               COMRelease(books) 'IMPORTANT If you assign a variable to an Interop Object, 
                  'you can't just set it to nothing, or else it gets lost in the midway, best to forcibly release it
            End Try
            Return Opened
         End If
end Function

      Public Sub Close()
         CloseApp()
         GC.Collect()
      End Sub

'I would insert the CloseApp, CloseBook and CloseSheet here
'but make them parameterless for App and Book just pointing to the
'specific private objects thus

      Private Sub CloseSheet()
         If _wks IsNot Nothing Then
            COMRelease(_wks)
         End If
      End Sub

      Private Sub CloseBook()
         If _wb IsNot Nothing Then
            CloseSheet()
            Try
               _wb.Close(_save)
            Catch
            End Try
            COMRelease(_wb)
         End If
      End Sub

      Private Sub CloseApp()
         If _app IsNot Nothing Then
            CloseBook()
            Try
               _app.Quit()
            Catch
            End Try
            COMRelease(_app)
         End If
      End Sub

'I also make my object IDisposable but you can just as easily
'override Finalize() and make sure [b]before[/b] you call MyBase.Finalize
'you call Me.Close() to make sure everything is closed out.

I have found the Marshaling services a absolute must, especially if you are creating and disposing Interop object willy nilly, as lo and behold after a few trial runs of your program, you open Task Manager and find 15 Excel.Exe Processes still running with like 6K allocated to them, so fair warning, always use that COMRelease function to verify the object is toast. And as commented in the code above, this means if you have

  dim wks as Excel.Sheets = _wb.Worksheets 'and yes the Worksheets
           'object is of type Excel.Sheets, NOT Excel.Worksheets, go figure :confused:

Even though that variable may go out of scope before it does you're better off

COMRelease(wks)

Hope This Helps