Every once in a 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 Replace 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 its 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 Constant 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
        ApXL.DisplayAlerts = True
    Set xlWBk = Nothing
    Set ApXL = Nothing
Exit Function
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Function
End Function
Tips (Main)
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
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
Looping Records to Send eMail
Check if Table Exists
Insert (or Remove) Blank Line
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
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.