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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top