Solved

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

Posted on 2014-10-02
4
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

695 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