How to output a CSV file from a matrix of Excel formulas using multiple files?

Question

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.

Thanks

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"