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 get error message with the following piece of code

For i = 0 To UBound(x)

Error message = Compile error: For Control variable already in use

Other way around...
should be

myList(1, myN) = myDir & "\" & myFile.Name: myList(2, myN) = myDate

 

[jindon]

OOps
1) change

Dim ws As Worksheet, wsName As String

to

Dim ws As Worksheet, wsName As String, ii As Long

2) change

    For i = 0 To UBound(x)
        y = Split(x(i), ",")
        If UBound(y) > 0 Then
            n = n + 1: a(n, 1) = y(Column1): a(n, 2) = Val(y(Column2))
        End If
    Next

to

    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

 

[Katy Jordan]

ok now i get a message box saying "\Interest-November.xls not found", the same message i had yesterday

OOps
1) change

Dim ws As Worksheet, wsName As String

to

Dim ws As Worksheet, wsName As String, ii As Long

2) change

    For i = 0 To UBound(x)
        y = Split(x(i), ",")
        If UBound(y) > 0 Then
            n = n + 1: a(n, 1) = y(Column1): a(n, 2) = Val(y(Column2))
        End If
    Next

to

    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

 

[jindon]

Change

wsName = "Interest-" & MonthName(Month(ForMonth)-1, False) & ".xls""

to

wsName = "Interest-" & MonthName(Month(Date)-1, False) & ".xls""

Make sure that you have "Interest-Jun.xls"
or do you want to create a new file named "Interest-Jun.xls" ?

 

[Katy Jordan]

ok macro worked but the data is not in order, its still the same as the orignal code

Data for MC0106 is in Col I and J and data for MC0206 Is in Col E and F, data for MC0406 is in Col C and D, and data for MC0506 is in Col A and B,


I need the data in CSV file order, so in Col 1 and 2 (or A & B) should be MC0106 data and in Col 3 and 4 should be MC0206 data etc

Change

wsName = "Interest-" & MonthName(Month(ForMonth)-1, False) & ".xls""

to

wsName = "Interest-" & MonthName(Month(Date)-1, False) & ".xls""

Make sure that you have "Interest-Jun.xls"
or do you want to create a new file named "Interest-Jun.xls" ?

 

[jindon]

Then
myDate is not reading correctly...
Add one line and see if it pick up the correct date.

myDate = DateSerial(Year(Date), Val(Mid$(temp, 3)), Val(Left$(temp, 2)))
MsgBox "temp = " & temp & vbLf & "myDate = " & myDate

 

[Katy Jordan]

nah still the same as before, I have posted the adjustment code, can you see anything out of wack?

Is this correct?

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 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, myFileaName 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 n > 0 Then HSortMA a, 1, n, 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

Then
myDate is not reading correctly...
Add one line and see if it pick up the correct date.

myDate = DateSerial(Year(Date), Val(Mid$(temp, 3)), Val(Left$(temp, 2)))
MsgBox "temp = " & temp & vbLf & "myDate = " & myDate

 

[jindon]

Did you see the messages?
I wanted you to read the messages and see if myDate is displaying correctly.

 

[Katy Jordan]

temp = MC05
My date= 30/04/2008

temp = MC04
My date= 31/03/2008
temp = MC02

My date= 31/01/2008
temp = MC03

My date= 29/02/2008

temp = MC01
My date= 31/01/2008

Did you see the messages?
I wanted you to read the messages and see if myDate is displaying correctly.

 

[jindon]

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)