Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-25-2018, 04:02 AM
LG1972 LG1972 is offline Windows 7 64bit Office 2010
Novice
 
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:





Code:
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) & "\"
    Else
      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
      objDoc.Bookmarks("\page").Range.Select
      With objDoc.ActiveWindow
        .ActivePane.View.SeekView = wdSeekCurrentPageHeader
        With .Selection.Find
          .ClearFormatting
          .Text = strFindText
          .Replacement.ClearFormatting
          .Replacement.Text = strReplaceText
          .Wrap = wdFindContinue
          .Execute Replace:=wdReplaceAll
        End With
      End With
    Next nPageNum
    objDoc.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
    objDoc.Save
    objDoc.Close
    strFile = Dir()
  Wend
End Sub
Reply With Quote
  #2  
Old 12-25-2018, 04:27 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
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
Default

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
Reply

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.
MSOfficeForums.com is not affiliated with Microsoft