How to Excel VBA Multiple Rows from Excel to After Last Line of Access Table?

shawn 0
Viewed 26

I would like to copy some rows in Excel from Row 2 all the way to the last non empty row, and then paste them to the bottom of an Access Table.

The following code works for me to open the Table I want, and then copying the rows in Excel that I want to paste........ but i got no idea how to paste it in. The solutions I've seen so far requires adding data to each field,but i just want to paste the entire row in because i've got 56 columns per row

Dim AccessBookFilePath As String
Dim appAccess As Access.Application

AccessBookFilePath = Range("N2").Value

'Create new Access object

Set appAccess = New Access.Application

'Open the Access Book

'Call appAccess.OpenCurrentDatabase("P:'Contracts\" & AccessBookFilePath)


appAccess.DoCmd.OpenTable "Retail Contracts Monitoring"

appAccess.UserControl = True


Windows("Dataset").Activate
Sheets("Data To Export").Select

Dim RowNumberStart As String

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

Rows("2:" & NextRow).Select
Selection.Copy
row copy-paste ms-access vba excel
1 Answers

Use Access object DoCmd.TransferSpreadsheet method to transfer data. Build a string of range limits to use in Range argument. Assuming first row of range is header.

Dim strRange As String
strRange = "A1:BD" & Range("A" & Rows.Count).End(xlUp).Row
appAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
                "Retail Contracts Monitoring", AccessBookFilePath, True, strRange
June70
Related