none
Error opening Excel File to add data using vb.net RRS feed

  • Question

  • Hi. I am trying to cycle through Excel records looking for any 'Lot' lines and then performing a calculation on that line.

    I am getting an error on the line

    Dim xlWorkBook = New Microsoft.Office.Interop.Excel.Workbook

    The error says:

    System.Runtime.InteropServices.COMException: 'Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).'


    Any Ideas?

    [code]

    Private Sub btnForcast_Click(sender As Object, e As EventArgs) Handles btnForcast.Click
            'Code to open chosen Excel file And do Lot calculations
            Dim strFileName As String
            strFileName = txtFileName.Text

            Dim xlApp = New Microsoft.Office.Interop.Excel.Application
            Dim xlWorkBook = New Microsoft.Office.Interop.Excel.Workbook
            Dim xlWorkSheet = New Microsoft.Office.Interop.Excel.Worksheet


            xlWorkBook = xlApp.Workbooks.Open(strFileName)
            xlWorkSheet = xlWorkBook.Worksheets("sheet1")

            'display the cell's value N20

            Dim strRowData As String
            Dim strRowID As String
            Dim strTRange As String
            Dim strQRange As String
            Dim strRRange As String
            Dim strFormula As String
            Dim intLot As Integer

            'Dim intRowCount As Integer = xlWorkSheet.Rows.Count

            strRowID = xlWorkSheet.Rows.ID

            For Each strRowID In xlWorkSheet.Rows
                strRowData = xlWorkSheet.Cells(strRowID, 13).value
                If strRowData = "Lot*" Then
                    'edit the cell with New value
                    strTRange = "T" + strRowID
                    strQRange = "Q" + strRowID
                    strRRange = "R" + strRowID
                    strFormula = "strTRange - (strQRange + strRRange)"
                    xlWorkSheet.Cells(strRowID, 24) = strFormula

                    'Save Lot value for Domain calulations
                    intLot = xlWorkSheet.Cells(strRowID, 24)


                End If

            Next


            'edit the cell with New value
            'xlWorkSheet.Cells(2, 2) = " ";
            xlWorkBook.Close()
            xlApp.Quit()
        End Sub

    [/Code]

    Tuesday, October 30, 2018 4:49 PM

All replies

  • How did you add a reference to your project?



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, October 30, 2018 5:01 PM
    Moderator
  • Hi,

    try to configure the corresponding Excel DCOM permissions

    1: Start "Component Services" by typing dcomcnfg.exe in "Start" -> "Run"

    2: Double-click "Component Services" -> "Computer" -> "My Computer" -> "DCOM Config"

    3: Find the "Microsoft Excel Application" in "DCOM Configuration", right click on it, and then click "Properties" to bring up the "Microsoft Excel Application Properties" dialog box.Program Properties dialog

    4: Click the "Identity" tab and select "The interactive user"

    5: Click the "Security" tab, click "Customize" on the "Launch and Activation Permissions", then click the corresponding "Edit" button, and add a "NETWORK SERVICE" user in the pop-up "Security" dialog box ( Note that you want to select this computer name) and give it the "local startup" and "local activation" permissions.

    6: Still the "Security" tab, click "Customize" on the "Access Rights", then click "Edit", and add a "NETWORK SERVICE" user in the pop-up "Security" dialog box, and then give " Local access "permissions.

    In this way, we have configured the corresponding Excel DCOM permissions.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 31, 2018 6:16 AM