Sep 14

Written by: Steve Gray
9/14/2010 1:53 PM 

I’m trying to get data imported into VB from Excel, and I’m going round in circles. I tried exporting Excel to comma-separated, but embedded commas gave me fits. Then I tried using a tab-delimited export. Better, but line feed characters are causing trouble.

Today I’m trying to read form Excel directly, using Microsoft.Office.Interop,Excel.dll (deep breath)

Dim oExcel As New Microsoft.Office.Interop.Excel.Application
Dim oBook As Microsoft.Office.Interop.Excel._Workbook = 
    oExcel.Workbooks.Open("N:\Sedgwick\100914_SedgwickTest_4_Upload.xlsx")
Dim oSheet As _Worksheet = oBook.Worksheets(1)

'Read value in A2 cell   
Dim cellValue As String = oSheet.Range("A2").Value

'get values from the first row
Dim cellValue2 As System.Object(,) = oSheet.Range("A2:h2").Value

'Change value in A2 cell
oSheet.Range("A2").Value = ""

'get the number of rows
Dim intRows As Int16 = oSheet.Rows.CurrentRegion.Rows.Count

'Save this Excel document
oBook.SaveAs("C:\Book1.xls", True)

oExcel.Quit()