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]

How to protect my workbook user form code, not to view by others
I have a user form and when I share it to other users, users should be just able to run the user form and input accordingly but they should not be able to view the code.
How can i achieve it?

 

[Andrew Poulsom]

In the Visual Basic Editor select your workbook in the Project Window and choose Tools|VBAProject Properties. On the Protection tab check Lock project for viewing, enter a Password and click OK. You need to save, close and reopen the workbook for the protection to take effect.

 

[Himaja]

In the Visual Basic Editor select your workbook in the Project Window and choose Tools|VBAProject Properties. On the Protection tab check Lock project for viewing, enter a Password and click OK. You need to save, close and reopen the workbook for the protection to take effect.

When I click on Run, it is opening Macros run.
But there is no Macro name displayed. I 'm not able to run

 

[Andrew Poulsom]

Don't you have a macro in a general module to show your UserForm, eg?

Sub ShowForm()
    UserForm1.Show
End Sub

 

[Himaja]

Don't you have a macro in a general module to show your UserForm, eg?

Sub ShowForm()
    UserForm1.Show
End Sub

No I don't have. Now I have pasted your code in the general module. Now its working.....
Thanks a lot....

 

[Himaja]

No I don't have. Now I have pasted your code in the general module. Now its working.....
Thanks a lot....

How to implement the below scenario for generating multiple files:

I have the below excel:
LineNo ColNo StepNo Value
2 36 1 20131212
3 42 2 EMP
10 30 3 10
2 40 1 2012
3 50 1 635
5 55 2 fdi

Based on the stepNo, files should be created.
It is like, for thefirst 3 rows, one file should be genertaed incorporating the 3 changes.
For the 4th row, other file should be generated
for the 5th,6th rows, one file should be generated.

Within multiple files generation itself, if i wanted to have more number of changes in one file (based on StepNo),
how to implement this scenario?

 

[Andrew Poulsom]

It would be more straightforward if your data was like this:

LineNo</SPAN>	ColNo</SPAN>	Value</SPAN>	StepNo</SPAN>
2</SPAN>	36</SPAN>	20131212</SPAN>	1</SPAN>
3</SPAN>	42</SPAN>	EMP</SPAN>	2</SPAN>
10</SPAN>	30</SPAN>	10</SPAN>	3</SPAN>
2</SPAN>	40</SPAN>	2012</SPAN>	1</SPAN>
3</SPAN>	50</SPAN>	635</SPAN>	1</SPAN>
5</SPAN>	55</SPAN>	fdi</SPAN>	2</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

Then it would only be necessary to test for a fourth column. Is that OK?

 

[Himaja]

Ya no problem.

 

[Himaja]

It can be in last or first column. Could you pls help in this Andew ?

 

[Andrew Poulsom]

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
                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)
                End If
                Print #FileOut, Data
            Loop
            Close #FileIn
            Close #FileOut
        Next Area
    End If
    wbInput.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub