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.

 

[Legacy 98055]

I don't know. I would rather not waste my time finding out...

 

[Katy Jordan]

So what now, is there light at the end of the tunnel?

what do i do next? I know the code is 99% there, but its just needs those adjustments

I don't know. I would rather not waste my time finding out...

 

[jindon]

i now get a run time error '438 - object does not support this property or method

the following code is highlighted in yellow

Set fso = Nothing: ws = Nothing

change to

Set fso = Nothing: Set ws = Nothing

 

[Katy Jordan]

Jindon how do i change the code so it picks up data from column 2 and column 4 of the CSV files? at the moment it picks up column 1 and 2, and how do i get the code so accepts files saved as one month in arrear?

change to

Set fso = Nothing: Set ws = Nothing

 

[Katy Jordan]

how do i change the below data to show the month before, i.e June

wsName = "Interest-" & Format$(Date, "mmmmyyyy") & ".xls"

and want the code to pick rows 2 and 4 from the csv files rather than rows 1 and 2, how do i change that below?

Sub test()
Dim ws As Worksheet, myDir As String, fn As String, temp As String, wsName As String
Dim fso As Object, x, y, a() As String, i As Long, n As Long, t As Long
Set fso = CreateObject("Scripting.FileSystemObject")
myDir = "U:\NRFF"
wsName = "Interest-" & Format$(Date, "mmmmyyyy") & ".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
fn = Dir(myDir & "\*.csv")
Do While fn <> ""
    Set myTxt = fso.OpenTextFile(myDir & "\" & fn)
    temp = myTxt.ReadAll: myTxt.Close
    x = Split(temp, vbCrLf)
    ReDim a(1 To UBound(x) + 1, 1 To 2)
    For i = 0 To UBound(x)
        y = Split(x(i), ",")
        If UBound(y) > 0 Then
            n = n + 1: a(n, 1) = y(0): a(n, 2) = y(1)
        End If
    Next
    With ws.Cells(1, t)
        .Value = fn
        .Offset(1).Resize(n, 2).Value = a
    End With
    t = t + 2: n = 0
    fn = Dir
Loop
Set fso = Nothing: Set ws = Nothing
End Sub

 

[Legacy 98055]

Hope you don't mind Jindon.

Sub Example()
    GetCSV #5/1/2008#, 2, 4
End Sub

Sub GetCSV(ForMonth As Date, Column1 As Integer, Column2 As Integer)
Dim ws As Worksheet, myDir As String, fn As String, temp As String, wsName As String
Dim fso As Object, x, y, a() As String, i As Long, n As Long, t As Long

Column1 = Column1 - 1
Column2 = Column2 - 1

Set fso = CreateObject("Scripting.FileSystemObject")
myDir = "U:\NRFF"
wsName = "Interest-" & Format$(ForMonth, "mmmmyyyy") & ".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
fn = Dir(myDir & "\*.csv")
Do While fn <> ""
    Set myTxt = fso.OpenTextFile(myDir & "\" & fn)
    temp = myTxt.ReadAll: myTxt.Close
    x = Split(temp, vbCrLf)
    ReDim a(1 To UBound(x) + 1, 1 To 2)
    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) = y(Column2)
        End If
    Next
    With ws.Cells(1, t)
        .Value = fn
        .Offset(1).Resize(n, 2).Value = a
    End With
    t = t + 2: n = 0
    fn = Dir
Loop
Set fso = Nothing: Set ws = Nothing
End Sub

 

[Katy Jordan]

Thanx Tom,

But i still have the problem around the date side of things, i need the date to know whatever month we are currently in it needs to minus one, so since its July now here, I will be doing the work for June, therefore the file will be saved as June, and in August it will be saved as July, the macro needs to know this.

As for extracting the data I can see that the macro has picked up Rows 2 and 4, but the data picked up from Row 4 has lots of zero's at the start but it does not have zeros in the CSV file, how do i get the macro to get rid of the zeros at the start so it shows 300.05

example for the first p'folio

Col 2 Col 4
ZHAHIE -0000000300.05

Hope you don't mind Jindon.

Sub Example()
    GetCSV #5/1/2008#, 2, 4
End Sub
 
Sub GetCSV(ForMonth As Date, Column1 As Integer, Column2 As Integer)
Dim ws As Worksheet, myDir As String, fn As String, temp As String, wsName As String
Dim fso As Object, x, y, a() As String, i As Long, n As Long, t As Long
 
Column1 = Column1 - 1
Column2 = Column2 - 1
 
Set fso = CreateObject("Scripting.FileSystemObject")
myDir = "U:\NRFF"
wsName = "Interest-" & Format$(ForMonth, "mmmmyyyy") & ".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
fn = Dir(myDir & "\*.csv")
Do While fn <> ""
    Set myTxt = fso.OpenTextFile(myDir & "\" & fn)
    temp = myTxt.ReadAll: myTxt.Close
    x = Split(temp, vbCrLf)
    ReDim a(1 To UBound(x) + 1, 1 To 2)
    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) = y(Column2)
        End If
    Next
    With ws.Cells(1, t)
        .Value = fn
        .Offset(1).Resize(n, 2).Value = a
    End With
    t = t + 2: n = 0
    fn = Dir
Loop
Set fso = Nothing: Set ws = Nothing
End Sub

 

[jindon]

Thanks Tom

Katy
1)
change

