Import bulk csv into Excel using VSTO VB.Net

CSV:

As per wiki A comma-separated values file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.

Code Example:

Followings are the Global or Class level variable needs to be declared:

Private oFilePath As String
Private objFile As Excel.Workbook
Private ExcelApp As Excel.Application
Private Const FILE_EXTENSION As String = ".csv"
Dim oCurrentWk As Excel.Workbook

Following code will on button click event:

Private Sub btnImport_Click(sender As Object, e As RibbonControlEventArgs) Handles btnImport.Click
	ExcelApp = Globals.ThisAddIn.Application
	OpenFileDialog1.Title = "Select CSV file"
	OpenFileDialog1.Multiselect = False
	OpenFileDialog1.Filter = ".csv files | *.csv"
	If OpenFileDialog1.ShowDialog <> OpenFileDialog1.ShowDialog.Cancel Then
		oFilePath = OpenFileDialog1.FileName.ToString()
	End If
	Try
		If oFilePath.Length = 0 Then
			MsgBox("Action Cancelled")
			GoTo cleanup
		End If
	Catch ex As Exception
		GoTo cleanup
	End Try
	'Dim oCurrentWk As Excel.Workbook = ExcelApp.Workbooks.Add()
	oCurrentWk = ExcelApp.ActiveWorkbook
	If IsNothing(oCurrentWk) Then
		oCurrentWk = ExcelApp.Workbooks.Add()
	End If
	Dim oCurrntSheet As Excel.Worksheet
	oCurrntSheet = oCurrentWk.ActiveSheet
	Dim oCurrRange As Range
	oCurrRange = oCurrntSheet.Range("A1")
	Dim mArray As Array = {2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2}
	'Check if file is CSV
	If getFileExtenstion(oFilePath.ToString() = FILE_EXTENSION) = True Then
		'Import csv
		With oCurrntSheet.QueryTables.Add(Connection:="TEXT;" + oFilePath, Destination:=oCurrRange)
			.Name = "Gulien"
			.FieldNames = True
			.RowNumbers = False
			.FillAdjacentFormulas = True
			.PreserveFormatting = True
			.RefreshOnFileOpen = False
			.RefreshStyle = XlCellInsertionMode.xlOverwriteCells
			.SavePassword = False
			.AdjustColumnWidth = True
			.RefreshPeriod = 0
			.TextFilePromptOnRefresh = False
			.TextFilePlatform = 437
			.TextFileStartRow = 1
			.TextFileParseType = XlTextParsingType.xlDelimited
			.TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote
			.TextFileConsecutiveDelimiter = False
			.TextFileTabDelimiter = False
			.TextFileSemicolonDelimiter = False
			.TextFileCommaDelimiter = True
			.TextFileSpaceDelimiter = False
			.TextFileColumnDataTypes = mArray
			.TextFileTrailingMinusNumbers = True
			.Refresh()
		End With
	End If
cleanup:
	'Releasing File
	ReleaseObject(oCurrRange)
	ReleaseObject(oCurrntSheet)
	'ReleaseObject(oCurrentWk)
End Sub

getFileExtension method:

Private Function getFileExtenstion(ByVal oFileName As String) As Boolean
	getFileExtenstion = False
	If oFileName.Length > 0 Then
		Try
			getFileExtenstion = True
		Catch ex As Exception
			getFileExtenstion = False
		End Try
	End If
End Function

Save Workbook method:

Private Sub SaveWB(wb As Excel.Workbook, oFileName As String)
	If wb IsNot Nothing Then
		ExcelApp.DisplayAlerts = False
		wb.SaveAs(oFileName, Excel.XlFileFormat.xlCSV)
		wb.Close(SaveChanges:=True)
	End If
End Sub

Release COM objects:

Private Sub ReleaseObject(ByVal objName As Object)
	Try
		System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objName)
	Catch ex As Exception
		MsgBox("Unable to process request")
	Finally
		objName = Nothing
	End Try
End Sub

getFilename method:

Private Function getFileName(ByVal oFilePath As String) As String
	getFileName = ""
	If oFilePath.Length > 0 Then
		Try
			getFileName = System.IO.Path.GetFileName(oFilePath)
		Catch ex As Exception
			getFileName = ""
		End Try
	End If
End Function

Save button click event:

Private Sub Save_Click(sender As Object, e As RibbonControlEventArgs) Handles Save.Click
	SaveWB(oCurrentWk, oFilePath)
	ReleaseObject(oCurrentWk)
End Sub

Share on Facebook

Tweet