Need assistance with converting a fully functioning Excel VBA routine in Access VBA (syntax)

[skydivetom]

Hello:



In another forum (i.e., ExelForum.com), forum guru/contributor **jindon** (as well as **beyondExcel**) assisted me with developing a BRILLIANT VBA routine which allows to import messy syslog files into a clean matrix format.

Each (actual) syslog file contains of hundreds/thousands of "messages" where each message (using start/end tags) may contain data across n "rows" (records).

The VBA routine (in MS-Excel) uses RegExp to successfully perform the following function(s):
a) parse through the entirety of a single syslog (containing n rows)
b) properly delimit multi-line data into columns
c) concatenate several data elements into a single column and
d) move each messages' sublines into the same row (with only pertinent data)

Ultimately, the fully functioning Excel routine 'cleans up' messy log files and transforms their data into matrix format for analysis purposes.

Please see https://www.excelforum.com/excel-pro...th-regexp.html for additional details.

// break break //

At this point in time, it would be even better to convert the Excel VBA code into Access VBA code. In a perfect world (so to speak), the only addition to the code would be to include looping through **all** syslogs (in a directory) and importing them into separate tables (vs. importing each syslog file one-by-one into a worksheet).

Attached are the following files:
- "01_Excel _Method.zip" incl.:
* Subfolder containing 4 syslog (sample) files such as {ac.log; other.log; ps.log; testingMoreColumns.log}
* File "Import Syslog Data vA01.xlsm" -- which contains a FULLY FUNCTIONING VBA routine that imports syslog data into a worksheet

- "02_Access_ Method" incl.:
* Subfolder containing the same 4 syslog (sample) files
* File "Import Syslog vA01.accdb" -- which contains a single form with some originally proposed Excel-to-Access *converted* VBA code

// break break //

Summary of the existing process in MS-Excel:
1. Open the XLSM
2. In column A, click on the red command button "Import Syslogs"
3. From the browse dialogue box, locate the subfolder containing the syslogs
4. Select one (1) of the syslogs and click "Ok".
5. Repeat this process for any of the four (4) syslog files.
6. Note: Obviously, when executing the syslog one-by-one, the output is always cleared/overwritten.

Please note the following about the syslog files:
a. "ac.log" contains 49 lines but they will be converted into 15 rows (in Excel)
b. "other.log" contains 6 lines but they will be converted into zero (0) rows (given the file does not include any actual messages).
c. "ps.log" contains 29 lines but they will be converted into 4 messages (in Excel)
d. "testingMoreColumns" contains 16 lines but they will be converted into 5 messages. Also, for testing purposes only, this is the only file with has additional data that translates into additional columns.

// break break //

Per https://www.excelforum.com/excel-pro...th-regexp.html (post #42), Excel/Access guru **jindon** has already proposed some VBA code (converted from XLSM into ACCDB format).

This code is already included in the ACCDB's form "F103_Syslog_ImportData". I presume this is nearly the 100% solution. However, at this time, the Access version extracts the .log files into another version of .log files (in the same directory as the ACCDB file is in.

That is, upon clicking on form's command button, it looks like nothing is happening but the temp files (with "pipe" as part of the filenames) are created. How would be code have to be changed so that the original .log files are imported into tables.

// break break //

My goal for the Access version is the following:
1. Upon clicking the command button, I will locate the subfolder containing the .log files.
2. Once I click "Ok", the VBA routine kicks in and reads in the information from -- in this case -- the 4 .log files.
3. At this point, for each .log file, I should end up with a (new) table where the file naming convention = "tbl_syslog_" + log's filename.
4. For example, the data from the 4 log file would be stored in {tbl_syslog_ac; tbl_syslog_other; tbl_syslog_ps; tbl_syslog_testingMoreColumns}.
5. Finally, given that the log files are NOT in matrix format, there are obviously no "headers" that could be used as fieldnames. For now, I'm fine w/ using auto-field names such as "FIELD1", "FIELD2", etc.

Again, jindon's VBA (Excel version) already addresses 100% of the requirements. Also, I believe, jindon's proposed VBA (in the Excel forum) is also very close to the 100% solution.

However, at this time, the code does not yet execute. Does anyone have a recommendation how to tweak it so that I ultimately get the same exact Excel results but now in the Access version (well, with 4 tables instead)?

Thank you for your assistance in advance.

Cheers,
EEH