Solved

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

Posted on 2014-10-02
4
193 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

759 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

20 Experts available now in Live!

Get 1:1 Help Now