Trying to perform a find and replace in excel

[primagic]

I am trying to perform a find and replace on text within excel.

My complete code that opens the worksheet, deletes rows and so on works find and also select all the cells but the replace doesnt work.

Code is:

Function ImportAuditMaster()

    'Hide MS Access popup warnings
    DoCmd.SetWarnings (False)

    'Get the file location for the first extract - Actions
    Dim strFileLocation As String
    strFileLocation = DLookup("FileLocation", "tblFileLocationLookup", "VBALookup = 3")
    
    'Open Microsoft Excel
    Dim xlApp As Object
    Dim ExcelRunning As Boolean
    
    'Check if Excel is running. If it is use that instance if not create a new instance.
    ExcelRunning = IsExcelRunning()
    If ExcelRunning Then
    Set xlApp = GetObject(, "Excel.Application")
    Else
    Set xlApp = CreateObject("Excel.Application")
    End If
    
    'Hide Excel and do the work in the background
    xlApp.Visible = True
    
    'Open the selected file so we can delete the appropriate rows before importing the data
    Set wb = xlApp.Workbooks.Open(strFileLocation, True, False)
    
    'Delete rows 1 - 4
    wb.sheets(1).Rows("1:4").Delete
    
    'Delete the blank row beneath the column headers after the first 4 rows have been deleted.
    wb.sheets(1).Rows(2).Delete
    
    'Delete column A
    wb.sheets(1).Columns(1).Delete
    
    'Select all cells
    wb.sheets(1).cells.select
    
    'Replace text
    Selection.Replace What:="00.00.0000", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

   
    'Turn off alerts that will get displayed when Excel tries to save the file
    xlApp.DisplayAlerts = False
    
    
    'Save the excel file
    wb.Save
    
    'Close the file
    wb.Close
    
    'Turn back on alerts
    xlApp.DisplayAlerts = True
    
    'Quit Excel
    If Not ExcelRunning Then xlApp.Quit
    Set xlApp = Nothing
    
   
    'Delete the existing data within the Actions table
    DoCmd.OpenQuery "qryDelete_AdditionalPayments_Data", acViewNormal
    
    'Import the csv file
    DoCmd.TransferText acImportDelim, "specAuditMaster", "AuditMaster", strFileLocation, True
    
    
    'Show MS Access popup warnings
    DoCmd.SetWarnings (True)
    
     

End Function

Getting a runtime error 424 - Object required on the following bit of code:

Selection.Replace What:="00.00.0000", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

How can I solve this?

 

[Andrzejek]

Do you have Option Explicit?

You do declare [tt]xlApp[/tt], but you hardly refear to this object.
You do use [tt]wb[/tt] in many places, but it is not declared anywhere.

Have fun.

---- Andy

 

[PHV]

[!]xlApp.[/!]Selection.Replace ...

Furthermore, be sure that xlPart and xlByRows are known.
Tip: use Option Explicit

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886