Passed strings longer than 255 characters are truncated in Excel

Sub NoLoop()
   Dim var1 As String, var2 As String, var3 As String, var4 As String
   Dim first As Integer, second As Integer, third As Integer

   ' Create four text string, each 100 characters in length.     
   var1 = String(100,"a")
   var2 = String(100,"b")
   var3 = String(100,"c")
   var4 = String(100,"d")

   ' The character length of each variable string is 100 characters.
   ' Define the variable equal to length of the first string.
   first = Len(var1) + 1
   ' Define variables equal to length of the original string plus
   ' the length of each additional string.
   second = first + Len(var2)
   third = second + Len(var3)

   ' Select the text box on the worksheet.
   ActiveSheet.Shapes("Text Box 1").Select

   With Selection

      ' Place the first string into the text box.
      .Characters.Text = var1

      ' Place the second, third, and fourth text strings into the
      ' text box. 
      .Characters(Start:=first).Text = var2
      .Characters(Start:=second).Text = var3
      .Characters(Start:=third).Text = var4
   End With
End Sub
				

Sub Looper()
   Dim i as Integer
   Dim mytxt As String

   ' Create a string 1000 characters in length.
   mytxt = WorksheetFunction.Rept("test", 250)

   ActiveSheet.Shapes("Text Box 1").Select
      
   With Selection

      ' Initialize text in text box.
      .Text = ""
      For i = 0 To Int(Len(mytxt) / 255)
      .Characters(.Characters.Count + 1).Text = Mid(mytxt, (i * 255) + _
         1, 255)
      Next

   End With

End Sub
				

Sub ExecuteLongConnection()

    'Declare variables.
    Dim Chan As Variant
    Dim LongConnection As Variant
    Dim NumRows, NumCols As Variant

    ' Set LongConnection to a long connection string
    ' (> 127 characters).
    LongConnection = "ODBC;DBQ=\\mustang2\databases\nwind.mdb;" _
       & "DefaultDir=\\mustang2\databases;Driver={Microsoft " _
       & "Access Driver (*.mdb)};DriverId=25;FIL=MS Access;" _
       & "ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;" _
       & "PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;" _
       & "UserCommitSync=Yes"

    ' Execute the PivotTableWizard method and use the StringToArray
    ' function to convert the long string to elements in an array.
    ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:= _
           Array("SELECT Employees.EmployeeID, Employees.Region," _
           & "Employees.Country FROM `\\mustang2\databases\NWIND`" _
           & ".Employees Employees"), _
           TableDestination:="", TableName:="PivotTable1", _
           BackgroundQuery:=False, _
           Connection:=StringToArray(LongConnection)

'NOTE: You can add your own code to add fields to the pivot table.

End Sub

Function StringToArray(Query As Variant) As Variant

   Const StrLen = 127 ' Set the maximum string length for
                      ' each element in the array to return
                      ' to 127 characters.
   Dim NumElems As Integer
   Dim Temp() As String

   ' Divide the length of the string Query by StrLen and
   ' add 1 to determine how many elements the String array
   ' Temp should contain, and redimension the Temp array to
   ' contain this number of elements.

   NumElems = (Len(Query) / StrLen) + 1
   ReDim Temp(1 To NumElems) As String

   ' Build the Temp array by sequentially extracting 127
   ' segments of the Query string into each element of the
   ' Temp array.

   For i = 1 To NumElems
      Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
   Next i

   ' Set the function StringToArray to the Temp array so it
   ' can be returned to the calling procedure.

   StringToArray = Temp

End Function