I am receiving on a weekly basis a list of Excel spreadsheets from a supplier (around 40 spreadsheets) with data in a specific format. Since the format is a bit complex and can't be converted to CSV by itself (merged cells, useless information...), I created a matrix of formulas that can convert the content of a sheet into a flat format - I can copy/paste this matrix in an other sheet.

Since I'd like to convert my 40 weekly sheets into a flat CSV format, I was wondering if there was a way to programmatically generate a CSV file using a tool where I could do the following process:

  • Excel spreadsheet as input;
  • Use a formula matrix as a data processing;
  • Generate a CSV file as an output.


  • If you can write a macro (VBA) in excel for the formulas that convert the excel data to do the data processing - you can certainly achieve this. The question is Do you intend to pass all the 40 weekly sheets through this mechanism manually or do you want a script that when given the location of all the 40 files - will do it and generate CSV files are store it for you?
    – Prasanna
    Commented Apr 21, 2015 at 13:35
  • @Prasanna Optimally, I'd like to forward the email with attachments to an email handler that would download the attachments, convert the Excel to flat CSVs automatically.
    – jpmonette
    Commented Apr 21, 2015 at 15:18

1 Answer 1


This type of system approach is possible, but is trickier than most would expect. To do this will need a lot of patience and a good framework. Since you do not have source control, the input files will have subtle variations over time. Do not try to get 100% of the work done the first time. Grow into it as you learn more about the data structure and how the files change over time.

  • Step 1. make a backup of the initial files in a folder called Raw. Never use Raw with any macro. Instead, always work with a copy. The ability to see the pristine starting files and re-do / compare will be major for someone learning and working with macro’s.
  • Step2 Make a macro to get rid of useless information and name it "Remove_Useless". Easiest way is to use a record macro and then update it to be in generic format. This does require some knowledge of how to work with macro's. There are several beginning macro sites that can go through the steps needed to make this macro.
  • Step 3. run the macro (taking care for backup & version control) with each of the other files. Modify macro as needed for it to work 100%.
  • Step 4. Once you have a macro working and it works without tweaking on a second batch of files. The start adding more macro’s while giving them useful names such as "Remove_Merge", "Save_as_CSV", etc. Following the same steps as above.

  • Step 5. Once have a complete set of macro's, then need 3 more macros. So far all macro's have been run by themselves. Make a Do_It_All macro that will run all macro's. And a Pre_Check macro that checks if there has been a significant variation in the source files. The Pre_check will call the Do_IT_All if everything ok, else call "Alert_Boss_Files_modified"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .