Hi, I'm not well verse with VBA and like to request for great help to make a script.
I will like an Excel file (let’s call it Extractor.xlsm) that can read log files in subfolders in the same directory.
Directory Description:
Subfolders are named by year and the log files within each subfolder are named by date with this naming format: Log_yyyy-mm-dd.txt
Log File Description:
In each log file, there are a few lines of random text at the beginning. This is not constant.
Then a table. There isn’t anything after the table except for an empty newline.
The table has some heading, a dash line and the content. There will always be a dash line separating the heading and content. The dash line is unique in that it will be the first instance where more than 2 consecutive “-“ characters can be found.
Only the heading and table body should be transferred from the log file to the Excel sheet.
Example of the log file is shown below. The table has some random values but each column width is exactly as shown and will remain the same for all log files. The columns are separated by a minimum of 2 consecutive spaces.
Excel File Description:
In the Excel sheet, cell L1 has a date.
When the cell L1 date value changes by user input, columns A to J should be automatically populated with the respective log file table heading and table body starting with the heading in row 1.
(For reference, manually, this is achieved by opening the desired log file, deleting the dash line row, copying the heading and table body to clipboard, select Excel sheet cell A1, then Paste using Text Import Wizard with Fixed Width option selected.)
Example of desired Excel output is shown below.
If a future date is entered and no log file is found, columns A to J should be blank with no other error pop-ups.
Above is the main request.
Extra:
If the VBA script is flexible to accommodate the below it will be even better.
Older log files doesn’t have the second last column. Example shown below.
So columns A to I should be automatically populated leaving columns J blank.
Thank you! ?
I will like an Excel file (let’s call it Extractor.xlsm) that can read log files in subfolders in the same directory.
Directory Description:
Subfolders are named by year and the log files within each subfolder are named by date with this naming format: Log_yyyy-mm-dd.txt
Example of folder content:
\2019\Log_2019-01-01.txt
\2019\Log_2019-12-31.txt
\2020\Log_2020-01-01.txt
\2020\Log_2020-01-02.txt
\2020\Log_2020-04-12.txt
Extractor.xlsm
Log File Description:
In each log file, there are a few lines of random text at the beginning. This is not constant.
Then a table. There isn’t anything after the table except for an empty newline.
The table has some heading, a dash line and the content. There will always be a dash line separating the heading and content. The dash line is unique in that it will be the first instance where more than 2 consecutive “-“ characters can be found.
Only the heading and table body should be transferred from the log file to the Excel sheet.
Example of the log file is shown below. The table has some random values but each column width is exactly as shown and will remain the same for all log files. The columns are separated by a minimum of 2 consecutive spaces.
Code:
Some text (this line present in all files); don't process this line; process table heading and body but not dash line
More text (this line not necessary present in all files); don't process this line
Proc Cess Head Ing But Dont Proc Cess Dash Line
-------------------------------------------------------------------------------------
A9HFS7 D-9930 DF8W ABC123 123 456 01:10 02:26 123 9345
SU38SF A343 0 0 02:35 03:22 492 8943
SJ3SJ9 ABCD-12345 39D 93F039 25444 0 11:22 15:36 356 3842
DIWEW8 243-45 R2 -800 2243 14:33 15:38 546 5425
2IS920 00000000 93940 0 16:33 18:33 ??? ????
S9829E 12-34 I899 RWE34 0 2345 20:22 23:22 486 4888Excel File Description:
In the Excel sheet, cell L1 has a date.
When the cell L1 date value changes by user input, columns A to J should be automatically populated with the respective log file table heading and table body starting with the heading in row 1.
(For reference, manually, this is achieved by opening the desired log file, deleting the dash line row, copying the heading and table body to clipboard, select Excel sheet cell A1, then Paste using Text Import Wizard with Fixed Width option selected.)
Example of desired Excel output is shown below.
If a future date is entered and no log file is found, columns A to J should be blank with no other error pop-ups.
Proc | Cess | Head | Ing | But | Dont | Proc | Cess | Das | Line | 12/4/2020 | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| A9HFS7 | D-9930 | DF8W | ABC123 | 123 | 456 | 1:10 | 2:26 | 123 | 9345 | Above is cell L1 | |
| SU38SF | A343 | 0 | 0 | 2:35 | 3:22 | 492 | 8943 | ||||
| SJ3SJ9 | E359ER | 39D | 93F039 | 25444 | 0 | 11:22 | 15:36 | 356 | 3842 | ||
| DIWEW8 | 243-45 | R2 | -800 | 2243 | 14:33 | 15:38 | 546 | 5425 | |||
| 2IS920 | 0 | 93940 | 0 | 16:33 | 18:33 | ??? | ???? | ||||
| S9829E | 12-34 | I899 | RWE34 | 0 | 2345 | 20:22 | 23:22 | 486 | 4888 |
Above is the main request.
Extra:
If the VBA script is flexible to accommodate the below it will be even better.
Older log files doesn’t have the second last column. Example shown below.
So columns A to I should be automatically populated leaving columns J blank.
Code:
Some text (this line present in all files); don't process this line; process table heading and body but not dash line
More text (this line not necessary present in all files); don't process this line
Proc Cess Head Ing But Dont Proc Cess Line
-------------------------------------------------------------------------------
A9HFS7 D-9930 DF8W ABC123 123 456 01:10 02:26 9345
SU38SF A343 0 0 02:35 03:22 8943
SJ3SJ9 ABCD-12345 39D 93F039 25444 0 11:22 15:36 3842
DIWEW8 243-45 R2 -800 2243 14:33 15:38 5425
2IS920 00000000 93940 0 16:33 18:33 ????
S9829E 12-34 I899 RWE34 0 2345 20:22 23:22 4888Thank you! ?
