Posts by chesterpark

chesterpark · Sep 28th 2015

Hello
Over the years I have always found what I needed eventually by searching, I have some code I have been using and it works spectaculary, and after hours i cant seem to get it right

But I would Like to take it one step farther, I need it to take the file name and add a column

IE it currently goes through each file in a folder and pulls 2 columns from it and pastes those into excel moving to right as it goes througg say 500 files
it currently puts the file name above the last column which has worked great until i want to manipulate the date easier.

[ATTACH=CONFIG]67053[/ATTACH]

So if possible I was wondering if anyone knew how to change my code so that it will have the "name of text file" then the 2 columns, then repeat like it does now

Actually another thing that would be great is, instead of being across like it is, add the name of the text file and just have 3 rows going downward instead of Left to right like now, so i can make a pivot table, not sure which way i would like best but either is great

Anyway i hope i have explained it enough

here is my code

Code

Sub Button3_Click()
    Sheet1.Cells.ClearContents


    Dim myDir As String, fn As String, ff As Integer, txt As String, flg As Boolean
    Dim delim As String, n As Long, b() As Variant, x As Variant, t As Integer
    
    myDir = "C:\Users\ASUS\Documents\Basket Stocks"
    delim = vbTab
    
    fn = Dir(myDir & "\*.csv")
    Do While fn <> ""
    
        ReDim b(1 To Rows.Count, 1 To 2)
        ff = FreeFile
        Open myDir & "\" & fn For Input As #ff
        
     '   Line Input #ff, txt  'Ignore header line
        
        Do While Not EOF(ff)
            Line Input #ff, txt
            x = Split(txt, delim)
            If Not flg Then
                n = n + 1: b(n, 1) = fn
                flg = True
            End If
            If UBound(x) > 0 Then
                n = n + 1
                b(n, 1) = x(2)
                b(n, 2) = x(1)
            End If
        Loop
        
        
      '  Columns(1).NumberFormat = "@"   'text
      '  Columns(2).NumberFormat = "General"  'general
      '  Columns(3).NumberFormat = "0"      'number
      
       '  Columns(1).NumberFormat = "0.00"
        ' Columns(3).NumberFormat = "0.00"


        
        
        
        Close #ff
        ThisWorkbook.Sheets(1).Cells(1, 1 + t * 2).Resize(n, 2).Value = b
        t = t + 1
        n = 0
        fn = Dir()
        flg = False
        
    Loop
    


End Sub

Display More