0

Found this and it helps but does quite get the complexity that I require. How to import specific text from files in to excel?

What i am doing is scanning a range of IPs with another script which will create a single text file for each server with that server's hardware profile. That script is not the problem and works correctly at this time.

Then I want a script that will loop through all of the files created above and import only specific data into an Excel spreadsheet.

Sub ReadFilesIntoActiveSheet()
Dim fso As FileSystemObject
Dim folder As folder, file As file, FileText As TextStream
Dim TextLine As String
Dim cl As Range

Dim num As Long ' numerical part of key, as in "Ann:"
Dim col As Long ' target column in Excel sheet
Dim key As String ' Part before ":"
Dim value As String ' Part after ":"

' Get a FileSystem object
Set fso = New FileSystemObject

' Get the directory you want
Set folder = fso.GetFolder("D:\YourDirectory\")

' Set the starting point to write the data to
' Don't write in first row where titles are
Set cl = ActiveSheet.Cells(2, 1)

' Loop thru all files in the folder
For Each file In folder.Files
    ' Open the file
    Set FileText = file.OpenAsTextStream(ForReading)

    ' Read the file one line at a time
    Do While Not FileText.AtEndOfStream

        TextLine = FileText.ReadLine 'read line

        key = Split(TextLine & ":", ":")(0)
        value = Trim(Mid(TextLine, Len(key)+2))
        num = Val(Mid(key,2))
        If num Then key = Replace(key, num, "") ' Remove number from key
        col = 0
        If key = "From" Then col = 1
        If key = "Date" Then col = 2
        If key = "A"    Then col = 2 + num
        If col Then
            cl.Offset(, col-1).Value = value ' Fill cell
        End If
    Loop

    ' Clean up
    FileText.Close
    ' Next row
    Set cl = cl.Offset(1) 
Next file
End Sub

The way I import that file into Excel looks similar to this:

excel clip

This is a small clip of a long file with lots of sections. This clip shows clips of 2 sections. I need this vba script to be able to determine the [] section and then pull a specific line under that. And there are many sections that may be nearly identical such as [InstanceID: DIMM.Socket.A1] and [InstanceID: DIMM.Socket.A2] and many of the sub lines may be the same as well such as Model may be listed under many different types of components.

How can this script go through and pull the Model from under [InstanceID: DIMM.Socket.A1] but skip all of the rest of the similar sections. And then pull BIOSReleaseDate and BIOSVersionString lines from the [InstanceID: System.Embedded.1] section. I assume I can use the rest of the original script to put these data points into specific columns such as Then col = 1 or Then col = A.

Thank you!

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
Brad Goodman
  • 1
  • What is the relationship between the code you posted and the screenshot? – Ron Rosenfeld Mar 15 '18 at 01:11
  • None at the moment. But I saw that the code could be adapted to possibly be close to what I need if I had a single section of info. Having multiple sections and some with similar tags adds a lot of complexity. – Brad Goodman Mar 15 '18 at 17:55
  • Suggest you adapt it to work for a single section first. Then, with a better idea of what you are doing, we can help you adapt it to work for multiple sections. – Ron Rosenfeld Mar 15 '18 at 20:11

0 Answers0