Excel Range to Access Table

[SlimSquirrel]

Hi,

I have some sample code that I have been working on and cant seem to get right.

I simply want to copy a range of Cells from an open excel document into a table in Access and thought the simplest way would be to paste (special) i.e. paste append. This works perfectly when done manually.

Now I know this is not an Excel forum but I was hoping that since it is VBA and the destination is Access that you might be able to help.

Here is the sample of my code.

Sub Button2_Click()
 
' Send sheet QuoteDetails range to Access database
 
    Sheets("QuoteDetails").Select
    Range("a2:cm2").Select
    Selection.Copy
    strDBName = "Quote List Version 2.accdb"
    strMyPath = "Z:\Quotes\1 Quote List"
    strDB = strMyPath & "\" & strDBName
    Set appAccess = CreateObject("Access.Application")
' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB
    appAccess.Visible = True
    appAccess.DoCmd.OpenTable "QuoteFullT"
    appAccess.DoCmd.RunCommand acCmdRecordsGoToNew
    appAccess.DoCmd.RunCommand acCmdPasteAppend
    
    appAccess.Close acTable, "QuoteFullT"
    appAccess.CloseCurrentDatabase
    appAccess.Quit acQuitSaveAll
 
End Sub

The error i get is as follows: Run-time error '2501':
The RunCommand action was canceled.

I hope there is some way to resolve this.

 

[Frothingslosh]

The command you want is DoCmd.Transferspreadsheet.

The linked page is Microsoft's documentation on it, along with an example at the bottom.