My very noddy Macro to import CSV files:
As part of a recent project, I was required to, as much as possible, automate the production of an excel spreadsheet. Currently we have no facility to do this directly. The plan ended up being to export the many queries as CVSV files. Every month these CSV files are then send to the relevant internal staff via e-mail as a password protected zipped file.
The process of importing these into a spreadsheet was achieved using a macro. It has been a very long time since I have used VBA for anything because I have always been able to achieve it in SQL or python and as such my macro was cobbled together from stackoverflow answers and a little research. The end result however is a functional piece of code that loops through a range within a config tab to grab a csv and import it into the named tab until it reaches the end of the range. This ends up with 8 tabs being populated from 8 CSVs and minimal work for the analysts.
In total it took less than a week, which had meetings, to develop the SQL, ETL and macro and it will save the end user around a day a month.
Here is the code I used:
No comments:
Post a Comment