Code for Excel Macros- Editing the Notepad

[Himaja]

Can any one help in providing the code for the below scenario:

I will browse for an excel file which has columns like: Line Number, Length, Value
Using Excel Macros, I have to open an existing text file, make the changes in it with the values in the "value" column of Excel from Line Number and Column no. specified in the excel.

Save the modified text file in a particular path

 

[Himaja]

Problem: When I try to edit in same line at different positions, I'm unable to do it. LineNo ColNo Value Steps
2 36 20131212 1
11 64 20130302 2
11 89 33 3 In the above eg, in For line 11, both the editings are not happened. Only first replacement is done and the 2nd replacement (33) is not done. Could you pls look into this and help me Andrew?

 

[Andrew Poulsom]

Why didn't you say that in Post #56? You will need to use the technique in Post #49. Try to do that yourself.

 

[Himaja]

As I'm unable to do that I have asked you Andrew. Could you help me out in this?

 

[Andrew Poulsom]

The purpose of this forum is to resolve specific questions not to provide an entire project. If you are unable to amend the code I have already provided using the techniques it contains, you should consider employing a consultant. Please read Tip #4 here:

http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

 

[Himaja]

Ok Thank you....

 

[Andrew Poulsom]

As you have told me that your requirement in Post #61 is the final one try:

Private Sub GoBtn_Click()
    Dim Folder As String
    Dim wbInput As Workbook
    Dim Rng As Range
    Dim Cell As Range
    Dim Area As Range
    Dim r As Variant
    Dim Pos As Long
    Dim Txt As String
    Dim FileCount As Long
    Dim LineCount As Long
    Dim FileIn As Long
    Dim FileOut As Long
    Dim FileName As String
    Dim Data As String
    Application.ScreenUpdating = False
    Folder = CreateObject("Wscript.Shell").specialfolders("Desktop") & Application.PathSeparator & "Output"
    If Dir(Folder, vbDirectory) = "" Then
        MkDir Folder
    End If
    Set wbInput = Workbooks.Open(TextBox2.Text)
    With wbInput.Worksheets(1).Range("A1").CurrentRegion
        Set Rng = .Offset(1).Resize(.Rows.Count - 1)
    End With
    If Rng.Columns.Count = 3 Then
        For Each Cell In Rng.Columns(1).Cells
            r = Cell.Value
            Pos = Cell.Offset(, 1).Value
            Txt = Cell.Offset(, 2).Value
            FileCount = FileCount + 1
            LineCount = 0
            FileIn = FreeFile
            Open TextBox1.Text For Input As #FileIn
            FileOut = FreeFile
            FileName = Folder & Application.PathSeparator & "Testcase" & FileCount & "_" & Format(Date, "mmmddyyyy") & "_" & Format(Time, "hhmmss") & ".txt"
            Open FileName For Output As #FileOut
            Do While Not EOF(FileIn)
                LineCount = LineCount + 1
                Line Input #FileIn, Data
                If LineCount = r Then
                    Data = WorksheetFunction.Replace(Data, Pos, Len(Txt), Txt)
                End If
                Print #FileOut, Data
            Loop
            Close #FileIn
            Close #FileOut
        Next Cell
    Else
        With wbInput.Worksheets(1)
            For r = .Range("A1").CurrentRegion.Rows.Count To 2 Step -1
                If .Cells(r + 1, 4).Value = 1 Then
                    .Cells(r + 1, 1).EntireRow.Insert
                End If
            Next r
            Set Rng = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
        End With
        For Each Area In Rng.Areas
            LineCount = 0
            FileCount = FileCount + 1
            FileIn = FreeFile
            Open TextBox1.Text For Input As #FileIn
            FileOut = FreeFile
            FileName = Folder & Application.PathSeparator & "Testcase" & FileCount & "_" & Format(Date, "mmmddyyyy") & "_" & Format(Time, "hhmmss") & ".txt"
            Open FileName For Output As #FileOut
            Do While Not EOF(FileIn)
                LineCount = LineCount + 1
                Line Input #FileIn, Data
                Do
                    r = Application.Match(LineCount, Area, False)
                    If Not IsError(r) Then
                        Pos = Area.Cells(r, 2).Value
                        Txt = Area.Cells(r, 3).Value
                        Data = WorksheetFunction.Replace(Data, Pos, Len(Txt), Txt)
                        Area.Cells(r, 1).Resize(, 4).ClearContents
                    Else
                        Exit Do
                    End If
                Loop
                Print #FileOut, Data
            Loop
            Close #FileIn
            Close #FileOut
        Next Area
    End If
    wbInput.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub