Can anyone recommend VBA code that will loop through a directory file structure and list every Excel workbook, path, info to put into a database table?
A nice Excel Template was used to record CO2 emissions as the regulation started. The template was really well thought out.
Over the years, it turned from one person to a half dozen.
The Excel templates are highly organized in a file structure for Years, States, Location ID, and all of the data.
My efforts to design a SQL Server DB is about completed.
I have past experience to write code to open an Excel workbook and harvest the data and then close it (copying the data to SQL Server).
In the past, I had an interface that allowed a user to search and choose the directory and choose the one workbook to open. There are so many of these due to years of multiple people entering data as the CO2 tracking grew.
What is different this time: many people with thousands of Excel Workbooks. It would be better to automate the process of iterating through each directory, each workbook to harvest the information.
The interface will be Access 2010 (vba) with a SQL Server 2008 back end.
This code may be a one-time process to make the conversion.
Any references, examples, suggestions, web-links ... would be appreciated. I am also open to any recommended books or software tool purchaces.
A nice Excel Template was used to record CO2 emissions as the regulation started. The template was really well thought out.
Over the years, it turned from one person to a half dozen.
The Excel templates are highly organized in a file structure for Years, States, Location ID, and all of the data.
My efforts to design a SQL Server DB is about completed.
I have past experience to write code to open an Excel workbook and harvest the data and then close it (copying the data to SQL Server).
In the past, I had an interface that allowed a user to search and choose the directory and choose the one workbook to open. There are so many of these due to years of multiple people entering data as the CO2 tracking grew.
What is different this time: many people with thousands of Excel Workbooks. It would be better to automate the process of iterating through each directory, each workbook to harvest the information.
The interface will be Access 2010 (vba) with a SQL Server 2008 back end.
This code may be a one-time process to make the conversion.
Any references, examples, suggestions, web-links ... would be appreciated. I am also open to any recommended books or software tool purchaces.
