How about something to test first?

Excel likes to remember the last settings used for Data|Text to columns (or via
the import wizard).

You can play with excel's memory by do a "dummy" text to columns--it should
reset the settings, so your real retrieval will work ok.

Option Explicit
Sub testme01()

   Dim wks As Worksheet
   Dim myCell As Range
   
   Set wks = Worksheets("sheet1")
   With wks
       Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
   End With
   
   myCell.Value = "asdf"
   
   myCell.TextToColumns Destination:=myCell, _
       DataType:=xlDelimited, _
       TextQualifier:=xlDoubleQuote, _
       ConsecutiveDelimiter:=False, _
       Tab:=False, _
       Semicolon:=False, _
       Comma:=False, _
       Space:=False, _
       Other:=False, _
       FieldInfo:=Array(1, 1)
       
   myCell.ClearContents

End Sub

========
This memory usually screws me up when I've run data|text to columns, then copy
from a different application and try to paste to excel.  I'm not sure if it will
help you--I'm not sure how you retrieve the data.