Passed Strings Longer Than 255 Characters Are Truncated

   Sub NoLoop()
      Dim var1 as String, var2 as String, var3 as String
      Dim first As Integer, secnd As Integer, third As Integer
   
      ' Note that each of the strings in quotation marks should be entered
      ' on one line.
      var1 = _
         "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
         aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
      var2 = _
         "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
         bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"
      var3 = _
         "ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
         ccccccccccccccccccccccccccccccccccc"
      var4 = _
         "ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
         ddddddddddddddddddddddddddddddddddd"

      ' 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.
      secnd = first + Len(var2)
      third = secnd + Len(var3)

      ' Insert first string into text box
      DialogSheets(1).TextBoxes(1).Characters.Insert String:=var1
      ' Insert second string at the location of the end of the first
      ' string.
      DialogSheets(1).TextBoxes(1).Characters(first).Insert String:=var2
      ' Insert third string at the location of the end of the second string
      ' and so on.
      DialogSheets(1).TextBoxes(1).Characters(secnd).Insert String:=var3
      DialogSheets(1).TextBoxes(1).Characters(third).Insert String:=var4
   
   End Sub
				

   Sub Looper()
   
      Dim i as Integer
      Dim mytxt As String
      ' Assign mytxt variable to the desired string.
      ' String should be entered as one line.
      mytxt = "This is the desired string longer than 255 characters."
   
      With DialogSheets(1).TextBoxes(1)
   
         ' Initialize text in text box.
         .Text = ""
         For i = 0 To Int(Len(mytxt) / 255)
         .Characters(.Characters.Count + 1).Insert Mid(mytxt, (i * 255) + _
            1, 255)
         Next
   
      End With
   
   End Sub
				

   Sub Excel97()

      ActiveSheet.Shapes("Text Box 2").Select
      Selection.Characters.Text = _
      "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

      Selection.Characters(201).Insert String:= _
      "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

   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)
   
   End Sub
   
   'NOTE: You can add your own code to add fields to the pivot table.     

         

   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