Hi all,
I am currently working with data and cash balances from a notepad. All of my information is separated by tabs (the spacing when you hit tab) however the cash balances look something like this i.e. (458,119.74). I am using the MS Excel VBO "get worksheet as collection" object to import the data into a collection and the code thinks that the comma found in the cash balance is a delimiter and separates them into 2 columns.
I tried playing around with the code by replacing the comma with a space to no avail. Thanks for your help!
Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname, False)
' Do we have a sheet?
sheetexists = ws IsNot Nothing
' No sheet? No entry.
If Not sheetexists Then Return
ws.Activate()
ws.UsedRange.Select()
ws.UsedRange.Copy()
Dim data As String = Clipboard.GetDataObject().GetData(DataFormats.Text, True)
' The data split into rows
Dim asRows() As String = Split(data, vbCrLf)
Dim table As New DataTable()
' Set a flag indicating the header row
Dim isHeaderRow As Boolean = True
For Each strRow As String In asRows
If Not String.IsNullOrEmpty(strRow) Then
Replace(strRow,",","")
Dim fields() As String = Split(strRow, vbTab)
If isHeaderRow Then
isHeaderRow = False
For Each field As String in fields
table.Columns.Add(field)
Next
Else
Dim row as DataRow = table.NewRow()
For i As Integer = 0 To fields.Length - 1
If i < fields.Length Then
row(i) = fields(i)
Else
row(i) = ""
End If
Next I
table.Rows.Add(row)
End If
End If
Next
worksheetcollection = table
