Solved

I need help combining different excel sheets with similar file name into one spreadsheet

Posted on 2014-10-02
4
200 Views
Last Modified: 2014-10-03
I have about 20 different folders all under another folder "email_lists". The folder's name is the first and last name of a person. (Example folder name: Ben Curtis)

Within each folder is about 10 - 11 .csv files with file names such as: Ben Curtis 001.csv and Ben Curtis 002.csv and Ben Curtis 003.csv and so on, all the way to 010 or 011.csv.

What I need is a function that will go through each folder and copy all the email address (only the email address) from each .csv file and paste it into one big .csv spreadsheet, then save that big spreadsheet as: "All Ben Curtis.csv."

Each Ben Curtis 001.csv type of spreadsheet has about 1,000 rows containing first name (Col A), last name (Col B) and email address (Col C).

I only need the email address.
0
Comment
Question by:mabehr
  • 2
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40358638
This code will prompt a user to browse and select a valid CSV file from a directory (any file can be chosen ).  The program will then open each CSV file (except an already consolidated file, if it exists), copy the emails from column C and insert them into column A of a template sheet.  When done, the template sheet will be saved as a CSV file - in the same folder - using the "All firstname lastname.csv" rule specified.
Option Explicit
Sub Combine_CSV_Emails()
    Dim strMaster, strFile, strFileName, strFilePath As String
    Dim strNewFileName As String
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    strMaster = Application.ActiveWorkbook.Name
    Range("A1").Select
    
    strFileName = Application.GetOpenFilename("CSV Files (*.csv*), *.csv")
    If strFileName = "" Or strFileName = "False" Then Exit Sub
    strFilePath = Left(strFileName, InStrRev(strFileName, "\"))
    strNewFileName = Mid(strFileName, InStrRev(strFileName, "\") + 1, 100)
    strNewFileName = "All " & Left(strNewFileName, InStrRev(strNewFileName, " ") - 1) & ".csv"
    
    strFile = Dir(strFilePath & "\*.csv*")
    Do While strFile <> ""
        If Left(strFile, 3) <> "All" Then
            Workbooks.Open Filename:=strFilePath & "\" & strFile
            'get data from column c and copy into master workbook
            Range("C1", Range("C1").End(xlDown)).Copy
            Workbooks(strMaster).Activate
            Sheets("Main").Select
            ActiveCell.PasteSpecial
            ActiveCell.End(xlDown).Offset(1, 0).Select
            Workbooks(strFile).Close SaveChanges:=False
        End If
        strFile = Dir
    Loop
    
    'save master workbook/sheet as another CSV file and close
    Sheets("Main").Select
    Sheets("Main").Copy
    ActiveWorkbook.SaveAs Filename:= _
        strFilePath & strNewFileName, FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close SaveChanges:=False
    Range("A1").Select
    Range("A1", Range("A1").End(xlDown)).ClearContents
    Range("A1").Select
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Sheets("Main").Select
    MsgBox "Process complete.  " & strNewFileName & _
           " saved in same directory as source files.", vbOKOnly, "Import CSV Data"
End Sub

Open in new window


A macro-enabled workbook with this functionality is attached.

Regards,
-Glenn
EE-Import-CSV-Email.xlsm
0
 

Author Closing Comment

by:mabehr
ID: 40360484
That worked perfectly, Glenn Ray and saved me TONS of time.
I really appreciate your help.

Thank you!!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40360496
You're welcome.  Don't go spamming anybody!    ;-)

Have a good weekend,
-Glenn
0
 

Author Comment

by:mabehr
ID: 40360514
Hmmm... you now have me puzzled.

"spamming anybody"??
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now