Type Initializer Error

[InDistress]

Howdy all,

I need some help because for the life of me I can't figure out why I'm getting this Type Initializer error.

I have an OpenFileDialogue control that once a user has selected a file calls a procedure from a public module I created. Effectively what this public module is used for is opening an excel spreadsheet that is then populated with data from my apps database - it's long and kind of complicated to explain so I won't elaborate unless you need me to.

The problem is - when the debugger hits my Call GetListOfWorksheets, it throws the following error at me:

System.TypeInitializationException - The Type Initializer for 'WindowsApplication1.TranslationCode' threw an exception
Data - {System.Collections.ListDictionaryInternal}
Helplink - Nothing
Inner Exception - {"Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154."}
- [System.Runtime.InteropServices.COMException]
etc. etc.

Now as far as I can fathom, it's a problem with the InteropServices. I've included the TranslationCode.vb code module and procedure I'm trying to call inside that module.

Imports Excel = Microsoft.Office.Interop.Excel
Public Module TranslationCode
    Dim ExcelApp As New Excel.Application
    Dim ExcelFilePath As String
    Dim ExcelWB As New Excel.Workbook
    Dim ExcelWS As Excel.Worksheet
    Dim AddSLAInfo, AddLGAInfo, AddSSDInfo, AddSDInfo As Boolean
    Dim SLAColumnIndex, LGAColumnIndex, SSDColumnIndex, SDColumnIndex As Integer
    Dim IF_PCColumnIndex, IF_SUBColumnIndex As Integer
    Dim IF_PCValue, IF_SUBValue As String
    Dim IF_MaxColumnValue As Integer
    Dim IF_RowCounter As Integer
    Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\data.mdb;Persist Security Info=True"
    Dim MyConnection As New System.Data.OleDb.OleDbConnection
    Dim oleCommand As New System.Data.OleDb.OleDbCommand
    Dim reader As System.Data.OleDb.OleDbDataReader
    Dim oledbStr As String
#Region ".....GET LIST OF WORKSHEETS FROM INPUT FILE"
 
    Public Sub GetListOfWorksheets()
        ExcelFilePath = Trim(UF009.UF009_TB_InputFilePath.Text)
        ExcelApp = New Excel.Application
        ExcelWB = ExcelApp.Workbooks.Open(ExcelFilePath)
        For Each ExcelWS In ExcelWB.Worksheets
            UF009.UF009_ComB_Worksheets.Items.Add(ExcelWS.Name)
        Next
    End Sub
#End Region

I know I'm missing something obvious, but I just can't see it.

Thanks in advance all.

 

[cjard]

uhm.. At a guess, the installed version of excel has some problem

As an aside, did you know you can turn a dataset into an excel spreadsheet without needing Excel to be installed? It might be an alternate solution to your problem...

 

[InDistress]

Hi cjard,

I fgured out the problem. If you look at the declerations section, there is a fundamental mistake being made:

Imports Excel = Microsoft.Office.Interop.Excel
Public Module TranslationCode
    Dim ExcelApp As New Excel.Application
    Dim ExcelFilePath As String
    Dim ExcelWB As New Excel.Workbook
    Dim ExcelWS As Excel.Worksheet

As you can see here, I'm dimming ExcelWB as a new Excel.workbook, but further down in my code I'm trying to set ExcelWB as an external file

    Public Sub GetListOfWorksheets()
        ExcelFilePath = Trim(UF009.UF009_TB_InputFilePath.Text)
        ExcelApp = New Excel.Application
        ExcelWB = ExcelApp.Workbooks.Open(ExcelFilePath)

Needless to say I was slapping my forehead against a brick wall when I discovered the problem - funny how 1 word can bring a whole project to it's knees - it would be nice if the expection assistant was alittle bit more helpful.

I'm interested to hear how you can turn a dataset into an excel file without actually having excel installed - it will come in handy in the very near future.

Cheers
InDistress

 

[cjard]

It is simple in concept:

Excel, since 2000, has been able to load and save in XML rather than binary format
So we take our dataset xml and xsl translate it into excel xml:

http://www.vbdotnetforums.com/showthread.php?t=17555