First 255 chars from array element

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

VB Code:

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

I wouldn't bother loading another object, and just iterate through your datatable:

VB Code:

Public Shared Sub Export(ByVal Filename As String, ByVal dt As DataTable, ByVal MRSMaster As String)
 
        Dim RowNbr As Int32 = 0
 
        Try
 
            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
 
            'Ascii for A
            Const AsciiHolder As Int32 = 65
 
            For iRow As Int32 = 0 To dt.Rows.Count - 1
                For iCol As Int32 = 0 To dt.Columns.Count - 1
                    Dim ColumnID As String
                    If iCol < 26 Then
                        ColumnID = Convert.ToChar(AsciiHolder + iCol)
                    Else
                        ColumnID = Convert.ToChar(AsciiHolder + CInt(iCol / 26) - 1) & Convert.ToChar(AsciiHolder + (iCol Mod 26) - 1)
                    End If
                    If dt.Rows(iRow).Item(iCol).ToString.Length > 255 Then
                        oSheet.Range(ColumnID & iRow + 2).Value = dt.Rows(iRow).Item(iCol).ToString.Substring(0, 255)
                    Else
                        oSheet.Range(ColumnID & iRow + 2).Value = dt.Rows(iRow).Item(iCol).ToString
                    End If
                Next
            Next
            '************** HERE ***************
 
            Select Case MRSMaster.Substring(0, 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 = Date.Today
            Dim MyStr As String
            MyStr = MyDateTime.ToString("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
 
 
        Catch ex As Exception
 
        End Try

If you want up to the first 255 characters of a string you could use:

VB Code:

myString.Substring(0, Math.Min(myString.Length, 255))

Nice tip on the Math.Min, I don't know why I never thought of using that before.

Thanks guys,

Bill - your code works very well, thanks.

Is it possible to also to copy over the column/field headers into the first row ? I was using this code for a couple of datatables and I was opening predefined templates with columns headers already defined. I am now using this with lots of datatables and don't want to be opening a different template for each when I'm sure its got to be pretty simple to also dump the field names ?

Thanks
Bob

VB Code:

If iRow = 0 Then
           oSheet.Range(ColumnID & iRow + 1).Value = dt.Columns(iCol).ColumnName()
        End If
'Before this line
If dt.Rows(iRow).Item(iCol).ToString.Length > 255 Then