wsName = "Interest-" & Format$(ForMonth, "mmmmyyyy") & ".xls"

to

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

2) change

n = n + 1: a(n, 1) = y(Column1): a(n, 2) = y(Column2)

to

n = n + 1: a(n, 1) = y(Column1): a(n, 2) = Val(y(Column2))

 

[Katy Jordan]

I ameded the code but when i press the macro button for some reason i get a message say U:\NRFF\Interest-November.xls is not found, why did November come up? when the file is saved as Interest-June2008

is my code below correct?

Sub test()
Dim ForMonth As Date, Column1 As Integer, Column2 As Integer
Dim ws As Worksheet, myDir As String, fn As String, temp As String, wsName As String
Dim fso As Object, x, y, a() As String, i As Long, n As Long, t As Long
Column1 = Column1 - 1
Column2 = Column2 - 1

Set fso = CreateObject("Scripting.FileSystemObject")
myDir = "U:\NRFF"
wsName = "Interest-" & MonthName(Month(ForMonth) - 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
fn = Dir(myDir & "\*.csv")
Do While fn <> ""
    Set myTxt = fso.OpenTextFile(myDir & "\" & fn)
    temp = myTxt.ReadAll: myTxt.Close
    x = Split(temp, vbCrLf)
    ReDim a(1 To UBound(x) + 1, 1 To 2)
    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
    With ws.Cells(1, t)
        .Value = fn
        .Offset(1).Resize(n, 2).Value = a
    End With
    t = t + 2: n = 0
    fn = Dir
Loop
Set fso = Nothing: Set ws = Nothing
End Sub

 

[jindon]

Not that code of mine, use Tom's.
Run Example and change the date as you want.
In this case, it will look at May2008.xls as the date is 6/1/2008, one month before.

Sub Example()
    GetCSV #6/1/2008#, 2, 4
End Sub
 
Sub GetCSV(ForMonth As Date, Column1 As Integer, Column2 As Integer)
Dim ws As Worksheet, myDir As String, fn As String, temp As String, wsName As String
Dim fso As Object, x, y, a() As String, i As Long, n As Long, t As Long
 
Column1 = Column1 - 1
Column2 = Column2 - 1
 
Set fso = CreateObject("Scripting.FileSystemObject")
myDir = "U:\NRFF"
wsName = "Interest-" & MonthName(Month(ForMonth)-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
fn = Dir(myDir & "\*.csv")
Do While fn <> ""
    Set myTxt = fso.OpenTextFile(myDir & "\" & fn)
    temp = myTxt.ReadAll: myTxt.Close
    x = Split(temp, vbCrLf)
    ReDim a(1 To UBound(x) + 1, 1 To 2)
    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
    With ws.Cells(1, t)
        .Value = fn
        .Offset(1).Resize(n, 2).Value = a
    End With
    t = t + 2: n = 0
    fn = Dir
Loop
Set fso = Nothing: Set ws = Nothing
End Sub