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: 2,993
Thanks: 76
Thanked 418 Times in 378 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.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
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 12:52 PM.


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 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World