Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

LinkBack Thread Tools Display Modes
Old 12-25-2018, 04:02 AM
LG1972 LG1972 is offline Windows 7 64bit Office 2010
Join Date: Dec 2018
Posts: 1
LG1972 is on a distinguished road
Default Find and replace header text across multiple files

I'm looking for a macro that will allow me to find and replace header text across multiple files. I've tried editing macros I found for doing just that in word but with no luck. Can someone help me edit this macro so it will work with excel documents?

This is the word macro I have used:

Sub ReplaceTextInHeaderInMultiDoc()
  Dim StrFolder As String
  Dim strFile As String
  Dim objDoc As Document
  Dim dlgFile As FileDialog
  Dim strFindText As String
  Dim strReplaceText As String
  Set dlgFile = Application.FileDialog(msoFileDialogFolderPicker)
  With dlgFile
    If .Show = -1 Then
      StrFolder = .SelectedItems(1) & "\"
      MsgBox "Please select the target folder."
      Exit Sub
    End If
  End With
  strFindText = InputBox("Enter text to be found:", "Find Text")
  strReplaceText = InputBox("Enter new text:", "Replace Text")
  strFile = Dir(StrFolder & "*.docx", vbNormal)
  While strFile <> ""
    Set objDoc = Documents.Open(FileName:=StrFolder & strFile)
    For nPageNum = 1 To Selection.Information(wdNumberOfPagesInDocument)
      Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:=nPageNum
      Application.Browser.Target = wdBrowsePage
      With objDoc.ActiveWindow
        .ActivePane.View.SeekView = wdSeekCurrentPageHeader
        With .Selection.Find
          .Text = strFindText
          .Replacement.Text = strReplaceText
          .Wrap = wdFindContinue
          .Execute Replace:=wdReplaceAll
        End With
      End With
    Next nPageNum
    objDoc.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
    strFile = Dir()
End Sub
Reply With Quote
Old 12-25-2018, 04:27 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,381
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light

Hi and welcome
please wrap code with code tags.
To do this edit your post - select the code and click the #button.
Thank you
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find & Replace in Header/Footer in 1000 files amodiammmuneerk@glenmarkp Word 12 03-05-2018 03:31 AM
Replace or apply new header in multiple files Carchee Word VBA 28 05-30-2017 08:44 AM
Find & Replace text in Field Code across multiple documents RPM7 Word VBA 6 05-12-2017 12:58 AM
Need to find/replace text in many word files - but text is in embedded word files semple.13 Word VBA 5 11-03-2015 01:20 PM
Created VBA to Find and Replace in Body, Header and Footer with Highlighting the replacement text QA_Compliance_Advisor Word VBA 11 09-23-2014 04:40 AM

All times are GMT -7. The time now is 07:54 PM.

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc. is not affiliated with Microsoft