Hello Basically, I need to export data from MS Excel to MS Access as the code below. However, I need to delete all filled rows before pasting to get rid of all old data each time the macro is run. How can I amend the code below? Thank you very much. Sub EXPORT_DEBT() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use by inputting destinations 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=M:\db test.mdb;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "DebtTable", 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("ISIN") = Range("A" & r).Value .Fields("Last Accessed Date") = Range("B" & r).Value .Fields("Asset Type") = Range("C" & r).Value .Fields("BBG Input ISIN") = Range("D" & r).Value .Fields("Issuer") = Range("E" & r).Value .Fields("Rating") = Range("F" & r).Value .Fields("Issue Date") = Range("G" & r).Value .Fields("Maturity Date") = Range("H" & r).Value .Fields("Years to Maturity") = Range("I" & r).Value .Fields("Modified Duration") = Range("J" & r).Value .Fields("Face Value ('000)") = Range("K" & r).Value .Fields("Coupon Rate") = Range("L" & r).Value .Fields("Market Yield") = Range("M" & r).Value .Fields("Last-Close Date") = Range("N" & 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
|