0

I am trying to replace characters such as "/" and "ó", and also a line break (alt + ENTER, manually replaced in excel with find and replace with CTRL + J) in excel files (.xlsx). The characters can be found all over the excel sheets (not one particular range). The reason that I would like to replace these values is that these characters are giving me errors in another application. I have 20 excel files in one folder, with multiple sheets. I would like to make a script (vba macro) to loop through the excel files and all its sheets and do the replacements. I am very new with vba/macros.

    Sub ReplaceStringInFile()

    Dim sBuf As String
    Dim sTemp As String
    Dim sFileName As String
    Dim FileExt(2) As String


    ChDir = "C:\mydirectory\"

    FileExt(1) = "xlsx"

    For i = 1 To 1

      sFileName = Dir("*." & FileExt(i))

      Do

        If sFileName = "" Then Exit Do

        sTemp = ""
        Open sFileName For Input As #1
          Do Until EOF(1)
            Line Input #1, sBuf
            sTemp = sTemp & sBuf & vbCrLf
          Loop
        Close #1

        sTemp = Replace(sTemp, "ó", "o")
        sTemp = Replace(sTemp, "/", "")

        Open sFileName For Output As #1
           Print #1, sTemp
        Close #1

        sFileName = Dir()

  Loop

Next i

End Sub

I have tried to combine code from scripts mentioned here: Find and replace string in all excel files in folder and here Excel macro to find and replace multiple strings in any text file Loop through files in a folder using VBA?

But I didn't get it to work.

Help would be very appreciated!

Lies
  • 1
  • 1
  • Can you edit your post with the code you currently have? – roses56 Aug 10 '20 at 12:56
  • Yes sorry, I am struggling to get the code copied in nicely with all the indents etc. Sorry – Lies Aug 10 '20 at 13:02
  • I recommend you break your problem into smaller pieces. Step 1) Can you read all Excel file names in a directory? 2) Can you open up an Excel file? 3) Do you know how to loop through all sheets 4) For a single sheet, can you find/replace? Try try tackle one step at a time. BTW, your code assumes text files. If you are opening up Excel files, you are on a completely wrong path using "Line Input" – Ernie Thomason Aug 10 '20 at 13:07
  • Hi Ernie, thank you. I can only do step 4. Ah.. I do not have .text files. What would I need for excel files? – Lies Aug 10 '20 at 13:30

0 Answers0