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

Advertisements

One thought on “Automating Excel with Mixed Data types via VB.NET

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s