Link to home
Start Free TrialLog in
Avatar of sqdperu
sqdperuFlag for United States of America

asked on

VB net, code to modify xlsx file, and errors when it is ran on NON-developer PC...

Using VB 2013 and Excel 2016 - Dev PC is Win 7 64-bit,  Erroring on Win 2008 R2 server 64-bit.

I wrote a program so I can drop an xlsx file in a folder and it will automatically import that file to a MS SQL server table.   That part works great and has for some time.

Turns out a column in the xlsx file is wrong if I don't open the file in Excel and format the column as text before I do the import.   I don't want to do that - want it automated.   So I found code online to do that, put it in my code, and it works great on my dev PC.

I put the program on the server with the new code and that is when I get the dreaded error: "Retrieving the COM class factory for component with CLSID {...useless number...} failed due to the following error: 80040154 Class not registered (Exception from HRESULT:0x80040154 (REGDB_E_CLASSNOTREG))."   In the details it mentions "System.Runtime.InteropServices.COMException".

So obviously there is something missing on the server where it will not run that is on my Dev PC where it will run.

The following is the code I got off internet that I added that causes it to fail on the server now:
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices

 Call SetColumnToText(strXlsxFileName, strXlsxSheetName, "G")

    Public Sub SetColumnToText(ByVal OpenFileName As String, ByVal SheetName As String, ByVal Column As String)

        ' Added.   01/30/2017...BWM

        If IO.File.Exists(OpenFileName) Then

            txtMsg.Text = "Formatting xlsx Column..."
            txtMsg.Refresh()

            Dim Proceed As Boolean = False

            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(OpenFileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next
            If Proceed Then
                xlCells = xlWorkSheet.Range(Column & ":" & Column)
                xlCells.Select()
                xlCells.NumberFormat = "@"
            Else
                MsgBox("Sheet '" & SheetName & "' not found.", CType(MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, MsgBoxStyle), "ExcelAutoImport - Excel Import FAILED")
            End If

            xlWorkSheet.SaveAs(OpenFileName)

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlCells)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            MsgBox("File '" & OpenFileName & "' not found.", CType(MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, MsgBoxStyle), "ExcelAutoImport - Excel Import FAILED")
        End If
    End Sub

    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

Open in new window


It fails somewhere in the Sub SetColumnToText.

Things I have tried:
-  Target CPU was already set to "x86" in previous version and still is.
-  Regsrv32.exe /i for files Microsoft.Office.Interop.Excel.dll, Microsoft.Vbe.Interop.dll, and office.dll ran from both the Windows\system32\ and Windows\SysWOW64\ folders.   Got error on both those: "The module "File path and name" was loaded but the entry-point DllRegisterServer was not found.  Make sure the "file path and name" is a valid DLL or OCX file and then try again.   NOTE:  I copied these files from my Dev PC to the server.
- I have ran both vstor_redist.exe and AccessDatabaseEngine.exe on the server.  These are both downloads from MS that is supposed to put necessary files for Office functionality.

What I am wondering is if what I am trying to do is considered an Excel Editing type of function and maybe can only be done on a PC that has Excel installed on it (which the server does not, but my dev PC does).

Any ideas?

Thanks,
ASKER CERTIFIED SOLUTION
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sqdperu

ASKER

I installed Excel 2007 on the server and  now my code works fine.  
Thanks,