I would like some feedback on my current process and hopefully some advice on how to improve.
What I have is hundreds of excel reports all containing specific data in the same fields.
For example: A1 = name, B2 = Postcode
All of the reports are in individual folders.
In explorer I copy file path of all the folders and paste them into excel all at once.
I then open the hyperlink in the excel document to each of these folders manually and copy over the path of the document inside. This is pasted next to the file path for its folder. This is so I have a full list of all the needed documents within the excel file and I methodically work through them.Once this list is obtained I use the following code to pull the data from the relevant place:
This all works perfectly but it means for the code to work I have to manually open each document, I then run a macro which will move the row onto the next sheet and paste it as values (so the data doesn't disappear when I close the page.)
Due to my lack of knowledge in excel and in VBA I'm sure there are ways I can improve what I'm currently doing. I have highlighted in bold two areas I would like to improve efficiency but I'm also willing to restructure the whole project.
The data is going to be used for mail merge and that aspect of the project is all set up.
I would appreciate anyone's input.
Thanks! Andy,