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
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,732
Thanks: 50
Thanked 1,578 Times in 1,504 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
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: 8,962
Thanks: 295
Thanked 692 Times in 660 Posts
Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about
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.
__________________
Tony Hine - My Aspiration --- Finding a better way of Explaining ---
From this YouTube:-

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

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
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,732
Thanks: 50
Thanked 1,578 Times in 1,504 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
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 11:22 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 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World