Access Diva Find and Replace (Excel)

fxlFindReplace()...

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...

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

Public Const xlPart = 2
Public Const xlWhole = 1
Public Const xlByRows = 1
Public Const xlByColumns = 2

Parameter	Constant
LookAt	xlPart = 2
	xlWhole = 1
SearchOrder	xlByRows = 1
	xlByColumns = 2