vizgraphics
New Member
- Joined
- Jun 8, 2016
- Messages
- 6
Hello,
I am wondering if anyone would be able to convert this script for Excel Mac 2011. It allows you to select a .txt file, and then search for values within Column A and replace from Column B within the txt file. This version works for PC versions but not Mac
I am wondering if anyone would be able to convert this script for Excel Mac 2011. It allows you to select a .txt file, and then search for values within Column A and replace from Column B within the txt file. This version works for PC versions but not Mac
Code:
Sub Replace_Text()
Dim strFile As String
Dim i As Integer
Dim strText As String
Dim cell As Range
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = ThisWorkbook.Path
If .Show <> -1 Then Exit Sub
strFile = .SelectedItems(1)
End With
i = FreeFile
strText = Space(FileLen(strFile))
With CreateObject("vbscript.regexp")
.Global = True
Open strFile For Binary Access Read Write As #i
Get #i, , strText
For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
.Pattern = Replace(Replace(Replace(Replace(cell.Value, "?", "\?"), "*", "\*"), "+", "\+"), ".", "\.")
strText = .Replace(strText, cell.Offset(, 1).Value)
Next cell
Put #i, 1, strText
Close #i
End With
End Sub