Go Back   Access World Forums > Apps and Windows > Visual Basic

Thread Tools Rate Thread Display Modes
Old 07-20-2017, 06:16 AM   #1
Newly Registered User
Join Date: Aug 2015
Location: Wolverhampton, UK
Posts: 6
Thanks: 2
Thanked 0 Times in 0 Posts
SlimSquirrel is on a distinguished road
Excel Range to Access Table


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
    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.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.

SlimSquirrel is offline   Reply With Quote
Old 07-20-2017, 08:01 AM   #2
Premier Pale Stale Ale
Frothingslosh's Avatar
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,189
Thanks: 82
Thanked 450 Times in 408 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Excel Range to Access Table

The command you want is DoCmd.Transferspreadsheet.

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sub to export excel range to new access table schniggeldorf Excel 3 06-29-2017 03:14 PM
Export Table to Excel Increases Range in Formulas xyba General 1 03-25-2016 02:53 AM
Question URGENT HELP - Access Query to Excel in excel Range jay9324 General 6 02-13-2015 10:22 AM
Transfer Excel range to Access table HReiter Modules & VBA 7 02-09-2012 08:04 PM
Range in Excel from Access ctr1085 Modules & VBA 3 07-13-2006 08:18 AM

All times are GMT -8. The time now is 01:37 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World