Hello,
I have some code written to call MS Excel from Access and delete some records in Excel based on a filter, but it doesn't really delete anything. Also, if you do application.echo true, you can't tell if it will open MS Excel. It's strange...
Code:
On Error GoTo ErrorHandler
Dim myExcel2 As Object
Dim myWb2 As Object
Dim myWs2 As Object
Dim rng2 As Range
Dim oCurrentCell As Range
Dim lastRow As Integer
Set myExcel2 = CreateObject("Excel.Application")
'Delete existing 2 KPIs from the template file
Set myWb2 = myExcel2.workbooks.Open(ImportExportTemp)
Set myWs2 = myWb2.worksheets("Data")
lastRow = myWs2.Cells(Rows.Count, 3).End(xlUp).Row
Set rng2 = ActiveSheet.Range(Cells(3, 2), Cells(3, lastRow))
Application.Echo False
For Each oCurrentCell In rng2
If oCurrentCell.Value = "cc118" Or oCurrentCell.Value = "cc131" Then
oCurrentCell.EntireRow.Delete
End If
Next oCurrentCell
Application.Echo True
myWb2.Save
myWb2.Close
Exit Sub
ErrorHandler:
MsgBox "The following error occurred: " & Err.Description
Thank you for any assistance
Exit SubOn Error GoTo ErrorHandler
Dim myExcel2 As Object
Dim myWb2 As Object
Dim myWs2 As Object
Dim rng2 As Range
Dim oCurrentCell As Range
Dim lastRow As Integer
Set myExcel2 = CreateObject("Excel.Application")
'Delete existing 2 KPIs from the template file
Set myWb2 = myExcel2.workbooks.Open(ImportExportTemp)
Set myWs2 = myWb2.worksheets("Data")
lastRow = myWs2.Cells(Rows.Count, 3).End(xlUp).Row
Set rng2 = ActiveSheet.Range(Cells(3, 2), Cells(3, lastRow))
Application.Echo False
For Each oCurrentCell In rng2
If oCurrentCell.Value = "Pb118" Or oCurrentCell.Value = "Pb131" Then
oCurrentCell.EntireRow.Delete
End If
Next oCurrentCell
Application.Echo True
myWb2.Save
myWb2.Close
Exit Sub
ErrorHandler:
MsgBox "The following error occurred: " & Err.Description
Exit Sub