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]

For Example:
In Text file, in Line 2, Position 36, if there is 20110101
In excel I gave the value like 20131212
Then the code has to to replace the existing 20110101 in text file and replace with 20131212 in the same excel.
My excel is like an instruction file which will say the value to be placed in that position
based on the Line and Position, it has to replace the existing value with the new value.
It is not like, what ever the value I give in Excel should be there in Text file. It is like replacing the existing value with the valued from the Excel

 

[Andrew Poulsom]

So how many characters should be replaced? The same number of characters as the replacement text? For line 3 that would result in:

CLS123456789WC WC12345-01 2012102620131026 Knope, Leslie 000000010 MD958601ABARBER SHOP N0

becoming:

CLS123456789WC WC12345-01 2012102620131026 Knope, LeHimajaMadineni MD958601ABARBER SHOP N0

 

[Himaja]

Yes correct.
The same number of characters as the replacement text. If the value I'm replacing is lesser, I will be appending spaces "Himaja "

 

[Andrew Poulsom]

Do you have your browsing code, to save me a bit of work?

 

[Himaja]

Ya I have it.

Public Sub BrowseExceBtn_Click()

Dim Path As String
Dim resultWorkbook As Workbook
Dim found As Boolean
Path = Application.GetOpenFilename(FileFilter:="Excel Filer (*.xls),*.xls", Title:="Open File(s)", MultiSelect:=False)
TextBox2.Text = Path

End Sub


Public Sub GoodFileBtn_Click()

Dim Path1 As String
Dim resultWorkbook As Workbook
Dim found As Boolean
Path1 = Application.GetOpenFilename(FileFilter:="Text Filer (*.txt),*.txt", Title:="Open File(s)", MultiSelect:=False)
TextBox1.Text = Path1

End Sub

 

[Andrew Poulsom]

Is that code on a UserForm?

 

[Himaja]

Yes..
When I click on Go button, everything which I mentioned previously should be done.

 

[Himaja]

I have written some code for Go Button. I havent implemented the requirement

Private Sub GoBtn_Click()
Dim XL As Excel.Application
Dim WBK As Excel.Workbook
Dim Ary(3) As String

    Set XL = CreateObject("Excel.Application")
    Set WBK = XL.Workbooks.Open(TextBox2.Text)
    For i = 2 To WBK.Sheets("Sheet1").UsedRange.Rows.Count
        For j = 2 To WBK.Sheets("Sheet1").UsedRange.Columns.Count
            
' MsgBox (WBK.Sheets("Sheet1").Cells(i, j).Value)
            ' Check that cell is not empty.
            If WBK.Sheets("Sheet1").Cells(i, j).Value = "" Then
                'Nothing in this cell.
                'Do nothing.
            Else
                            Ary(i - 2) = WBK.Sheets("Sheet1").Cells(i, j).Value
                            'Code should be implemented
                
            End If

 
                                        'Code should be implemented
        Next j
        Next i
                            'Code should be implemented
    WBK.Close
    Set XL = Nothing

End Sub

 

[Andrew Poulsom]

Try:

Private Sub GoBtn_Click()
    Dim wbInput As Workbook
    Dim wbOutput As Workbook
    Dim Rng As Range
    Dim Cell As Range
    Dim r As Long
    Dim Pos As Long
    Dim Txt As String
    Set wbInput = Workbooks.Open(TextBox2.Text)
    Workbooks.OpenText Filename:=TextBox1.Text, Origin:=xlMSDOS, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1)
    Set wbOutput = ActiveWorkbook
    With wbInput.Worksheets(1).Range("A1").CurrentRegion.Columns(1)
        Set Rng = .Offset(1).Resize(.Rows.Count - 1)
    End With
    For Each Cell In Rng.Cells
        r = Cell.Value
        Pos = Cell.Offset(, 1).Value
        Txt = Cell.Offset(, 2).Value
        With wbOutput.Worksheets(1).Cells(r, 1)
            .Value = WorksheetFunction.Replace(.Value, Pos, Len(Txt), Txt)
        End With
    Next Cell
'   Save to desktop
    With wbOutput
        .SaveAs Filename:=CreateObject("Wscript.Shell").specialfolders("Desktop") & Application.PathSeparator & .Name, FileFormat:=xlText
        .Close SaveChanges:=False
    End With
    wbInput.Close SaveChanges:=False
End Sub

 

[Himaja]

Try:

Private Sub GoBtn_Click()
    Dim wbInput As Workbook
    Dim wbOutput As Workbook
    Dim Rng As Range
    Dim Cell As Range
    Dim r As Long
    Dim Pos As Long
    Dim Txt As String
    Set wbInput = Workbooks.Open(TextBox2.Text)
    Workbooks.OpenText Filename:=TextBox1.Text, Origin:=xlMSDOS, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1)
    Set wbOutput = ActiveWorkbook
    With wbInput.Worksheets(1).Range("A1").CurrentRegion.Columns(1)
        Set Rng = .Offset(1).Resize(.Rows.Count - 1)
    End With
    For Each Cell In Rng.Cells
        r = Cell.Value
        Pos = Cell.Offset(, 1).Value
        Txt = Cell.Offset(, 2).Value
        With wbOutput.Worksheets(1).Cells(r, 1)
            .Value = WorksheetFunction.Replace(.Value, Pos, Len(Txt), Txt)
        End With
    Next Cell
'   Save to desktop
    With wbOutput
        .SaveAs Filename:=CreateObject("Wscript.Shell").specialfolders("Desktop") & Application.PathSeparator & .Name, FileFormat:=xlText
        .Close SaveChanges:=False 
    End With
    wbInput.Close SaveChanges:=False
End Sub

Reply:
When Given as True in the highlighted line, It is working.
There are two issues:
1. since you are copying it to Excel and then to Notepad, I'm getting "" in each line except the edited line.
Could you pls help in removing the Quotes in each line...........
2. All the modifications/editings are done in one text file and getting saved. But i need like,
for each row in excel, there should be a specific text file generated. If i have 10 rows, 10 different text files should be generated.

Thanks a lot in helping me.....