Sam Longstaff
Member
I am looking for VBA code to perform a multi-step task extracting data from a TOR file. The TOR files are saved to a disk that I have to then select to open as a read only workbook, select the columns of data I want and copy these in to a new worksheet in my archive master file work book. I would like to be able to build a VBA code that I can apply as a macro to a button that will perform the following steps:
1. Select the file from the disk and open it.
2. Find the columns with the following headers: /Object/TestOrder/AliquotInfo/@SampleId, /Object/TestOrder/AliquotInfo/Well/@DeepWellLocation and /Object/TestOrder/AliquotInfo/Well/@WellStatus.
3. Copy the data in these 3 columns from row 2 down to row 98.
4. Open my archive master file workbook (file location C:\Archive\Archive Master File.xlsx) and in a new worksheet put the headers in A1-C1: Sample number, Location, Volume status.
5. Paste the copied data in A2:C98
6. Name the worksheet with the same name as the file opened in step 1.
7. Repeat the process for each of the files on the disk.
8. Save the file before closing.
The disk will be used multiple times adding more files on to it, with this in mind it may be best to have a solution that allows for the files that require transfer to be manually selected. Also each new worksheet must have a unique name and can not be given a name already in use by another worksheet.
Hope this is clear and not too big an ask, any help greatly appreciated.
Thanks,
Sam
1. Select the file from the disk and open it.
2. Find the columns with the following headers: /Object/TestOrder/AliquotInfo/@SampleId, /Object/TestOrder/AliquotInfo/Well/@DeepWellLocation and /Object/TestOrder/AliquotInfo/Well/@WellStatus.
3. Copy the data in these 3 columns from row 2 down to row 98.
4. Open my archive master file workbook (file location C:\Archive\Archive Master File.xlsx) and in a new worksheet put the headers in A1-C1: Sample number, Location, Volume status.
5. Paste the copied data in A2:C98
6. Name the worksheet with the same name as the file opened in step 1.
7. Repeat the process for each of the files on the disk.
8. Save the file before closing.
The disk will be used multiple times adding more files on to it, with this in mind it may be best to have a solution that allows for the files that require transfer to be manually selected. Also each new worksheet must have a unique name and can not be given a name already in use by another worksheet.
Hope this is clear and not too big an ask, any help greatly appreciated.
Thanks,
Sam