Issues with Strings from Excel Collections

gtodd

New Member
Joined
Dec 16, 2015
Messages
4
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)


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

End Sub
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

gtodd

New Member
Joined
Dec 16, 2015
Messages
4
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:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,789
Messages
5,470,790
Members
406,723
Latest member
Fcolombo

This Week's Hot Topics

Top