VBA code To Merge All .csv files into a single excel sheet (data start from row 11th)
 

Hi All VBA experts:
I have 1000+ data files stored in csv. All of these files have the same structure& format.
I use a vba code written by abousetta

To merge it into one spreadsheet automatically, but the problem is,the data i needed start only from row 11 onward.
Is there a way to modify the code to allow this to happen?

Here's my requirement on the code:
1) Must be able to merge all the file csv file in on shoot,
2) Data needed start from 11th row onward,
3) after open each file, copy the data from 11th row and onward, copy and paste it into MasterCSV sheet, the next data will be paste to the same column but next row to the previous data copied...

I attach one of my file into the thread...

[quote]
Option Explicit

Sub CombineCSVFiles()

Dim sCSV$, sCombCSV$, iFF%
Dim myFolder, myFile, arrCSV
Dim myRange As Range
Dim fso As Object
Dim fPath As String

' Turn off some Excel functionality so your code runs faster
With Application
.ScreenUpdating = False
.DisplayStatusBar = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

' Use File System Object to choose folder with files
Set fso = CreateObject("Scripting.FileSystemObject")

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
.InitialFileName = ThisWorkbook.Path & "\" ' Default path - Change as required
.Title = "Please Select a Folder"
.ButtonName = "Select Folder"
If .SelectedItems.Count 0 Then fPath = .SelectedItems(1) & "\" Else Exit Sub
End With

' Open each file consequently and merge into a variable
iFF = FreeFile
Set myFolder = fso.GetFolder(fPath).Files
For Each myFile In myFolder
If LCase(myFile) Like "*.csv" Then
sCSV = Space(FileLen(myFile))
Open myFile For Binary Access Read As #iFF
Get #iFF, , sCSV
sCombCSV = sCombCSV & sCSV
Close #iFF
End If
' Loop through all files in folder
Next myFile

' Convert variable to array
arrCSV = Split(sCombCSV, vbCrLf)

' Paste data back to Excel
Set myRange = Range(Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1), Range("A" & Rows.Count).End(xlUp).Offset(UBound(arrCSV) + 1))
myRange = Application.Transpose(arrCSV)
myRange.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Comma:=True

' clean up
myFile = vbNullString
iFF = 1

' Turn Excel functionality back on
With Application
.DisplayStatusBar = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub

[quote]

VBA code To Merge All .csv files into a single excel sheet (data start from row 11th)
 

windslayer wrote:

I have 1000+ data files stored in csv. All of these files have the same
structure& format.
I use a vba code written by abousetta

To merge it into one spreadsheet automatically, but the problem is,the
data i needed start only from row 11 onward.
Is there a way to modify the code to allow this to happen?

Here's my requirement on the code:
1) Must be able to merge all the file csv file in on shoot,
2) Data needed start from 11th row onward,
3) after open each file, copy the data from 11th row and onward, copy
and paste it into MasterCSV sheet, the next data will be paste to the
same column but next row to the previous data copied...

I attach one of my file into the thread...

Go back to your other thread, the one titled "Excel VBA macro to auto import
new CSV file from fixed directory". (You'll notice that I'm the only person
who replied.) Take a look at the code that I posted there. *ACTUALLY TRY
USING THAT CODE.* If you have questions about that code, REPLY IN THAT OTHER
THREAD.

--
We have software testers?

VBA code To Merge All .csv files into a single excel sheet (datastart from row 11th)
 

We saw your post and realized that with a slight modification, one of
our products could do what you wanted. We just did the modification
so that it is available. The product is the Consolidation Assistant.
With it, you can select your CSV files and specify the rows to include
from the first top of the first CSV file, and then to exclude from all
subsequent CSV file. The link to the Consolidation Assistant's page
is http://www.add-ins.com/consolidation_assistant.htm

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


On Jun 3, 7:56*pm, windslayer
wrote:
Hi All VBA experts:
I have 1000+ data files stored in csv. All of these files have the same
structure& format.
I use a vba code written by abousetta

To merge it into one spreadsheet automatically, but the problem is,the
data i needed start only from row 11 onward.
Is there a way to modify the code to allow this to happen?

Here's my requirement on the code:
1) Must be able to merge all the file csv file in on shoot,
2) Data needed start from 11th row onward,
3) after open each file, copy the data from 11th row and onward, copy
and paste it into MasterCSV sheet, the next data will be paste to the
same column but next row to the previous data copied...

I attach one of my file into the thread...







Option Explicit

Sub CombineCSVFiles()

Dim sCSV$, sCombCSV$, iFF%
Dim myFolder, myFile, arrCSV
Dim myRange As Range
Dim fso As Object
Dim fPath As String

' Turn off some Excel functionality so your code runs faster
With Application
.ScreenUpdating = False
.DisplayStatusBar = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

' Use File System Object to choose folder with files
Set fso = CreateObject("Scripting.FileSystemObject")

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
.InitialFileName = ThisWorkbook.Path & "\" ' Default path - Change as
required
.Title = "Please Select a Folder"
.ButtonName = "Select Folder"
If .SelectedItems.Count 0 Then fPath = .SelectedItems(1) & "\" Else
Exit Sub
End With

' Open each file consequently and merge into a variable
iFF = FreeFile
Set myFolder = fso.GetFolder(fPath).Files
For Each myFile In myFolder
If LCase(myFile) Like "*.csv" Then
sCSV = Space(FileLen(myFile))
Open myFile For Binary Access Read As #iFF
Get #iFF, , sCSV
sCombCSV = sCombCSV & sCSV
Close #iFF
End If
' Loop through all files in folder
Next myFile

' Convert variable to array
arrCSV = Split(sCombCSV, vbCrLf)

' Paste data back to Excel
Set myRange = Range(Range("A" & Range("A" & Rows.Count).End(xlUp).Row +
1), Range("A" & Rows.Count).End(xlUp).Offset(UBound(arrCSV) + 1))
myRange = Application.Transpose(arrCSV)
myRange.TextToColumns DataType:=xlDelimited,
ConsecutiveDelimiter:=False, Comma:=True

' clean up
myFile = vbNullString
iFF = 1

' Turn Excel functionality back on
With Application
.DisplayStatusBar = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub

--
windslayer- Hide quoted text -

- Show quoted text -