Can someone please help me

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Makrini

Well-known Member
Joined
May 22, 2007
Messages
1,035
If you want to pull from CSV files (and only the first 2 columns)

Here is a start for you...

The text I have in red is the file path - The Text in green is where you want the import to start.

So you need to create a loop and change those 2 things for every file you want to import. (we could write the whole code for you but step at a time is the best way to learn).

Code:
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;[COLOR="Red"]C:\Users\Administrator\Desktop\Book1.csv[/COLOR]", Destination:=[COLOR="Lime"]Range("A1")[/COLOR])
        .Name = "Book1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 9, 9, 9, 9, 9, 9, 9, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

"May" is not a good month for an example...
You need to change "mmmyyyy" to "mmmmyyyy" if your month is a complete month name.
try
Code:
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:\Custodians\Interest\Year\Month(" & Format$(Date,"mmmyyyy") & ")"
wsName = "Interst-" & Format$(Date,"mmmyyyy") & ".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(a,1)
        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 : ws = Nothing
End Sub
 

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
I am getting a run time error "9"- subscript out of range

this piece if code is highlighted in yellow, what have i done wrong?

Code:
y = Split(x(i), ",")



"May" is not a good month for an example...
You need to change "mmmyyyy" to "mmmmyyyy" if your month is a complete month name.
try
Code:
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:\Custodians\Interest\Year\Month(" & Format$(Date,"mmmyyyy") & ")"
wsName = "Interst-" & Format$(Date,"mmmyyyy") & ".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(a,1)
        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 : ws = Nothing
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Can you change
Rich (BB code):
For i = 0 To UBound(a,1)
to
Rich (BB code):
For i = 0 To UBound(x)
 

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
i now get a run time error '438 - object does not support this property or method

the following code is highlighted in yellow

Code:
Set fso = Nothing: ws = Nothing


Can you change
Rich (BB code):
For i = 0 To UBound(a,1)
to
Rich (BB code):
For i = 0 To UBound(x)
 
L

Legacy 98055

Guest
Hi Katy.

I this an operation that you perform on a daily basis or at the end of each month?

Will you post an example of one of your CSV files please? I especially need to see the row that contains the headers, if any, and then several rows of data...

Thanks...
 

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
Hi Tom,

This operation is performed at the end of each month.

The below is some of the data in CSV file, the headers start at A1.

<TABLE style="WIDTH: 237pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=316 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 53pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=71 height=17> End Date</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64> Pfolio</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64> A/c Code</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 88pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=117> A/c Native at End</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39584">16/05/2008</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">CAETRN</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="AUDCUST ">AUDCUST </TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>-300.05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39584">16/05/2008</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">CBWTGH</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="AUDCUST ">AUDCUST </TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str=" "> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39584">16/05/2008</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">CIETRN</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str="AUDCUST ">AUDCUST </TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:str=" "> </TD></TR></TBODY></TABLE>



Hi Katy.

I this an operation that you perform on a daily basis or at the end of each month?

Will you post an example of one of your CSV files please? I especially need to see the row that contains the headers, if any, and then several rows of data...

Thanks...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top