Convert text file to CSV and extract data loop macro help

[Savan87]

Hello,

I have a folder containing a number of text files, I have attached a dummy sample for your reference (Please note the actual text file contains 290 rows of data)

I am seeking a piece of code that extracts a particular cell from each textfile and then stores this as a cell in a new excel spreadsheet.

For example I have 4 text files in the folder.
Test_1961-01_Actual.txt
Test_1961-02_Actual.txt
Test_1961-02_Actual.txt
Test_1962-01_Actual.txt

I need a piece of code that deletes the first 6 rows (containing irrelavent information), converts Test_1961-01_Actual.txt to a CSV format (delimiting by spaces) and then looks up a value based on a particular row and column i.e. row 196, column 155 and then stores this cell value in a new workbook in cell a1.

Next I want the code to go through the next text file Test_1961-02_Actual.txt, look up the same row and column, storing the value in cell a2 of the previously created workbook

I need the code to loop through the all the text files in the folder extracting the corresponding cell.

What is this the best way to achieve this, could anyone help me with the piece of code? I am quite happy to modify a piece of code I just struggle writing it from scratch

Thanks a lot for any assistance you can provide

Test-1961-01_Actual.txt

[Savan87]

Is it possible to export directly from a text file if I know the row and column location of the data or is it necssary to convert the text file to a csv so VBA can identify the respective location of the data.

I want to extract the data in row 185 and column 5 from each respective text file (i.e. cell E185 after deleting the first 5 rows of the text file and converting it to a csv)

[Tinbendr]

Your record size isn't panning out.

If I pull the file into Excel, it's 125 columns X 54 rows. (48 if you skip the header.)

Where is the actual info then? There is no Row 125 or Column 155.

[Savan87]

The actual text files (after converting them to excel format) run from row 1 to 296, column A to FX - the text file I linked was the largest file I could upload and as a result does not include all the data.

The record sizes are 180 columns by 296 rows, the file I uploaded contains just 54 rows - I deleted the rest

Anyone can help?

[Savan87]

Still no luck with this , is it worth uploading the much larger text file to a file sharing site or is the the smaller file sufficient?

Any assistance would be greatly appreciated

[Savan87]

Shameless bump

[Tinbendr]

Please Login or Register  to view this content.

[Savan87]

Thank you very much David for your assistance

The code works like a charm and is very easy to follow