Results 1 to 3 of 3
  1. #1
    Lifeseeker1019 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    6

    Access to open Excel and delete records, VBA

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You can't. Open excel,delete the record there.
    you can't delete in excel either if access has the linked table open.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Disable the error handler then step debug code.
    Access VBA can open Excel and delete rows. Review https://www.educba.com/vba-delete-row/
    I got this simple test to work.
    Code:
    Sub DelExcelRec()
    
    Dim xlApp As Excel.Application
    Dim wb As Excel.Workbook
    
    Dim lastRow As Integer
    Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set wb = xlApp.Workbooks.Open("C:\Users\June\Condos.xlsx", False, False) lastRow = wb.Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row wb.Worksheets("Sheet1").Rows(lastRow).Delete End Sub



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Delete query to delete records that meet criteria
    By neill_long in forum Queries
    Replies: 1
    Last Post: 06-11-2018, 02:41 PM
  2. Delete does not delete records in evey table
    By LaughingBull in forum Access
    Replies: 5
    Last Post: 09-01-2015, 04:05 PM
  3. Delete Query based on records in a linked Excel table
    By gaker10 in forum Database Design
    Replies: 11
    Last Post: 09-30-2014, 10:06 AM
  4. Delete Columns in Excel using Access VBA
    By gasmaskman in forum Programming
    Replies: 1
    Last Post: 02-04-2014, 09:43 AM
  5. Replies: 2
    Last Post: 01-24-2012, 02:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums