Excel Range to Access Table (1 Viewer)

SlimSquirrel

New member
Local time
Today, 15:40
Joined
Aug 4, 2015
Messages
6
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.

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.
 

Users who are viewing this thread

Top Bottom