Can someone please help me

[Katy Jordan]

hi, i am new to excel and VBA, and hope I can find some answers on this board

I have searched the forum regarding copying data from multiple worksheets into a main workbook, but I did not find anything that tells me how to copy data from multiple csv files into one workbook tab, I can see a thread created by Hiport which is very similar to what I want.
Basically I have a CSV file saved for each day of the month, these files are saved in their monthly folders, so all the CSV files for May will be saved in U:\Custodians\Interest\Year\Month(May2008), now in this folder will also be a master workbook called “Interest-May2008”.
What I do next is very manual, I have to copy from each CSV file, data from Col 1 and Col2 and paste this to the master workbook- sheet (downloads), all the data will be pasted next to each other, i.e. col 1 and 2 will be data for 01-May-08, and col 3 and 4 will have data for 02-May-08 etc, as you can see it’s a very daunting task having to go through each file and copy and paste.
I need a macro written so it copies data in col 1 and 2 in each csv file and then pastes that data in a master workbook sheet (downloads), the data will be in date order of the CSV files, I want the data pasted in row 3 of the master workbook (downloads).
I hope someone can solve my misery, you will save me 45 minutes of pain of copy and pasting.

 

[Katy Jordan]

ok now I have

MC0206 in Col 1 and 2
MC0306 in Col 3 and 4
MC0106 in Col 5 and 6
MC0506 in Col 7 and 8
MC0406 in Col 9 and 10

So still not in order

OK thanks
There was another typo...

Private Sub SearchFiles(myDir As String, myFileaName As String, myFileType As String)

should be

Private Sub SearchFiles(myDir As String, myFileName As String, myFileType As String)

 

[jindon]

OOps
change

If n > 0 Then HSortMA a, 1, n, 2

to

If n > 0 Then HSortMA myList, 1, myN, 2

in SearchFile sub

 

[Katy Jordan]

Nope, still not in order, what shall i do now? i can feel we are nearly there

OOps
change

If n > 0 Then HSortMA a, 1, n, 2

to

If n > 0 Then HSortMA myList, 1, myN, 2

in SearchFile sub

 

[jindon]

OOps
should be

If myN > 0 Then HSortMA myList, 1, myN, 2

 

[Katy Jordan]

ok i get a compile error around "Else"

Sub test()
    myN = 0
    SearchFiles "U:\NRFF", "MC", ".CSV"
    If myN > 0 Then HSortMA myList, 1, myN, 2
        GetCSV Date, 2, 4
    Else
        MsgBox "No file in the folder"
    End If
End Sub

OOps
should be

If myN > 0 Then HSortMA myList, 1, myN, 2

 

[jindon]

This maybe the last...
change to

Sub test()
    myN = 0
    SearchFiles "U:\NRFF", "MC", ".CSV"
    If myN > 0 Then
        GetMyCSV 2, 4
    Else
        MsgBox "No file in the folder"
    End If
End Sub

 

[Katy Jordan]

Ok i changed the mY N to searchfile sub, but still it didnt work , this is the full code which i have now

Private myList() As Variant, myN As Long
 
Sub test()
    myN = 0
    SearchFiles "U:\NRFF", "MC", ".CSV"
    If myN > 0 Then
        GetCSV Date, 2, 4
    Else
        MsgBox "No file in the folder"
    End If
End Sub
 
Private Sub SearchFiles(myDir As String, myFileName As String, myFileType As String)
Dim fso As Object, myFile As Object
Set fso = CreateObject("Scripting.FileSystemObject")
For Each myFile In fso.GetFolder(myDir).Files
    If myFile.Name Like myFileName & "*" & myFileType Then
        temp = Mid$(myFile.Name, Len(myFileName) + 1, 4)
        myDate = DateSerial(Year(Date), Val(Mid$(temp, 3)), Val(Left$(temp, 2)))
        MsgBox "temp = " & temp & vbLf & "myDate = " & myDate
        myN = myN + 1
        ReDim Preserve myList(1 To 2, 1 To myN)
        myList(1, myN) = myDir & "\" & myFile.Name: myList(2, myN) = myDate
    End If
Next
If myN > 0 Then HSortMA myList, 1, myN, 2
Set fso = Nothing
End Sub
 
Sub GetMyCSV(Column1 As Integer, Column2 As Integer)
Dim ws As Worksheet, wsName As String, ii As Long
Dim fso As Object, x, y, b() As String, i As Long, n As Long, t As Long
Column1 = Column1 - 1
Column2 = Column2 - 1
Set fso = CreateObject("Scripting.FileSystemObject")
wsName = "Interest-" & MonthName(Month(Date) - 1, False) & ".xls"
On Error Resume Next
Set ws = Workbooks(wsName).Sheets("downloads")
If ws Is Nothing Then _
    Set ws = Workbooks.Open(myDir & "\" & wsName).Sheets("downloads")
On Error GoTo 0
If ws Is Nothing Then
    MsgBox myDir & "\" & wsName & " is not found"
    Exit Sub
End If
t = 1
For i = 1 To myN
    Set myTxt = fso.OpenTextFile(myList(1, i))
    temp = myTxt.ReadAll: myTxt.Close
    x = Split(temp, vbCrLf)
    ReDim a(1 To UBound(x) + 1, 1 To 2)
    For ii = 0 To UBound(x)
        y = Split(x(ii), ",")
        If UBound(y) > 0 Then
            n = n + 1: a(n, 1) = y(Column1): a(n, 2) = Val(y(Column2))
        End If
    Next
    With ws.Cells(1, t)
        .Value = myList(1, i)
        .Offset(1).Resize(n, 2).Value = a
    End With
    t = t + 2: n = 0
Next
Set fso = Nothing: Set ws = Nothing
End Sub
 
Private Sub HSortMA(ary, LB, UB, ref)
Dim M As Variant, temp
Dim i As Long, ii As Long, iii As Long
i = UB: ii = LB
M = ary(ref, Int((LB + UB) / 2))
Do While ii <= i
    Do While ary(ref, ii) < M
        ii = ii + 1
    Loop
    Do While ary(ref, i) > M
        i = i - 1
    Loop
    If ii <= i Then
        For iii = LBound(ary, 1) To UBound(ary, 1)
            temp = ary(iii, ii): ary(iii, ii) = ary(iii, i): ary(iii, i) = temp
        Next
        ii = ii + 1: i = i - 1
    End If
Loop
If LB < i Then HSortMA ary, LB, i, ref
If ii < UB Then HSortMA ary, ii, UB, ref
End Sub

 

[jindon]

        GetCSV Date, 2, 4

should be

        GetMyCSV 2, 4

 

[Katy Jordan]

ok it works, but it brings the whole file path as headers for each date

so in Col A it has U:\NRFF\0106.csv etc, can we change it so the macro only brings over 0106 etc

        GetCSV Date, 2, 4

should be

        GetMyCSV 2, 4

 

[jindon]

In GetMyCSV
change

    With ws.Cells(1, t)
        .Value = myList(1, i)

to

    With ws.Cells(1, t)
        .Value = myList(2, i)

or

    With ws.Cells(1, t)
        .Value = Foramt$(myList(2, i),"ddmm")