PDA

View Full Version : Open all CSV files in folder, and merge to one master file.



ChrisAcheson
08-10-2017, 06:48 AM
Hi

I am currently using the below code, in which i have to open all my csv's rename them, and then pull into each tab.

Then run the below code to merge all of the open tabs into one master tab.


Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

For Each sht In wrk.Worksheets
If sht.Name = "Master" Then
MsgBox "There is a worksheet called as 'Master Dupes Removed'." & vbCrLf & _
"Please remove or rename this worksheet since 'Master Dupes Removed' would be" & _
"the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
Exit Sub
End If
Next sht

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
'Rename the new worksheet
trg.Name = "Master Dupes Removed"
'Get column headers from the first worksheet
'Column count first
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
'Now retrieve headers, no copy&paste needed
With trg.Cells(1, 1).Resize(1, colCount)
.Value = sht.Cells(1, 1).Resize(1, colCount).Value
'Set font as bold
.Font.Bold = True
End With

'We can start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it is Master worksheet)
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
Next sht
'Fit the columns in Master worksheet
trg.Columns.AutoFit

'Screen updating should be activated
Application.ScreenUpdating = True

Is there a way, or can you kindly point me in the right direction to:

open all csv files in folder directly from zip files into one master csv tab?

Thank you

ChrisAcheson
08-10-2017, 06:49 AM
Oh, and each file in each zip is the same name, so i hope there is not a naming convention issue when opening.

snb
08-10-2017, 12:18 PM
This is all you need:


Sub M_snb()
createobject("wscript.shell").run "cmd /c copy G:\OF\*.csv G:\total.csv"
workbooks.open "G:\total.csv"
End sub

ChrisAcheson
08-11-2017, 07:39 AM
Hi Snb, thanks for your feedback.

I must be doing something wrong.

I tried your below code. All it did was open up a blank document



Sub M_snb()
createobject("wscript.shell").run "cmd /c copy G:\OF\*.csv G:\total.csv"
workbooks.open "G:\total.csv"
End Sub


my pathway = Z:\details\Folder A

So i replaced your code to ready:


Sub M_snb()
createobject("wscript.shell").run "cmd /c copy Z:\details\Folder A\*.csv Z:\details\Folder A\total.csv"
workbooks.open "Z:\details\Folder A\total.csv"
End Sub

Is that wrong?

Thank you kindly.

ChrisAcheson
08-11-2017, 07:51 AM
Also Snb, should the code you advise, extract all the zip files in the folder and merge onto one master sheet. Or is the function of this code only to amalgamate alreazy unziped csv files.

As I am looking to drag all zip files into the said folder, and the code extracts all zips, and uses the csv files with the zips into a master amalgamated document.


Thanks for your help and advise.

snb
08-11-2017, 09:13 AM
See the title of your thread.
My answer pertains to csv files, not to zip-files.


Never use spaces in foldernames.

Jan Karel Pieterse
08-14-2017, 07:51 AM
Perhaps this helps?
http://rondebruin.nl/win/s7/win002.htm

ChrisAcheson
08-14-2017, 08:08 AM
Thanks chaps.

Snb, apologies, your right. I didnt mention the csv files are in a ziped state within the folder.



Jan, ill take a look over the link you point to. I think i looked at this before, and didnt quite meet my requirements. Will quickly revisit this though.

Thank you.