Text to Excel VBA

[john3j04]

Hello,

I have a text file with multiple checks. For each check I have something like this...

Group ID: v-1234
Title: test
ID: 123
Version: 6.2
script output
Status: Pass

Group ID: v-9865
Title: test2
ID: 125
Version: 6.6
script output
Status: Fail

I have an excel spreadsheet that needs to be filled out. Rows 5 through 317 each have a Group ID:, along with the other fields. How can I take the Group Title from my text file and put it in the corresponding row, and also put the status in as well? In the spreadsheet, Pass is column H and Fail is column I. Also, so there a way to tell it for each set of information in the text file, to take the line between Version and Status, and put that into the column S for each set of information? Any help would be greatly appreciated.

Thanks,
John

[Tinbendr]

Upload a sample sheet. Include a sample text file if possible.

Go Advanced->Manage Attachments.

[john3j04]

Here is the sample spreadsheet. If the text file says that the Status is a pass, then it should place a lowercase x in the Complete field on the spreadsheet. If the text file says that the status is a fail, then it should put a lowercase x in the Ongoing field on the spreadsheet. If there is any output from the commands (after they are ran) in the text file between the required settings and status fields, it should output that data into the Remarks section of the spreadsheet, All of this should be according to which Group ID (Vulid) is in the text file...and corresponds to the Vuln# on the spreadsheet. If the status is not either Pass or Fail, then it should skip to the next set of text in the text file. I will attach the sample text file in another response. Thanks!
John

[john3j04]

ok, it keeps telling me that the text file is an invalid file....so here is the text that was in the .txt file. I hope this helps!

Group ID (Vulid): V-1075
Group Title: Display Shutdown Button
Rule ID: 214654231
Severity: III
Rule Version (STIG-ID): 2156
Rule Title: The shutdown option will not be available from the logon dialog box.
Required Setting: Must be set to 0.
ouput from a command in batch
Status: Pass

Group ID (Vulid): V-1089
Group Title: Legal Notice Display
Rule ID: 537424
Severity: II
Rule Version (STIG-ID): 1685
Rule Title: The required legal notice must be configured to display before console logon.
Required Setting: Must have full usage statement.
ouput from a command in batch
Status: Fail

Group ID (Vulid): V-1090
Group Title: Caching of logon credentials
Rule ID: 21894
Severity: CAT III
Rule Version (STIG-ID): 21843
Rule Title: Caching of logon credentials will be limited.
Required Setting: Must be set to 1.
ouput from a command in batch
Status: Pass

[Tinbendr]

Test this out. See attachment.

[john3j04]

wow! that works great, but is there a way to have it automatically pull from a defined text file? Lets say that samplefile.txt was located at C:\TEMP\Logs. Instead of running the macro, it would be nice if when you open the workbook it automatically ran the macro on the defined text file. If you can do that, then that would be perfect!

[Tinbendr]

wow! that works great, but is there a way to have it automatically pull from a defined text file? Lets say that samplefile.txt was located at C:\TEMP\Logs. Instead of running the macro, it would be nice if when you open the workbook it automatically ran the macro on the defined text file. If you can do that, then that would be perfect!
replace Fname = Application.GetOpenFilename("Text Files (*.txt), *.txt") with FName = "C;\TEMP\Logs\Samplefile.txt"
In the ThisWorkBook code module, insert this code.
Private Sub Workbook_Open()
Module1.ImportData
End Sub