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
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