Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Macro to replace SSN in all files within a folder
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 08-28-2014, 07:28 AM
caj1980 caj1980 is offline Macro to replace SSN in all files within a folder Windows 7 64bit Macro to replace SSN in all files within a folder Office 2010 64bit
Novice
Macro to replace SSN in all files within a folder 		 
Join Date: Aug 2014
Posts: 6
caj1980 is on a distinguished road
Default Macro to replace SSN in all files within a folder
I am looking for help creating a macro that will replace all social security numbers in each document in a folder. Search string would be "???-??-" to be replaced with "XXX-XX-" so that we keep the last 4 of the SSN. I need to be able to run the macro against a folder of multiple documents with each document containing multiple instances of the SSN to be replaced. I have found some solutions on-line in various places but this is all new to me. I am not a programmer! We currently spend about 2 hours a day manually replacing all of the SSN's in each document.

THANK YOU SO MUCH!!!
Reply With Quote
  #2  
Old 08-28-2014, 09:10 PM
macropod's Avatar
macropod macropod is offline Macro to replace SSN in all files within a folder Windows 7 64bit Macro to replace SSN in all files within a folder Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
It would be better to do this when the documents are generated, not afterwards. If they are being generated via mailmerge, for example, field coding can be used to output XXX-XX- plus only the last four digits.

That said, the following macro will process all documents in the selected folder:
Code:
Sub BulkReplaceSSNs()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, wdDoc As Document
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc.Range.Find
    .ClearFormatting
    .Text = "([0-9]{3})-([0-9]{2})-([0-9]{4})"
    .Replacement.Text = "XXX-XX-\3"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchWildcards = True
    .Execute Replace:=wdReplaceAll
  End With
  wdDoc.Close SaveChanges:=True
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
The macro includes its own browser, so all you need do is select the folder to process, and let it run. Just be sure to not save the document you're running the macro from to the same folder.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-11-2014, 07:01 AM
caj1980 caj1980 is offline Macro to replace SSN in all files within a folder Windows 7 64bit Macro to replace SSN in all files within a folder Office 2010 64bit
Novice
Macro to replace SSN in all files within a folder 		 
Join Date: Aug 2014
Posts: 6
caj1980 is on a distinguished road
Smile Best answer!!!
This code worked beautifully! Thank you so much!
Reply With Quote
  #4  
Old 09-11-2014, 07:17 AM
caj1980 caj1980 is offline Macro to replace SSN in all files within a folder Windows 7 64bit Macro to replace SSN in all files within a folder Office 2010 64bit
Novice
Macro to replace SSN in all files within a folder 		 
Join Date: Aug 2014
Posts: 6
caj1980 is on a distinguished road
Default Help to Run Macro on Doc Open
I have a word doc with a macro that will search all files in a folder and strip the SSN's from the files (Thanks, macropod!). Now, to make this operation as simple as possible for my customer, I would like to set it up so the macro (VB) is saved to a specific document not globally (I have this part done). What I need help with is configuring that macro to run automatically when the file is opened. End goal is for the customer to open the word doc containing the macro and it auto runs instead of them having to navigate through Word to View/Macros/select and run the macro. I would also like for the word doc to close when the macro is finished running. It will be much easier to distribute the doc with the embedded macros than to instruct each customer how to import the macro.

The script is avail here if needed: https://www.msofficeforums.com/word-...in-folder.html
Reply With Quote
  #5  
Old 09-11-2014, 07:36 AM
caj1980 caj1980 is offline Macro to replace SSN in all files within a folder Windows 7 64bit Macro to replace SSN in all files within a folder Office 2010 64bit
Novice
Macro to replace SSN in all files within a folder 		 
Join Date: Aug 2014
Posts: 6
caj1980 is on a distinguished road
Default
I figured it out... just renamed the macro to "AutoOpen ()". Now to figure out how to auto close the document after it runs...
Reply With Quote
  #6  
Old 09-11-2014, 07:46 AM
caj1980 caj1980 is offline Macro to replace SSN in all files within a folder Windows 7 64bit Macro to replace SSN in all files within a folder Office 2010 64bit
Novice
Macro to replace SSN in all files within a folder 		 
Join Date: Aug 2014
Posts: 6
caj1980 is on a distinguished road
Default Need code to close word after AutoOpen macro executes
I am using this code set up to auto run when the doc is opened. I would like to add a few lines of code to the end of the macro to close the word doc when the macro finishes running. Thanks for the help!
Reply With Quote
  #7  
Old 09-11-2014, 04:12 PM
macropod's Avatar
macropod macropod is offline Macro to replace SSN in all files within a folder Windows 7 64bit Macro to replace SSN in all files within a folder Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
After:
Application.ScreenUpdating = True
insert:
ThisDocument.Close SaveChanges:=True

Note: using SaveChanges:=True ensures any edits you've made to the document and/or its code will be saved.

PS: I've merged your threads into one, since they all concern the one project.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 09-11-2014, 04:17 PM
caj1980 caj1980 is offline Macro to replace SSN in all files within a folder Windows 7 64bit Macro to replace SSN in all files within a folder Office 2010 64bit
Novice
Macro to replace SSN in all files within a folder 		 
Join Date: Aug 2014
Posts: 6
caj1980 is on a distinguished road
Default
Thanks again macropod!!
Reply With Quote
Reply

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to replace SSN in all files within a folder macro to change name of files in a folder in order expert4knowledge Word VBA 5 07-10-2014 03:54 PM
Remove title property from all files in a folder konopca Word VBA 2 12-04-2012 10:54 AM
Macro to replace SSN in all files within a folder Move files from one folder to another obasanla Word 1 09-28-2012 04:42 PM
Macro to replace SSN in all files within a folder Word Macro - change date in footer for all files in a folder patidallas22 Word VBA 2 03-09-2012 08:14 AM
Adding a field to a folder of PDF files. Balliol Windows 0 11-22-2009 02:02 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:08 PM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft