Open all CSV files in folder, and merge to one master file.

[ChrisAcheson]

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]

Oh, and each file in each zip is the same name, so i hope there is not a naming convention issue when opening.

[snb]

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]

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]

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]

See the title of your thread.
My answer pertains to csv files, not to zip-files.


Never use spaces in foldernames.

[Jan Karel Pieterse]

Perhaps this helps?
http://rondebruin.nl/win/s7/win002.htm

[ChrisAcheson]

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.