Excel Macro to apend data from two notepad into a single notepad

Question

I want to delete the last line contain '*' of two notepad and apend the reamining data into a new notepad by excel macro.

Please guys help me out. I can't find any suggestion.

As a suggestion, you can use the FileSystemObject (google "Excel FileSystemObject") to read the text files and output to a new file. There are plenty of examples online for how to do something similar, wouldn't take much searching. — tigeravatar, Sep 19 '13 at 15:08
here is a walkthrough [how to read *.txt files from VBA](http://vba4all.wordpress.com/category/vba-macros/reading-txt-files-from-vba/) — , Sep 19 '13 at 15:30

score 0 · Answer 1 · answered Sep 19 '13 at 18:32

Using @mehow's suggestion, here is some code that you can use:

' To get this to run, you'll need to reference Microsoft Scripting Runtime:
    ' Per http://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba
    ' Within Excel you need to set a reference to the VB script run-time library. The relevant file is usually located at \Windows\System32\scrrun.dll
    ' To reference this file, load the Visual Basic Editor (ALT-F11)
    ' Select Tools - References from the drop-down menu
    ' A listbox of available references will be displayed
    ' Tick the check-box next to 'Microsoft Scripting Runtime'
    ' The full name and path of the scrrun.dll file will be displayed below the listbox
    ' Click on the OK button

Sub appFiles()
    'File path and names for each file
    Dim sFile1 As String
    Dim sFile2 As String
    Dim sFileLast As String
    'Search string
    Dim sSearchStr As String
    'Delimiter used to separate/join lines
    Dim sDL As String
    'If the final file already exists, should it overwrite the previous _
        contents (True) or append to the end of the file (False)
    Dim doOverwrite As Boolean
    'File contents
    Dim sMsg1 As String
    Dim sMsg2 As String
    Dim sMsgFinal As String

    sFile1 = "C:\Users\foobar\Desktop\foo.txt"
    sFile2 = "C:\Users\foobar\Desktop\foo2.txt"
    sFileLast = "C:\Users\foobar\Desktop\fooFinal.txt"
    sSearchStr = "*"
    sDL = Chr(13) & Chr(10)
    doOverwrite = True

    sMsg1 = appendLines(sFile1, sSearchStr, sDL)
    sMsg2 = appendLines(sFile2, sSearchStr, sDL)
    sMsgFinal = sMsg1 & sDL & sMsg2

    Call writeToFile(sMsgFinal, sFileLast, doOverwrite)
End Sub

Function appendLines(sFileName As String, sSearchStr As String, Optional sDL As String = " ") As String
    Dim oFSO As FileSystemObject
    Set oFSO = New FileSystemObject

    Dim oFS As TextStream
    Dim sStr As String
    Dim sMsg As String
    If oFSO.fileexists(sFileName) Then  'Check if file exists
        On Error GoTo Err

        Set oFS = oFSO.openTextFile(sFileName)
        'Read file
        Do While Not oFS.AtEndOfStream
            sStr = oFS.ReadLine
            If InStr(sStr, sSearchStr) Then
                appendLines = sMsg
            Else
                sMsg = sMsg & sStr & sDL
            End If
        Loop
        oFS.Close
    Else
        Call MsgBox("The file path (" & sFileName & ") is invalid", vbCritical)
    End If

    Set oFS = Nothing
    Set oFSO = Nothing
    Exit Function

Err:
    Call MsgBox("Error occurred while reading the file.", vbCritical)

    oFS.Close
    Set oFS = Nothing
    Set oFSO = Nothing
End Function

Sub writeToFile(sMsg As String, sFileName As String, Optional doOverwrite As Boolean = False)
    Dim oFSO As FileSystemObject
    Set oFSO = New FileSystemObject

    Dim oFS As TextStream

    On Error GoTo Err
    If oFSO.fileexists(sFileName) Then
        If doOverwrite Then
            Set oFS = oFSO.openTextFile(sFileName, ForWriting)
        Else
            Set oFS = oFSO.openTextFile(sFileName, ForAppending)
        End If
    Else
        Set oFS = oFSO.CreateTextFile(sFileName, True)
    End If

    Call oFS.write(sMsg)

    oFS.Close
    Set oFS = Nothing
    Set oFSO = Nothing
    Exit Sub

Err:
    Call MsgBox("Error occurred while writing to the file.", vbCritical)

    oFS.Close
    Set oFS = Nothing
    Set oFSO = Nothing
End Sub

You'll need to customize the appFiles routine as needed, by providing file names to sFile1, sFile2, and sFileLast; your desired search string to sSearchStr (you mentioned using "*"); a delimiter to separate lines (it's currently written to use a carriage return and new line); and a parameter deciding whether or not to overwrite the final file (if you find yourself running this multiple times with the same final file).

Here's another link that I used while writing the code above: link - Explains how to write to a file from within a macro

Hope this helps.

While the above does work, you may want to do this in VBScript instead as a stand-alone file, rather than have it tied up in Excel. But I guess it really depends on why you want it tied to Excel, which you haven't yet expanded upon in your OP. — chewmewaba4, Sep 19 '13 at 18:38

Excel Macro to apend data from two notepad into a single notepad

1 Answers1