Automating Excel with Mixed Data types via VB.NET

When dealing with mixed data types, automating the importation of an Excel (.xls) file via ADO.NET using VB.NET can be a challenging task. I recently encountered an issue where a numeric column was not being imported because a few rows in the worksheet had spaces in the column. Changing the settings for Excel in the registry, as mentioned in many articles on the web, did not work for me and was a waste of my time. Until I sorted my workbook, setting the Extended Properties with IMEX=1 did not work for me either.

The following sort subroutine put all of the rows with spaces in the column at the top of the worksheet. When I loaded the sorted workbook into a dataset, the result subsequently forced Excel to read the column in as text and I obtained a good dataset.

‘ Subroutine to sort an Excel workbook before importing with the IMEX=1 option in the Extended Properties of the connection string.

Public Sub SortExcelWorkbook(ByRef vstrFileName As String)

Dim wbExcel As New Microsoft.Office.Interop.Excel.Application
wbExcel.DisplayAlerts = False
Try
Dim wbBook As Microsoft.Office.Interop.Excel.Workbook
wbBook = clsExcel.Workbooks.Open(vstrFileName)
Dim wbSheet As Microsoft.Office.Interop.Excel.Worksheet = wbBook.Worksheets(1)
Dim nbrRows As Integer

‘ Your spreadsheet may report the maximum number of rows.
‘ You might want to change this line to better reflect the size of your sheet.
nbrRows = wbSheet.Rows.Count

‘ Column J contains mixed data types. For instance, your column might have numbers in the first 25 rows and spaces in the last few rows.

Dim strRangeEnd As String
strRangeEnd = “J” & nbrRows.ToString
wbSheet.Columns.Sort(wbSheet.Range(“J2”, strRangeEnd), _
Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , , _
Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, , _
Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, , , _
Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns, _
Microsoft.Office.Interop.Excel.XlSortMethod.xlStroke, _
Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal, _
Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal, _
Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal)

wbBook.SaveAs(vstrFileName)
wbExcel.Quit()
Catch ex As Exception

Finally
wbExcel.Quit()
End Try
End Sub