Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-23-2017, 09:02 AM   #1
schniggeldorf
Newly Registered User
 
Join Date: Jan 2013
Posts: 22
Thanks: 7
Thanked 0 Times in 0 Posts
schniggeldorf is on a distinguished road
Sub to export excel range to new access table

Hi:

I am trying to write a Sub in Excel that would copy a range of cells from a Excel worksheet, and export them as a new table in an Access Database. My code, in Excel VBA is as follows:
Code:
Public Sub ExportWsToAccessTable()
Dim FullOutputPath As String
Dim objAccess As Access.Application
Dim Range As String
Dim DestTable As String
Dim HasFieldNames As Boolean

FullOutputPath = "S:\NEURO\PUBLIC\IMATCH\Database\IMATCH outcomes database.accdb"
Set objAccess = New Access.Application
DestTable = "tblKpImportPre"
HasFieldNames = True
Range = "a1:eu9"
Call objAccess.OpenCurrentDatabase(FullOutputPath)

objAccess.Visible = True

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, DestTable, FullOutputPath, HasFieldNames, Range
    
objAccess.CloseCurrentDatabase
Set objAccess = Nothing

End Sub
The code compiles OK, but when I try to run it I get error 3073 - "Operation must use an updatable query". I've searched the web, but haven't found anything that quite fits my situation.

Can anybody help me figure this out?

Thanks.


Last edited by schniggeldorf; 06-23-2017 at 09:04 AM. Reason: Correcting a typo
schniggeldorf is offline   Reply With Quote
Old 06-23-2017, 09:09 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,896
Thanks: 92
Thanked 1,694 Times in 1,572 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Sub to export excel range to new access table

You are trying to export from Excel to Access so the Transfer Spreadsheet line should be acExport

Also try type Excel12xml

Alternately try importing the Excel file when in Access
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-28-2017, 03:34 AM   #3
ashleedawg
"Here for a good time"
 
ashleedawg's Avatar
 
Join Date: Jun 2017
Location: Canada
Posts: 153
Thanks: 23
Thanked 38 Times in 34 Posts
ashleedawg is on a distinguished road
Re: Sub to export excel range to new access table

Range is a reserved word; you'll need to pick a different variable name.

Beyond that, there could be a number of things making the recordset "not updateable"; it's tough to say what the problem is without seeing the destination table.

Is there a reason you can't import into Access instead of exporting from Excel?

__________________
_______________________________________
Back in the Fire
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ashleedawg is offline   Reply With Quote
Old 06-29-2017, 03:14 PM   #4
schniggeldorf
Newly Registered User
 
Join Date: Jan 2013
Posts: 22
Thanks: 7
Thanked 0 Times in 0 Posts
schniggeldorf is on a distinguished road
Re: Sub to export excel range to new access table

Thanks to all who offered suggestions. Unfortunately, none of them worked. I eventually concluded that the 'not updateable" problem related to records I was pulling from Access into Excel in earlier code. Because of this, Access apparently saw Excel as a user, and locked its tables.

I found another way to get the data imported into excel from access, and this solved the problem.

However, I remain stuck. My current code is as follows:

Code:
Public Sub ExportWsToAccessTable()
Dim AdoCon As ADODB.connection
Dim AccessFilePath As String
Dim AccessFileName As String
Dim strRange As String
Dim Range As Range
Dim DestTable As String
Dim HasFieldNames As Boolean
Dim WorksheetName As String
Dim password As String
Dim Connection1 As String
Dim Connection2 As String
Dim strSQL As String

Set AdoCon = New ADODB.connection

AccessFilePath = "S:\NEURO\PUBLIC\IMATCH\Database\"
AccessFileName = "IMATCH outcomes database.accdb"
password = "xxxxx"
DestTable = "tblKpImportPre"
WorksheetName = "Pre"
HasFieldNames = True
strRange = "a1:eu9"
Set myRange = ActiveWorkbook.Worksheets(WorksheetName).Range(strRange)
myRange.Select
Connection1 = "Data Source = " & AccessFilePath & AccessFileName & ";"
Connection2 = Connection1 & "JET OLEDB:Database Password= " & password & ";"
strSQL = "INSERT INTO " & DestTable & " " _
    & "SELECT * FROM " & ActiveWorkbook.FullName & "].[" & WorksheetName & "$]"

With AdoCon
    .Provider = "Microsoft.ACE.oledb.12.0"
    .Properties("Extended Properties") = "Excel 15.0 Xml;HDR=YES;IMEX=2;ACCDB=YES"
    If IsNull(password) Then
        .Properties("Data Source") = Connection1
    Else
        .Properties("Data Source") = Connection2
    End If
    .Open
End With

AdoCon.Execute strSQL
When I attempt to run this, I get an "authentication failed" error on the .Open line indicated in red. I've been beating my head against the wall for several days, but I can't figure out what's wrong with my syntax. The password I'm feeding to Access works fine if I open the database manually.

schniggeldorf is offline   Reply With Quote
Reply

Tags
access , data export , excel

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Table to Excel Increases Range in Formulas xyba General 1 03-25-2016 02:53 AM
Naming a range to export data to an access table. LOUISBUHAGIAR54 Modules & VBA 5 12-16-2014 12:39 PM
Transfer Excel range to Access table HReiter Modules & VBA 7 02-09-2012 08:04 PM
Export Access Table to Excel- with the date as part of the new Excel file name WinDancer Macros 10 01-22-2008 01:10 PM
Export Access Query to an existing excel file specifying range aga2957 Queries 0 12-12-2006 11:00 PM




All times are GMT -8. The time now is 04:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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