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

 

[Andrew Poulsom]

When saving as text, Excel surrounds text that contains a comma with double quotes, because the comma is a delimiter (csv). To avoid that we will need to use a different method which I will look at shortly.

What names do you want to give to the saved files?

 

[Himaja]

For names:
Names should start with "Testcase" +<1>+"_" +<Current Date and Timestamp>
The subsequent files can have "Testcase" +<2> +"_" +<Current Date and Timestamp>
.....................
Eg: (Date and Timestamp display can be varied)
Testcase1_oct152013_02:56:23
Testcase2_oct152013_02:56:23
Testcase3_oct152013_02:56:23

 

[Himaja]

One thing to make it simple:
My text file will never have " any where.
So once we are done with changes, before saving the text file, we can replace " in the entire text file with no space. So that we can use the same above code.

But I dont know how to replace " with nospace in Text file using code.

 

[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
    Dim FileCount As Long
    Dim FileName As String
    Application.ScreenUpdating = False
    Set wbInput = Workbooks.Open(TextBox2.Text)
    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
        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 wbOutput
            With .Worksheets(1).Cells(r, 1)
                .Value = WorksheetFunction.Replace(.Value, Pos, Len(Txt), Txt)
            End With
'           Save to desktop
            FileCount = FileCount + 1
            FileName = "Testcase" & FileCount & "_" & Format(Date, "mmmddyyyy") & "_" & Format(Time, "hhmmss") & ".txt"
            .SaveAs FileName:=CreateObject("Wscript.Shell").specialfolders("Desktop") & Application.PathSeparator & FileName, FileFormat:=xlTextPrinter
            .Close SaveChanges:=False
        End With
    Next Cell
    wbInput.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

 

[Himaja]

It is great. But the problem is,the file is getting appended with some numbers as shown below:


-- removed inline image ---

 

[Himaja]

-- removed inline image ---

 

[Andrew Poulsom]

You can't post an image like that. Copy the data and paste it into a reply.

 

[Himaja]

HDRAutomatic Data Processing, Inc. 201303012013022120130228
POL444444444HI MA35693601 2012102720130715ABC CONSULTING LLC 56263 TENDONS LN MARSHALL VA20115 5405555555email123@email12.com N3A
CLS444444478HI MA12345678 2012071520130715 cbvcvcb, vcvcvcvc 000000003 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000040019000000000000000020130201201302152013030120130225004 777000012126 000
CLS444444478HI MA12345678 2012071520130715 CROFT, SOPHIA 000000004 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000075721600000000000000020130201201302152013030120130225004 777000012126 000
CLS444444478HI MA12345678 2012071520130715 gffgfgg, Louisa 000000005 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000080847400000000000000020130201201302152013030120130225004 777000012126 000
CLS444444478HI MA12345678 2012071520130715 qewbvbbv, Elizabeth 000000007 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000063072400000000000000020130201201302152013030120130225004 777000012126 000
CLS444444478HI MA12345678 2012071520130715 vbvvb, Georgiana 000000012 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000046209800000000000000020130201201302152013030120130225004 777000012126 000
CLS444444478HI MA12345678 2012071520130715 bvghgh, kljjhjjhj 000000013 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000074057600000000000000020130201201302152013030120130225004 777000012126 000
CLS444444478HI MA12345678 2012071520130715 popiooioiu, fgh 000000014 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000057367500000000000000020130201201302152013030120130225004 777000012126 000
CLS444444478HI MA12345678 2012071520130715 Hurst, werfghnn 000000018 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000049166700000000000000020130201201302152013030120130225004 777000012126 000
TRLAutomatic Data Processing, Inc. 2013030101:03:45 000000009


15
15
22
22
15
15
15
22

 

[Himaja]

If you dont mind, can I have the text files to be generated in a folder within desktop rather than in desktop with name as: Output

 

[Himaja]

I came to know from where it is getting added.

My input file is as below:

HDRAutomatic Data Processing, Inc. 201303012013022120130228
POL444444444WC WC35693601 2012071520130715ABC CONSULTING LLC 56263 TENDONS LN MARSHALL VA20115 5405555555email123@email12.com N3A
CLS444444444WC WC35693601 2012071520130715 BINGLEY, CAROLINE 000000003 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000040019000000000000000020130201201302152013030120130225004 777000012126 00015
CLS444444444WC WC35693601 2012071520130715 CROFT, SOPHIA 000000004 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000075721600000000000000020130201201302152013030120130225004 777000012126 00015
CLS444444444WC WC35693601 2012071520130715 Hayters, Louisa 000000005 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000080847400000000000000020130201201302152013030120130225004 777000012126 00022
CLS444444444WC WC35693601 2012071520130715 Grantley, Elizabeth 000000007 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000063072400000000000000020130201201302152013030120130225004 777000012126 00022
CLS444444444WC WC35693601 2012071520130715 Darcy, Georgiana 000000012 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000046209800000000000000020130201201302152013030120130225004 777000012126 00015
CLS444444444WC WC35693601 2012071520130715 Palmer, Charlotte 000000013 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000074057600000000000000020130201201302152013030120130225004 777000012126 00015
CLS444444444WC WC35693601 2012071520130715 Harrington, Pen 000000014 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000057367500000000000000020130201201302152013030120130225004 777000012126 00015
CLS444444444WC WC35693601 2012071520130715 Hurst, Caroline 000000018 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000049166700000000000000020130201201302152013030120130225004 777000012126 00022
TRLAutomatic Data Processing, Inc. 2013030101:03:45 000000009


Except first and last line, all the remaining lines have spaces at the last.
and the 15,15, etc are coming from each line and pasted at the end.
My Out file is as below:

HDRAutomatic Data Processing, Inc. 201303012013022120130228
POL444444444WC WC35693601 2012102720130715ABC CONSULTING LLC 56263 TENDONS LN MARSHALL VA20115 5405555555email123@email12.com N3A
CLS444444444WC WC35693601 2012071520130715 BINGLEY, CAROLINE 000000003 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000040019000000000000000020130201201302152013030120130225004 777000012126 000
CLS444444444WC WC35693601 2012071520130715 CROFT, SOPHIA 000000004 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000075721600000000000000020130201201302152013030120130225004 777000012126 000
CLS444444444WC WC35693601 2012071520130715 Hayters, Louisa 000000005 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000080847400000000000000020130201201302152013030120130225004 777000012126 000
CLS444444444WC WC35693601 2012071520130715 Grantley, Elizabeth 000000007 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000063072400000000000000020130201201302152013030120130225004 777000012126 000
CLS444444444WC WC35693601 2012071520130715 Darcy, Georgiana 000000012 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000046209800000000000000020130201201302152013030120130225004 777000012126 000
CLS444444444WC WC35693601 2012071520130715 Palmer, Charlotte 000000013 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000074057600000000000000020130201201302152013030120130225004 777000012126 000
CLS444444444WC WC35693601 2012071520130715 Harrington, Pen 000000014 VA881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000057367500000000000000020130201201302152013030120130225004 777000012126 000
CLS444444444WC WC35693601 2012071520130715 Hurst, Caroline 000000018 MD881002ACLERICAL OFFICE EMPLOYEES N O C N0 00000000049166700000000000000020130201201302152013030120130225004 777000012126 000
TRLAutomatic Data Processing, Inc. 2013030101:03:45 000000009


15
15
22
22
15
15
15
22