Replace text from excel into a text file

[jrisebo]

I need to write to a text file certain numbers that are populated from excel. The reason this is difficult, is because the text file location is dynamic, but the file path will be defined in excel in a named cell called 'textfilepath'. The name of the file will be defined in excel by a cell named 'textfilename' . Lastly the data that needs to be replaced is never in the same line, but always is the line after the word 'PREP' The text that is replaced is always justified to the right at the 5th space. The value that replaces the number there is defined as 'increment';

So, I am looking for code that will open the file named 'textfilename' in the 'textfilepath', find the line after the word 'PREP', then look up the value in the 'increment' cell, and replace the number there, but keep the justification at 5.

This is a difficult task in my mind, but perhaps someone can figure it out. I am a novice when doing programing, as i use it just to automate my job.

 

[John_w]

See if this gets near - try it on a copy of the text file.

Public Sub Replace_Text()

    Dim textFile As String
    Dim fileNum As Integer
    Dim fileData As String
    Dim p1 As Long, p2 As Long

    textFile = Range("textfilepath").Value & Range("textfilename").Value
   
    If Dir(textFile) <> vbNullString Then
        fileNum = FreeFile
        Open textFile For Binary As #fileNum
        fileData = Space(LOF(fileNum))
        Get #fileNum, , fileData
        Close #fileNum              
        p1 = InStr(fileData, "PREP")
        p1 = InStr(p1, fileData, vbCrLf) + 1
        p2 = InStr(p1, fileData, vbCrLf) + 1
        fileData = Left(fileData, p1) & "     " & Range("increment").Value & Mid(fileData, p2)
        fileNum = FreeFile
        Open textFile For Output As #fileNum
        Print #fileNum, fileData
        Close #fileNum
    Else
        MsgBox "File not found: " & textFile
    End If
       
End Sub

 

[jrisebo]

See if this gets near - try it on a copy of the text file.

Public Sub Replace_Text()

    Dim textFile As String
    Dim fileNum As Integer
    Dim fileData As String
    Dim p1 As Long, p2 As Long

    textFile = Range("textfilepath").Value & Range("textfilename").Value
  
    If Dir(textFile) <> vbNullString Then
        fileNum = FreeFile
        Open textFile For Binary As #fileNum
        fileData = Space(LOF(fileNum))
        Get #fileNum, , fileData
        Close #fileNum             
        p1 = InStr(fileData, "PREP")
        p1 = InStr(p1, fileData, vbCrLf) + 1
        p2 = InStr(p1, fileData, vbCrLf) + 1
        fileData = Left(fileData, p1) & "     " & Range("increment").Value & Mid(fileData, p2)
        fileNum = FreeFile
        Open textFile For Output As #fileNum
        Print #fileNum, fileData
        Close #fileNum
    Else
        MsgBox "File not found: " & textFile
    End If
      
End Sub

Thanks, I will give it a try today hopefully.

 

[jrisebo]

Thanks, I will give it a try today hopefully.

See if this gets near - try it on a copy of the text file.

Public Sub Replace_Text()

    Dim textFile As String
    Dim fileNum As Integer
    Dim fileData As String
    Dim p1 As Long, p2 As Long

    textFile = Range("textfilepath").Value & Range("textfilename").Value
  
    If Dir(textFile) <> vbNullString Then
        fileNum = FreeFile
        Open textFile For Binary As #fileNum
        fileData = Space(LOF(fileNum))
        Get #fileNum, , fileData
        Close #fileNum             
        p1 = InStr(fileData, "PREP")
        p1 = InStr(p1, fileData, vbCrLf) + 1
        p2 = InStr(p1, fileData, vbCrLf) + 1
        fileData = Left(fileData, p1) & "     " & Range("increment").Value & Mid(fileData, p2)
        fileNum = FreeFile
        Open textFile For Output As #fileNum
        Print #fileNum, fileData
        Close #fileNum
    Else
        MsgBox "File not found: " & textFile
    End If
      
End Sub

Ok, awesome so far so good. One thing I forgot to mention is there is other text already on that line, so the text in the first 5 spaces can only be replaced, currently its deleting the whole line, and then adding the value. Also, the value could be double digits, so the last digit of the value has to be right justified on the 5th spot.

Here is an example of the line we are working with

    7    4    0    1    0 3248 3142  210

So we are replacing the 7, and keeping the rest of the other text (its not static, they could change numbers too, but the justification is important.

Thanks

 

[John_w]

Try this - it just needed p2 adjusting to be the index of the character after the 7 in your example, i.e. the 6th character of that line instead of the end of that line, and the replacement value right-justified within a 'field' of 5 spaces.

Public Sub Replace_Text()

    Dim textFile As String
    Dim fileNum As Integer
    Dim fileData As String
    Dim p1 As Long, p2 As Long

    textFile = Range("textfilepath").Value & Range("textfilename").Value
    
    If Dir(textFile) <> vbNullString Then
        fileNum = FreeFile
        Open textFile For Binary As #fileNum
        fileData = Space(LOF(fileNum))
        Get #fileNum, , fileData
        Close #fileNum                
        p1 = InStr(fileData, "PREP")
        p1 = InStr(p1, fileData, vbCrLf) + 1
        p2 = p1 + 6
        fileData = Left(fileData, p1) & Right("     " & Range("increment").Value, 5) & Mid(fileData, p2)                
        fileNum = FreeFile
        Open textFile For Output As #fileNum
        Print #fileNum, fileData
        Close #fileNum
    Else
        MsgBox "File not found: " & textFile
    End If
        
End Sub

 

[jrisebo]

Try this - it just needed p2 adjusting to be the index of the character after the 7 in your example, i.e. the 6th character of that line instead of the end of that line, and the replacement value right-justified within a 'field' of 5 spaces.

Public Sub Replace_Text()

    Dim textFile As String
    Dim fileNum As Integer
    Dim fileData As String
    Dim p1 As Long, p2 As Long

    textFile = Range("textfilepath").Value & Range("textfilename").Value
   
    If Dir(textFile) <> vbNullString Then
        fileNum = FreeFile
        Open textFile For Binary As #fileNum
        fileData = Space(LOF(fileNum))
        Get #fileNum, , fileData
        Close #fileNum               
        p1 = InStr(fileData, "PREP")
        p1 = InStr(p1, fileData, vbCrLf) + 1
        p2 = p1 + 6
        fileData = Left(fileData, p1) & Right("     " & Range("increment").Value, 5) & Mid(fileData, p2)               
        fileNum = FreeFile
        Open textFile For Output As #fileNum
        Print #fileNum, fileData
        Close #fileNum
    Else
        MsgBox "File not found: " & textFile
    End If
       
End Sub

perfect, thanks for your help on this.