Read Excel data with retain spacing between data

[calvinkwoo3000]

I manage to read my excel data correctly,
How do i retain the spacing in between.

data in excel file = ABC DEF (3 spacing between ABC and DEF)
what i get = ABC DEF (remain 1 spacing be ABC and DEF)

*** I want ABC DEF (3 spacing in between)

my coding as below:
Dim oWS As Excel.Worksheet = DirectCast(oWBa.Worksheets(1), Excel.Worksheet)
oWS.Range("B" & i).Text // get ABC DEF
oWS.Range("B" & i).Value // get ABC DEF
oWS.Range("B" & i).Value2 // get ABC DEF

[kareninstructor]

Hello,

The following code I seeded a cell with "ABC...DEF" where the dot is a space, so that cell value has a length of 9 and upon running the code got the proper cell value with embedded spaces. Please note that the code was done in VS2010 but should work in VS2005.

Special notes: There is a good deal of code to read several cells, this is done to ensure all objects used to access Excel are properly disposed.

Public Class Form1
    Private Sub cmdGetCells_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGetCells.Click
        ' Open a file named File1.xlsx, read from sheet named 'Sample'
        ' get values for cells from string array.
        Dim FileName As String = IO.Path.Combine(Application.StartupPath, "File1.xlsx")
        Dim Cells = GetSomeCells(FileName, "Sample", New String() {"F11", "E10", "F10", "I10", "I11", "M4", "O4"})
        Dim sb As New System.Text.StringBuilder
        sb.AppendLine("Cell values from array")
        For Each cell In Cells
            Console.WriteLine("{0}={1} {2}", cell.Key, cell.Value, cell.Value.Length)
            sb.AppendLine(String.Format("{0}={1}", cell.Key, cell.Value))
        Next
        sb.AppendLine("Value for F10")
        sb.AppendLine(Cells("F10"))

        MessageBox.Show(sb.ToString)

    End Sub
End Class

Code module

Option Strict On
'Option Infer On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module ExcelOperations
    Public Function GetSomeCells(ByVal FileName As String, ByVal SheetName As String, ByVal Cells As String()) As Dictionary(Of String, String)
        Dim Dict As New Dictionary(Of String, String)

        If IO.File.Exists(FileName) Then
            Dim Proceed As Boolean = False
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next

            If Proceed Then
                For Each cell In Cells
                    xlCells = xlWorkSheet.Range(cell)

                    Dict.Add(cell, CStr(xlCells.Value))

                    ReleaseComObject(xlCells)
                Next

            End If

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlCells)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        End If

        Return Dict

    End Function
    Public Sub ReleaseComObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Module

Attachment 99631

[calvinkwoo3000]

kevininstructor, thank for your reply.

I write the code using VB.Net.

Here is my development file and data file,

https://skydrive.live.com/redir?resi...IkA7nE7qNIiWb4

can you please advise?
THank you!