• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to extract data from TOR file and place into Archive master file

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
 
Sam Longstaff
1) Open a new Excel-file
2) Start Marco-recorder
3) Do You above named steps (( skip Your #7 ))
4) Stop Macro-recorder
5) You'll have a basic code for You
>
After that -
If You would need more hints -
You should upload here a TOR-file too.
 
OK, so I did as you said above and this can be found in the macros (macro2) in the archive 1.xlsm file. I have also attached a FILES IN FOLDER LOGGER Archiving.xlsm file. This has a macro for finding the files in the folder (in this case on the disc in the drive) and then a second macro to consolidate the contents of the files in to one worksheet. I'd like to amend this second macro so that it takes the content of each file, copies the cells (H3:H98,M3:M98,R3:R98) and pastes them in to a new worksheet in the archive 1.xlsm and then names this new worksheet with the first 8 characters of the file name (e.g 070120-1). It would then repeat the process for any file that has a "Y" next to it in the "load files" column of the logger workbook.

Is this possible?
 

Attachments

  • Archive 1.xlsm
    16.8 KB · Views: 0
  • FILES_IN_FOLDER_LOGGER Archiving.xlsm
    26.2 KB · Views: 1
Sam Longstaff
What is missing?
... except how to handle more than one file.

Wasn't You thread: VBA to extract data from TOR file and place into Archive master file ?
Should some one have own TOR-files or Archive master-file? ( terms )
Should every time paste to the new sheet as You've written? ... if You run 'macro' more than once ... hmm?
... or as later You've written, to named sheets ... should it avoid to overwrite?

All needed information should be clear to do something and it should able to test too.
So far - those needs more information.
 
Yes it should not overwrite.
The TOR files are located on a CD in the D: drive, when these are manually opened a msg box appears stating that file extension is not recognised, accept if trusted. What would need included in the VBA to facilitate this?
After this msg box another appears asking what format to open the file in: xml table or read only workbook. When doing this manually I would choose read only work book. What VBA script is needed for this?
 
Sam Longstaff
Did You missed ... What is missing?
Did You wrote like:
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.

How to test above #1 & #2 without any TOR-file?
ps I cannot read CDs neither I don't have D:-drive.
 
I wasn't able to load the TOR file on here as the extension was not supported. I have put it below as an xlsx
 

Attachments

  • Copy of 070120-1_Aliquot0.6_0.01_01072020_122000.xlsx
    21.5 KB · Views: 3
Back
Top