open a csv and compare the data with my existing worksheet.

Hi all,

This may be a long post to try to describe the problem I'm tasked with solving.....

I have an excel workbook which contains details of my customers who have all opted to receive emails from me from time to time.
The worksheet is set out to look like a database and has the following 'field' titles...

company, first name, last name, address 1, address 2, address 3, city, postcode, email, telephone.

When I want to send out emails, I use swiftpage to send in bulk. around 200 at a time.

I export a .csv from excel to swiftpage and sent out my bulk email.

After this, I get a csv file from swiftpage with the following 'field' names...

company, first name, last name, email, openings (the amount of times my customer opened the email.)

The .csv file generated by swiftpage doesn't include the phone number which is a big problem for me as a large part of my business involves calling the people who read the email.

What I would like to do is write a macro to compare the .csv generated with swiftpage with my workbook, create a new sheet and place the matches in the new sheet.

Is this possible?

Would I be able to add a file browser control to my form to browse for the .csv file or would I need to ensure it's saved in a certain location and code that into my macro? *edit* I found the answer to this, I can use

Please Login or Register  to view this content.

*edit*

can macros create a new sheet and name it or would I need to do this manually?

I don't really know where to start other than opening the .csv for reading then comparing each email address with my existing workbook and copying the row with the matching email address into a new sheet.
Anyone got any advice for me? I'm not asking anyone to write the code for me but if anyone could point me in the right direction I would be very grateful.

I will of course share my code when the macro is complete. If someone can learn from it in the future that would be a good thing.


Many thanks in advance,

Paul.

If you can upload a sample csv file and a sample of the workbook your using to update/create a new file, so we can see the data structure of the files it would help us to give a solution. Of course replace sensitive data with fictitious data.

Will the ever be more then one email address of the same address?

Ok so the csv file will only have one unique email address? Will you be running this code on a server? Also if you can upload a cut down csv file and a bigger xls file so I can test with it will save me from having to mockup the worksheets.

What should the output file look like?

In the attached example are 2 files. One csv and a xlsm file. Being Im not sure of your expected output I just added the openings to the activesheet in the xlsm file.

To test open the xlsm file, click the button and browse to the csv file I included in the zip file.

@sheffieldlad

Your wecolme, and by all means you can post the solution/code. I would have but I deleted the workbooks I was working on that had the code.