First 255 chars from array element

[staticbob]

Guys,

I am using the code below to get all rows from a datatable into a multidim array, I am then writing each array element to a new row in an Excel workbook.

I am getting a problem if any of the elements have more than 255 chars, I believe this is the issue anyway. It starts copying fine, but then when I get to what could be a large text field, I get an error.... "Exception from HRESULT: 0x800A03EC"

How can I only copy the first 255 chars from an array element ? I have marked below where I believe I need to make the change, or would it be when populating the array initially ?

Thanks in advace,
Bob

Public Shared Sub Export(ByVal Filename As String, ByVal dt As DataTable, ByVal MRSMaster As String)
 
            Dim RowNbr As Int32 = 0
            Dim AllRecords(dt.Rows.Count) As Object
 
            Try
 
                'get MatRec into array
                For Each Rw As DataRow In dt.Rows
                    AllRecords(RowNbr) = Rw.ItemArray()
                    RowNbr += 1
                Next Rw
 
                Dim oExcel As New Excel.Application
                Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
                Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
                Dim oCells As Excel.Range
                'oExcel.Visible = False : oExcel.DisplayAlerts = False
 
                'Open MRS template
                oBook = oExcel.Workbooks.Open(MRSMaster)
 
                oSheets = oBook.Worksheets
                oSheet = CType(oSheets.Item(1), Excel.Worksheet)
                oExcel.AddIns("Analysis ToolPak").Installed = True
 
                'Do all the data export here.
                Dim x As Integer
                Dim y As Integer
                Dim range As String
                For x = 0 To AllRecords.GetUpperBound(0)
 
'************** HERE ***************
 
                    range = "A" & x + 2 & ":Q" & x + 2
                    oSheet.Range(range).Value = AllRecords(x)
                Next
 
                Select Case Right(MRSMaster, 13)
                    Case "antMaster.xls"
                        oSheet.Columns(17).delete()
                        oSheet.Columns(13).delete()
                        oSheet.Columns(2).delete()
                        oSheet.Columns(1).delete()
                    Case "MRSMaster.xls"
                        oSheet.Columns(14).delete()
                        oSheet.Columns(5).delete()
                        oSheet.Columns(2).delete()
                        oSheet.Columns(1).delete()
                End Select
 
 
                Dim MyDateTime As Date = Today
                Dim MyStr As String
                MyStr = Format(MyDateTime, "dd-mm-yy")
                oSheet.Name = "PWBExport " & MyStr
 
 
                oBook.SaveAs(Filename)
                oBook.Close()
                oExcel.DisplayAlerts = True
 
                'Quit Excel and thoroughly deallocate everything 
                oExcel.Quit()
                oExcel = Nothing : oBooks = Nothing : oBook = Nothing
                oSheets = Nothing : oSheet = Nothing
                AllRecords = Nothing
 
            Catch ex As Exception
                EH.Log(ex)
            End Try
        End Sub