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.

 

[BrianB]

Hopefully my code here will help you on your way
http://www.mrexcel.com/forum/showthread.php?t=140187

 

[Katy Jordan]

hi brian thanx for the below, but what things do I have change in this code to suit my needs, i am very new to VBA so by looking at your code i have no either what steps to take next.

Hopefully my code here will help you on your way
http://www.mrexcel.com/forum/showthread.php?t=140187

 

[Makrini]

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).

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]

"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

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]

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?

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

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]

Can you change

For i = 0 To UBound(a,1)

to

For i = 0 To UBound(x)

 

[Katy Jordan]

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

Can you change

For i = 0 To UBound(a,1)

to

For i = 0 To UBound(x)

 

[Legacy 98055]

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]

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...