Hi, my name is Timothy Goodwin. Welcome to my blog!

I am a Microsoft .NET Developer and Webmaster at Commercial Data Corporation in Memphis, Tennessee. I have 8 years of expertise in programming with ASP.NET, VB.NET and Microsoft Transact SQL.

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

Web Project Debugging Locally on Windows 7 – Visual Studio 2010, IIS7, and SQL Server Quick Notes

If using SQL Server locally, install a Developer Edition of SQL Server. It will make your life much easier.

Make sure that IIS7 is running.
Make sure that the ASP.NET State Server is running.
Make sure that SQL Server is running.

Make sure that .NET version that you are going to use is set in the following the 3 areas:
1) IIS7 Server Level
2) Application Pool for the Website
3) The Web Application in Visual Studio

If using a local instance of SQL Server, setup an Application Pool for your web application with the Local Service account selected.
Add the Local Service account to the security logins of SQL Server and then add the databases that you are going to access to the User Mapping for that account.

Make sure that your connection in the web.config file is using your instance of SQL Server.

Test it out by using the >Start without Debugging command. (This should flush out any errors.)
After all of the issues have been worked out, then use the debugger.

Hello, I am Timothy Goodwin. Welcome to my blog.