fxlFindReplace()...

Every once in a while while exporting Access data to Excel I need to *clean* it up after it reaches Excel, so, I wrote a little Function to do just that.

The Find and Replaxe in Excel has these parameters...

.Replace What:="'", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True

However, in my Function I left off SearchOrder, SearchFormat and ReplaceFormat as I didn't need them.  Leaving those off causes Excel to use it's built-in default and unless you need those parameters excluding them does not harm.  If you decide you want to use them then you will need to replace the Excel Constant with the number indicated below or declare them as a Constants within your Module...
The difficult I do immediately, the impossible takes a little bit longer.
Function fxlFindReplace(strFileName, strSheetName, strRange, strFind, strReplace) As String
On Error GoTo xlTrap
'2018 Find and Replace in Excel from Access
'To run: ?fxlFindReplace("C:\Hold\2.xls", "Sheet1", "A1:C8", 2, 1)
'From https://www.access-diva.com/
 
    Dim ApXL As Object
    Dim xlWBk As Object
 
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strFileName)
 
        ApXL.DisplayAlerts = False
        xlWBk.Sheets(strSheetName).Range(strRange).Replace What:=strFind, Replacement:=strReplace, LookAt:=1, MatchCase:=True
        xlWBk.Save
        ApXL.DisplayAlerts = True
 
    Set xlWBk = Nothing
    ApXL.Application.Quit
    Set ApXL = Nothing
 
Exit Function
xlTrap:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    ApXL.Application.Quit
    Exit Function
 
End Function
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
Parameter Constant
LookAt xlPart = 2
xlWhole = 1
SearchOrder xlByRows = 1
  xlByColumns = 2
Public Const xlPart = 2
Public Const xlWhole = 1
Public Const xlByRows = 1
Public Const xlByColumns = 2
OR
Copy and paste the below into your modUtilities or a new Module (Remember don't name the Module the same as the Function name.)  Usage details at top of Module.
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.