Working in aseptic manufacturing for pharma. We have equipment that identifies how much pressure vials can take during the process. The machine collects that data and serially exports each individual file on each vial into a proprietary app which uses an Excel spreadsheet to chart the data.
We are looking for a way to put specific data from each file into one spreadsheet. For example, let's say Pressure = 11.9 for VialA/FileA; 10 for VialB/FileB; 11 for VialC/FileC. The data is found in column A1 = Vial# (label) and B1 Pressure#.
I need guidance on creating a macro or VBA code (or finding one that I can tweak) that will:
1) Go to each file in a folder on [Drive]://
2) Open file and copy Cell A1 and B1
3) Paste data in Cell A1 and B1 of the open Spreadsheet (or open the template but I am summing the template is already open and ready for use since the VBA or Macro will be housed in the template that will hold all data)
4) Go to the next file in the folder
5) Open the file, copy the cells per point 2)
6) Paste data in the next cells (A2 and B2) and so on...for each file in the folder.
Note: there could be 10K or more files in the folder that the macro or VBA code will need to incorporate and open until all files have been accessed and data collected out of the files and pasted into one file.
Not sure how challenging this might be. I am familiar with VBA and creating macros in MS Office Apps (not MVP but I am MS Office Software certified - expert level for 2013 and older versions--and I can catch up by reading or YouTube for anything I am rusty on).
Any thoughts on the best approach or is there existing code that I can use and manipulate?
Thank you for your help.
D. Reid
