Issues with Strings from Excel Collections

[gtodd]

Hello all,

I'm indexing some text files to get some data and pull it into excel. The file format I'm pulling from is a ".txt". The issue I'm running into is when VBA pastes in the string from the data collection. The immediate window using debug.print shows exactly how I want to see my data in the cell (with spaces) - see below. While after the macro has run, the spaces have been moved out to the right. However, if I copy and paste into a word or text document, the format shows up as it does in the immediate window. Is there a way a can force VBA to preserve the spaces?

Any help is appreciated!

Debug.Print view: "6000____1111111111111111____ BB____M161001____K1____ 0____QD____20130611____ 20130710____0____22____ A"
Excel Worksheet view: "60001111111111111111BBM161001K10QD2013061120130710022A________________________________________"

*(underscores used as spaces)

Function Qty_From_Txt(sFlocation, iFileIndex) As Collection

Set coll = New Collection

For i = 1 To iFileIndex

sFname = Sheets("Filenames").Cells(i + 5, 2).Value

    sfilestring = sFlocation & sFname

    Dim fso As New FileSystemObject
    'Set fso = Server.CreateObject("Scripting.FileSystemObject")

    Set txtStream = fso.OpenTextFile(sfilestring, ForReading, False)

    Do While Not txtStream.AtEndOfStream
        sLineText = txtStream.ReadLine
    
        If sLineText Like "6000*" Then
            coll.Add sLineText
        End If
    Loop

Set Qty_From_Txt = coll
txtStream.Close

Next i

End Function


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Sub Retrieve_Usage()

Dim Qty_coll As Collection
Dim sFlocation As String
    
    Sheets("Filenames").Activate
    iFileCount = WorksheetFunction.CountA(Range("B:B"))
    sFlocation = ActiveWorkbook.Sheets("Control").Cells(7, 3).Value
           
        If Right(sFlocation, 1) <> "\" Then
                                                                                                              
            sFlocation = sFlocation & "\"
                                                                                                              
        End If
    
    Sheets("6000 - Quantity").Activate
      
Set Qty_coll = Qty_From_Txt(sFlocation, iFileCount)

Sheets("6000 - Quantity").Activate
ActiveSheet.Range("A2").Select

'Dim sLine As Variant
'For Each sLine In Qty_coll
'PasteLine = sLine.Qty_coll
'
'ActiveCell.Value = PasteLine
'
'ActiveCell.Offset(rowOffset:=1).Activate
'Next sLine

For i = 1 To Qty_coll.Count

pasteline = Qty_coll(i) 

ActiveCell.Value = pasteline  'ISSUE OCCURS HERE

ActiveCell.Offset(rowOffset:=1).Activate

Next i

End Sub

 

[gtodd]

Answered my own question. The macros above work as expected, but excel does not display the string in the cell the same way it is displayed in the immediate window. An audit of the flat file showed that everything was separated by tabs (an oversight on my part). I solved it by entering this slice of code:

Function Qty_From_Txt(sFlocation, iFileIndex) As Collection

Set coll = New Collection

For i = 1 To iFileIndex

sFname = Sheets("Filenames").Cells(i + 5, 2).Value

    sfilestring = sFlocation & sFname

    Dim fso As New FileSystemObject
    'Set fso = Server.CreateObject("Scripting.FileSystemObject")

    Set txtStream = fso.OpenTextFile(sfilestring, ForReading, False)

    Do While Not txtStream.AtEndOfStream
        sLineText = txtStream.ReadLine
    
        If sLineText Like "6000*" Then
            coll.Add sLineText
        End If
    Loop

Set Qty_From_Txt = coll
txtStream.Close

Next i

End Function


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Sub Retrieve_Usage()

Dim Qty_coll As Collection
Dim sFlocation As String
    
    Sheets("Filenames").Activate
    iFileCount = WorksheetFunction.CountA(Range("B:B"))
    sFlocation = ActiveWorkbook.Sheets("Control").Cells(7, 3).Value
           
        If Right(sFlocation, 1) <> "\" Then
                                                                                                              
            sFlocation = sFlocation & "\"
                                                                                                              
        End If
    
    Sheets("6000 - Quantity").Activate
      
Set Qty_coll = Qty_From_Txt(sFlocation, iFileCount)

Sheets("6000 - Quantity").Activate
ActiveSheet.Range("A2").Select

'Dim sLine As Variant
'For Each sLine In Qty_coll
'PasteLine = sLine.Qty_coll
'
'ActiveCell.Value = PasteLine
'
'ActiveCell.Offset(rowOffset:=1).Activate
'Next sLine

For i = 1 To Qty_coll.Count

pasteline = Qty_coll(i) 

ActiveCell.Value = pasteline  'ISSUE OCCURS HERE

ActiveCell.Offset(rowOffset:=1).Activate

Next i

ActiveSheet.Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False

End Sub