I have a lot of .csv format files in a folder. Columns in each file are same
but not the rows.

I have another .xls file in the same folder with similar columns. I am
looking for a VBA which can copy all records from cm1FEB2005bhav.csv file
into my master.xls file in sheet1, then copy all records from
cm2FEB2005bhav.csv file into my master.xls file in sheet1 just below the
existing data and continue this till cm4MAR2005bhav.csv

If it is a saturday or sunday, the .csv files will not be present.

For example:
Files
cm5FEB2005bhav.csv
cm6FEB2005bhav.csv
will not be present as 5Feb and 6 Feb was saturday and sunday.

Maxi

How about something like:

Option Explicit
Sub testme()

Dim myFolder As String
Dim dCtr As Long
Dim testStr As String
Dim myFileName As String
Dim DestCell As Range
Dim TmpWkbk As Workbook
Dim MstrWks As Worksheet

myFolder = "C:\my documents\excel\test\"
If Right(myFolder, 1) < "\" Then
myFolder = myFolder & "\"
End If

Set MstrWks = workbooks("master.xls").Worksheets("sheet1")

With MstrWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

For dCtr = DateSerial(2005, 2, 2) To DateSerial(2005, 3, 4)
myFileName = myFolder & "cm" & Format(dCtr, "dmmmyyyy") & "bhav.csv"
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0
If testStr = "" Then
'not found
'comment this line when you're happy
Debug.Print myFileName & " wasn't found"
Else
Set TmpWkbk = Workbooks.Open(Filename:=myFileName)
TmpWkbk.Worksheets(1).UsedRange.Copy _
Destination:=DestCell
TmpWkbk.Close savechanges:=False
With DestCell.Parent
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
Next dCtr

End Sub

This assumes I can use column A to find the next available row.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

mac_see wrote:

I have a lot of .csv format files in a folder. Columns in each file are same
but not the rows.

I have another .xls file in the same folder with similar columns. I am
looking for a VBA which can copy all records from cm1FEB2005bhav.csv file
into my master.xls file in sheet1, then copy all records from
cm2FEB2005bhav.csv file into my master.xls file in sheet1 just below the
existing data and continue this till cm4MAR2005bhav.csv

If it is a saturday or sunday, the .csv files will not be present.

For example:
Files
cm5FEB2005bhav.csv
cm6FEB2005bhav.csv
will not be present as 5Feb and 6 Feb was saturday and sunday.

Maxi

--

Dave Peterson

Terrific. That worked !!!!

Thanx a million

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme()

Dim myFolder As String
Dim dCtr As Long
Dim testStr As String
Dim myFileName As String
Dim DestCell As Range
Dim TmpWkbk As Workbook
Dim MstrWks As Worksheet

myFolder = "C:\my documents\excel\test\"
If Right(myFolder, 1) < "\" Then
myFolder = myFolder & "\"
End If

Set MstrWks = workbooks("master.xls").Worksheets("sheet1")

With MstrWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

For dCtr = DateSerial(2005, 2, 2) To DateSerial(2005, 3, 4)
myFileName = myFolder & "cm" & Format(dCtr, "dmmmyyyy") & "bhav.csv"
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0
If testStr = "" Then
'not found
'comment this line when you're happy
Debug.Print myFileName & " wasn't found"
Else
Set TmpWkbk = Workbooks.Open(Filename:=myFileName)
TmpWkbk.Worksheets(1).UsedRange.Copy _
Destination:=DestCell
TmpWkbk.Close savechanges:=False
With DestCell.Parent
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
Next dCtr

End Sub

This assumes I can use column A to find the next available row.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

mac_see wrote:

I have a lot of .csv format files in a folder. Columns in each file are same
but not the rows.

I have another .xls file in the same folder with similar columns. I am
looking for a VBA which can copy all records from cm1FEB2005bhav.csv file
into my master.xls file in sheet1, then copy all records from
cm2FEB2005bhav.csv file into my master.xls file in sheet1 just below the
existing data and continue this till cm4MAR2005bhav.csv

If it is a saturday or sunday, the .csv files will not be present.

For example:
Files
cm5FEB2005bhav.csv
cm6FEB2005bhav.csv
will not be present as 5Feb and 6 Feb was saturday and sunday.

Maxi

--

Dave Peterson