CODEWITHSUNDEEP
excelvbams-access
Devin
Devin

Reputation: 323

VBA program to delete rows in excel and move deleted rows to access database

I am trying to create a VBA program in Excel to delete certain rows and send those deleted rows to an access database. Here is what I want the workflow to be:

  1. An individual downloads a CSV file called print.xlsx that opens in Excel, and contains something similar to what the table below looks like(obviously the content here is random)
  2. That individual also has a primary workbook(that again looks similar to this), that is always open on their desktop, lets call it mike.xlsm
  3. That individual clicks a button in mike.xlsm that executes a macro that should do the following:
    1. Deletes any rows in mike.xlsm that are not contained in print.xlsx(based on an identifying number (in the flag column). No number will ever duplicate, so that is not an issue.
    2. Gets any rows from print.xlsx that were not in mike.xlsm, and adds them to mike.xlsm, so essentially, the same rows exists for mike.xlsm as in print.xlsx. The reason for doing this is that print.xlsx does not contain all the columns that mike.xlsm does. However, this shouldn't interfere with the code, as the columns come after the columns that exists in both workbooks. The additional columns in the mike workbook are meant for user input.
    3. Sends those deleted rows to an Access database, however attaches an additional column to those rows that contains the workbook's name (mike), for easy querying with numerous individuals in the future

The code I am currently using to delete rows is:

Sub Delete_DNE()
    Dim lRow As Long
    Dim iCntr As Long
    lRow = 4000
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, 5).Value = "DNE" Then
            Rows(iCntr).Delete
        End If
    Next
    End Sub

My question is: Does anyone have good code that will execute the above workflow, or suggestions for part of the code, to ultimately get to the full workflow?


I am just using vlookup manually to determine what needs to be labled "DNE" meaning it does not exist in print.xlsx, and then manually copying the rows out of print.xlsx that weren't in mike, into mike. Obviously, this is not an automated or efficient way to do this if we want several employees to be doing this, and a large access database of entries. Thank you!



The following code was in a question flagged as a possible duplicate, and is helpful, but does not address the problem, mostly because it transfers the entire worksheet to access and not just specific rows; I only want the deleted rows sent to access:

Sub AccImport()
  Dim acc As New Access.Application
  acc.OpenCurrentDatabase "C:\Users\Public\Database1.accdb"
  acc.DoCmd.TransferSpreadsheet _
          TransferType:=acImport, _
          SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
          TableName:="tblExcelImport", _
          Filename:=Application.ActiveWorkbook.FullName, _
          HasFieldNames:=True, _
          Range:="Folio_Data_original$A1:B10"
  acc.CloseCurrentDatabase
  acc.Quit
  Set acc = Nothing
End Sub

Excel sheet

Upvotes: 0

Views: 1638

Answers (2)

ASH
ASH

Reputation: 20302

Ok, I would do it like this. This is very generic...needs to be modified by you to suit your specific needs...

Anything that says 'Sheet2' in ColumnI, starting in Row2 (you may want to start in Row1, if you don't have headers), gets cut/pasted from Sheet1 to Sheet2

Sub MoveSheet2()
    Dim i As Variant
    Dim endrow As Integer
    Dim Sheet1 As Worksheet, Sheet2 As Worksheet

    Set Sheet1 = ActiveWorkbook.Sheets("Sheet1")
    Set Sheet2 = ActiveWorkbook.Sheets("Sheet2")

    endrow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row

    For i = 2 To endrow
        If Sheet1.Cells(i, "I").Value = "Sheet2" Then
           Sheet1.Cells(i, "I").EntireRow.Cut Destination:=Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp).Offset(1)
        End If
    Next
End Sub

Now, move everything from Sheet2 to your Access DB.

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\FolderName\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable  
    ' all records in a table
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0 
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Upvotes: 2

Ronnie Smith
Ronnie Smith

Reputation: 18555

I haven't coded with VBA in years but you certainly can do what you describe. The process would go something like:

  1. create empty array to hold deleted rows (arrays).
  2. iterate your rows and upon before the delete, push that data into the tempArray.
  3. upon iteration of all rows complete, write the tempArray to your database.

Upvotes: 1

Related Questions