הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Sunday, March 31, 2019 4:21 PM
I have an old app that does this and now have a new app that requires the same so figured I could just use my old code in the new program. But it doesn't work! Been trying for a few days now to figure out and I just don't know what the issue is. Hoping someone could provide some insight. I'll paste the code below and see if I can post an image of the error and line that triggers it. Any help would be greatly appreciated!
Public Function importExcelfileToDataTable(ByVal xlsFileFullName As String) As System.Data.DataTable
Dim returnDT As New System.Data.DataTable()
If File.Exists(xlsFileFullName) Then
' Create new Application.
Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
'Open Excel Workbook
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(xlsFileFullName)
'Get sheet the first work sheet Get sheet, if not found return null;
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = xlWorkBook.Worksheets(1) 'get the first worksheet.
Dim startingCol As Integer = xlWorkSheet.UsedRange.Column
Dim numberCol As Integer = xlWorkSheet.UsedRange.Columns.Count
Dim startingRow As Integer = xlWorkSheet.UsedRange.Row
Dim numberRow As Integer = xlWorkSheet.UsedRange.Rows.Count
'Get range
Dim xlRange As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.UsedRange
'Load all cells into Array
Dim arrayRanges(,) As Object = xlRange.Value 'This line loads the range of the excel file into an array
xlWorkBook.Close()
xlApp = Nothing
xlRange = Nothing
xlWorkSheet = Nothing
'import into Datatable by converting the object array to a table
returnDT = convertToDataTable(arrayRanges)
End If
End Function
Here's the error:
All replies (3)
Sunday, March 31, 2019 11:07 PM
Hello,
The following goes about drilling down to the WorkSheet slightly different than how you are which may work. In regards to the range, here it's hard coded but can be replaced with what you have. When working with Excel automation (which I don't know, only use Open XML for Excel) I tend to go overboard with releasing objects so there is a bunch of code for that which will not affect getting the range into an array.
Option Strict On
Option Infer On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module ExcelDemoIteratingData_Sampe
Public Sub OpenExcelIterateForumPost_1(ByVal FileName As String, ByVal SheetName As String)
If IO.File.Exists(FileName) Then
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(FileName)
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
Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
Next
If Proceed Then
Dim xlUsedRange = xlWorkSheet.Range("A1", "B8")
Dim DecimalTest As Decimal = 0
Try
Dim ExcelArray(,) As Object = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
If ExcelArray IsNot Nothing Then
' do something
End If
Finally
ReleaseExcelItem(xlUsedRange)
End Try
Else
' sheet not found
End If
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
ReleaseExcelItem(xlCells)
ReleaseExcelItem(xlWorkSheets)
ReleaseExcelItem(xlWorkSheet)
ReleaseExcelItem(xlWorkBook)
ReleaseExcelItem(xlWorkBooks)
ReleaseExcelItem(xlApp)
Else
' file not found
End If
End Sub
Private Sub ReleaseExcelItem(ByVal sender As Object)
Try
If sender IsNot Nothing Then
Marshal.ReleaseComObject(sender)
sender = Nothing
End If
Catch ex As Exception
sender = Nothing
End Try
End Sub
End Module
Please remember to mark the replies as answers if they help and unmarked 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.
NuGet BaseConnectionLibrary for database connections.
StackOverFlow
Monday, April 1, 2019 7:14 AM
Hi,
You can try the method provided by PankajSoni.
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.
Tuesday, April 2, 2019 5:06 PM
Thanks for your help Karen! Finally got back on to try and figure this out. Gave your code a shot but unfortunately getting the same error. Could it be something with my set up? It's crazy because, like I said in my original post, I already wrote a program years ago that worked, I cut and paste that code into my new program and it doesn't work. Extremely frustrating. 