Export to Excel - String Length Exceeded...

[JaedenRuiner]

Well,

in the help documentation I have read, it appears that the "max" length for a cell is 32767 characters, for a string or "text" value. however, I am currently utilizing the Range.Value property to set an array of values to all the values in a row at a single time, which is far quicker than setting each cell individually.

Simply put:

   Public Function SetRowValues(ByVal Row As Integer, _
                    ByVal Values() As Object, Optional ByVal sheet As String = "") As Boolean
      If Loaded Then
         Try
            If SelectSheet(sheet) Then ' if sheet is valid, set the Active worksheet
               DirectCast(_wks.Range(_wks.Cells(Row, 1), _wks.Cells(Row, Values.Length)), _
                   Excel.Range).Value = Values 'Set the Worksheet Row's Values
               Return True
            End If
         Catch ex As Exception
            _ex = ex
         End Try
      End If
      Return False
   End Function

This has worked so far without any errors, setting the values in the Excel.range, which is aimed at the current 'writing' row, to the values in the array passed to the function.

However, I noticed that suddenly the last 4 columns of an export were not being listed so I stepped through to find out what was occurring, and lo and behold i found an exception being thrown amidst the write process. Since the majority of the time with the Jet.Oledb SQL provider for an ADHOC query has determined the Column dimensions to be 255 characters in width, I tried eliminating (setting to "") the one column entry that is vastly larger than 255 characters, and as the function was called for the second row the call succeeded and the values were present.

I have updated my script to resize the selected text from the offending DB column, but I was wondering if anyone knew how I might affect the Excel Cell Value property to accept the full 4000 character DB NVarChar() of text as the documentation for Excel says it is supposed to support.

Thanks