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: 6,760
Thanks: 55
Thanked 2,148 Times in 2,059 Posts
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: 9,477
Thanks: 359
Thanked 776 Times in 741 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: 6,760
Thanks: 55
Thanked 2,148 Times in 2,059 Posts
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
Updating Excel From Access faldo General 4 12-08-2003 06:06 AM




All times are GMT -8. The time now is 06:39 PM.


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