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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-20-2017, 06:16 AM   #1
SlimSquirrel
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

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.

SlimSquirrel is offline   Reply With Quote
Old 07-20-2017, 08:01 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,129
Thanks: 79
Thanked 437 Times in 397 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
Reply

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 04:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
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