Forum Discussion
Solved
Help needed with automatically importing and transposing data into a table from a series of files.
Hello,
I have a data log that I'm building a for a lab I set up. I have been manually transferring data for the last month and need some help.
Here's a snapshot of my log:
Essentially, I have a series of files in one subfolder with the same exact name as the value in column C (@Sample Name in table). I would like to figure out a way that i can have this excel file automatically pull the data from the file matching that cell value. It needs to be pasted and transposed into the cell in column L (@CBC in table), filling all cells up to column AA (@THCVA in table.
The data in the source file will always be located in cells E18:E33
Source files are CSV format.
File Structure:
Log: .../QC Lab/2022 QC Lab Data Log.XLSX
Data Files ..../QC Lab/HPLC Reports/2022/[Sample Name].CSV
The year will obviously change over time, but that's something i can figure out how to edit next year.
Anway, i assume this needs to happen with macros, but my experience with macros is very limited. I would appreciate any help or guidance in achieving this automatic data transfer, as i am currently having to manually transfer 20-30 sets of data every day.
Much appreciated!
Bunnytree I would suggest you learn a bit about PowerQuery (PQ). It has built-in functionality to connect to files in folders (including sub-folders). How exactly is a bit difficult to describe in a few words and you may not find it very easy in the beginning, but once you get to know it a little, you'll love it. And, I dare to say it will be easier to learn and maintain than a VBA solution.
The link below would be a good starting point. Chapter 9 (scroll down a bit) deals with importing files from a folder.
Bunnytree I would suggest you learn a bit about PowerQuery (PQ). It has built-in functionality to connect to files in folders (including sub-folders). How exactly is a bit difficult to describe in a few words and you may not find it very easy in the beginning, but once you get to know it a little, you'll love it. And, I dare to say it will be easier to learn and maintain than a VBA solution.
The link below would be a good starting point. Chapter 9 (scroll down a bit) deals with importing files from a folder.
