Copy data from notepad into excel

Hello platesigns,

Is the Notepad file on sheet 8f30f76f-e047-489a-9e6f-43f0ade?

If so then your text file is actually the page source for an HTML file. Is this format the same for all files in the folder?

Hello platesigns,

The attached workbook has the macro code shown below installed. There is a button on "Sheet1" to run the macro.

The macro assumes the text files are in the same folder as the workbook. If the files are not in this folder, change the macro variable Folderpath (in blue font) to where your files are saved.

Module1 Macro Code

Sub ParseHTML(ByVal FileToOpen As String, ByRef Dest As Range)

    Dim Data(4)     As String
    Dim Filename    As String
    Dim HTMLdoc     As Object
    Dim n           As Long
    Dim oDiv        As Object
    Dim PageSrc     As String
    Dim URL         As String
        
        URL = "file:///" & FileToOpen
        URL = Replace(URL, "\", "/")
        
            With CreateObject("MSXML2.XMLHTTP")
                .Open "GET", URL, False
                .Send
                PageSrc = .ResponseText
            End With
            
            Set HTMLdoc = CreateObject("HTMLfile")
            HTMLdoc.Open
            HTMLdoc.Write PageSrc
            
            For Each oDiv In HTMLdoc.getElementsByTagName("div")
                If oDiv.classname = "row" Then
                    For n = 0 To oDiv.ChildNodes.Length - 1
                        If oDiv.ChildNodes(n).NodeType = 1 Then
                            Select Case oDiv.ChildNodes(n).innerHTML
                                Case Is = "Contractor Name:"
                                    Data(0) = oDiv.ChildNodes(n + 1).innerHTML
                                Case Is = "Contact Person:"
                                    Data(1) = oDiv.ChildNodes(n + 1).innerHTML
                                Case Is = "Telephone:"
                                    Data(2) = oDiv.ChildNodes(n + 1).innerHTML
                                Case Is = "E-Mail Address:"
                                    Data(3) = oDiv.ChildNodes(n + 1).innerHTML
                                Case Is = "Fax Number:"
                                    Data(4) = oDiv.ChildNodes(n + 1).innerHTML
                            End Select
                        End If
                    Next n
                End If
            Next oDiv
            
        Dest.Value = Data()
        
End Sub

Sub CopyFileData()

    Dim File        As Variant
    Dim Folderpath  As Variant
    Dim oFiles      As Object
    Dim oFolder     As Object
    Dim oShell      As Object
    Dim Rng         As Range
    Dim Wks         As Worksheet
    
        Folderpath = ThisWorkbook.Path
        
        Set Wks = Worksheets("Sheet1")
        
        Set Rng = Wks.Range("A2:E2")
        
            Wks.UsedRange.Offset(1, 0).ClearContents

            Set oShell = CreateObject("Shell.Application")
            Set oFolder = oShell.Namespace(Folderpath)
            
            If oFolder Is Nothing Then
                MsgBox "The Folder " & Folderpath & " Was Not found." & vbCrLf _
                        & "Please Check that the Path is Correct.", vbOKO + vbExclamation
                Exit Sub
            End If
            
                Set oFiles = oFolder.Items
            
                oFiles.Filter 64, "*.txt"
    
                For Each File In oFiles
                    Call ParseHTML(File.Path, Rng)
                    Set Rng = Rng.Offset(1, 0)
                Next File
            
End Sub

Hello platesigns,

The attached workbook will open all text files in the same folder that the workbook is saved in. This will occur when the workbook opens or you manually click the button on "Sheet1".

You can change the folder location in the macro CopyFileData in Module1. If your worksheet name is not "Sheet1" then you will need to change this match your sheet's name.

How this works is each text file is read and converted into an HTML file. The elements of the HTML file are scanned and the data is collected. The collected data is the output to the next empty row of the worksheet.

Here are the macros...

ThisWorkbook Module

Private Sub Workbook_Open()
    Call Module1.CopyFileData
End Sub

Moudle1 Macros

Sub ParseHTML(ByVal FileToOpen As String, ByRef Dest As Range)

    Dim Data(4)     As String
    Dim Filename    As String
    Dim HTMLdoc     As Object
    Dim n           As Long
    Dim oDiv        As Object
    Dim PageSrc     As String
    Dim URL         As String
        
        On Error GoTo ErrHandler
        
        URL = "file:///" & FileToOpen
        URL = Replace(URL, "\", "/")
        
          ' Open the text file and copy the HTML text in a string variable.
            With CreateObject("MSXML2.XMLHTTP")
                .Open "GET", URL, False
                .Send
                PageSrc = .ResponseText
            End With
            
          ' Convert the HTML page source text into an HTML file.
            Set HTMLdoc = CreateObject("HTMLfile")
            HTMLdoc.Open
            HTMLdoc.Write PageSrc
            
          ' Examine the HTML elements in the HTML file.
            For Each oDiv In HTMLdoc.getElementsByTagName("div")
                If oDiv.classname = "row" Then
                    For n = 0 To oDiv.ChildNodes.Length - 1
                        If oDiv.ChildNodes(n).NodeType = 1 Then
                            Select Case oDiv.ChildNodes(n).innerHTML
                                Case Is = "Contractor Name:"
                                    Data(0) = oDiv.ChildNodes(n + 1).innerHTML
                                Case Is = "Contact Person:"
                                    Data(1) = oDiv.ChildNodes(n + 1).innerHTML
                                Case Is = "Telephone:"
                                    Data(2) = oDiv.ChildNodes(n + 1).innerHTML
                                Case Is = "E-Mail Address:"
                                    Data(3) = oDiv.ChildNodes(n + 1).innerHTML
                                Case Is = "Fax Number:"
                                    Data(4) = oDiv.ChildNodes(n + 1).innerHTML
                            End Select
                        End If
                    Next n
                End If
            Next oDiv
            
      ' Copy the parsed information to the worksheet.
        Dest.Value = Data()
        
ErrHandler:
        If Err <> 0 Then
            MsgBox "Run-time error '" & Err.Number & "':" & vbCrLf & vbCrLf & Err.Description
        End If
        
End Sub

Sub CopyFileData()

    Dim File        As Variant
    Dim Folderpath  As Variant
    Dim oFiles      As Object
    Dim oFolder     As Object
    Dim oShell      As Object
    Dim Rng         As Range
    Dim Wks         As Worksheet
    
      ' Change this to the folder with your files.
        Folderpath = ThisWorkbook.Path
        
      ' Change the worksheet name if it is not Sheet1.
        Set Wks = Worksheets("Sheet1")
        
        Set Rng = Wks.Range("A2:E2")
        
          ' Clear any previous files' data.
            Wks.UsedRange.Offset(1, 0).ClearContents

            Set oShell = CreateObject("Shell.Application")
            Set oFolder = oShell.Namespace(Folderpath)
            
            If oFolder Is Nothing Then
                MsgBox "The Folder " & Folderpath & " Was Not found." & vbCrLf _
                        & "Please Check that the Path is Correct.", vbOKO + vbExclamation
                Exit Sub
            End If
            
              ' Return all the folder and files in the selected folder.
                Set oFiles = oFolder.Items
            
              ' Leave only a list of Text Files in this folder.
                oFiles.Filter 64, "*.txt"
                
                If oFiles.Count = 0 Then
                    MsgBox "No Text Files Were found in Folder:" & vbCrLf & Folderpath, vbOKOnly + vbExclamation
                    Exit Sub
                End If
    
              ' Use CTRL+Break to interrupt the loop.
                For Each File In oFiles
                    DoEvents
                    Call ParseHTML(File.Path, Rng)
                    Set Rng = Rng.Offset(1, 0)
                Next File
            
End Sub