sqdperu
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.InteropSer vices.COME xception".
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:
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.E xcel.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,
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.InteropSer
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
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.E
- 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks,