Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-20-2017, 06:10 AM   #1
Robster
Newly Registered User
 
Join Date: Mar 2014
Location: London
Posts: 60
Thanks: 13
Thanked 0 Times in 0 Posts
Robster is on a distinguished road
Updating Excel from Access

Hi,
I want to know if you can update an Excel file from Access when it is a linked table?
I try to do it an update query but get error Cannot update '(expression)'; field not updateable.

Is it not possible or do I need to change some settings?

Robster is offline   Reply With Quote
Old 11-20-2017, 06:14 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,595
Thanks: 59
Thanked 2,435 Times in 2,335 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Updating Excel from Access

no posible senor.
you can update through automation.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 11-20-2017, 06:18 AM   #3
Robster
Newly Registered User
 
Join Date: Mar 2014
Location: London
Posts: 60
Thanks: 13
Thanked 0 Times in 0 Posts
Robster is on a distinguished road
Re: Updating Excel from Access

thanks.
How would i do that?

Robster is offline   Reply With Quote
Old 11-20-2017, 06:24 AM   #4
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,164
Thanks: 495
Thanked 904 Times in 856 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Updating Excel from Access

I found these two knowledge base articles by Microsoft very helpful when I was the first manipulating spreadsheets from MS Access.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 11-20-2017 at 06:56 AM.
Uncle Gizmo is offline   Reply With Quote
Old 11-20-2017, 06:35 AM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,595
Thanks: 59
Thanked 2,435 Times in 2,335 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Updating Excel from Access

1. add Reference (In VBE->Tools->Reference)
in Microsoft Excel XX.X Object Library

2. instantiate excel objects:

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSh As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("excel path+name here")
Set xlSh = xlWB.Sheets(1)

' do the updating
With xlSh
.Cells(1,1).Value="new value"
.Range("A2").Value ="new value"
...
...

'close all Object and save the excel file
Set xlSh = Nothing
xlWB.Close True
Set xlWB=Nothing
xlApp.Quit
Set xlA[[=Nothing

__________________
"Never stop learning, because life never stops teaching"
arnelgp 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
Excel graph in Access not updating leok31 Forms 2 05-07-2014 10:19 PM
Updating Access dba from Excel china99boy Excel 1 06-27-2012 02:44 AM
updating access db from excel sunshine076 Modules & VBA 3 07-14-2010 04:32 AM
Updating Access Tables from Excel SteveJones General 2 03-26-2008 10:54 PM
[SOLVED] Updating Excel From Access faldo General 4 12-08-2003 06:06 AM




All times are GMT -8. The time now is 12:56 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