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 the below error for the following part of the code
Compile error:
ByRef argument type mismatch, its higlighting

myList

Sub test()
    myN = 0
    SearchFiles "U:\NRFF", "MS", ".csv"
    If n > 0 Then
        GetMyCSV myList
    Else
        MsgBox "No file in the folder"
    End If
End Sub

delete ")" after 1

temp = Mid$(myFile.Name, Len(myFileName)+1,4)

 

[jindon]

Yup.
Change that line to

        GetMyCSV 2,4

 

[Katy Jordan]

OK now i get a message box saying "No file in Folder", which is bizarre, cos i have 5 CSV files in the folder at the moment

Yup.
Change that line to

        GetMyCSV 2,4

 

[jindon]

Can you change

    If n > 0 Then
        GetMyCSV 2,4
    Else

to

    If myN > 0 Then
        GetMyCSV 2,4
    Else

 

[Katy Jordan]

nope still says no file in folder, i kept GetMyCSV 2,4 else if i put my List i get that argument error

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

Can you change

    If n > 0 Then
        GetMyCSV 2,4
    Else

to

    If myN > 0 Then
        GetMyCSV 2,4
    Else

 

[jindon]

Can you just change

If myFile.Name Like myFileName & "####" & myFileType Then

to

If myFile.Name Like myFileName & "*" & myFileType Then

 

[Katy Jordan]

OK now is get a runtime mismatch around this code

myDate = DateSerial(Year(Date), Mid$(temp, 3), Left$(temp, 2))

Can you just change

If myFile.Name Like myFileName & "####" & myFileType Then

to

If myFile.Name Like myFileName & "*" & myFileType Then

 

[jindon]

Hummm
How about

myDate = DateSerial(Year(Date), Val(Mid$(temp, 3)), Val(Left$(temp, 2)))

 

[Katy Jordan]

ok now i get error on this piece of code

runtime error 9 script out of range

myList(myN, 2) = myDate

Hummm
How about

myDate = DateSerial(Year(Date), Val(Mid$(temp, 3)), Val(Left$(temp, 2)))

 

[jindon]

Other way around...
should be

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