• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Updating Excel from Access (1 Viewer)

Robster

Registered User.
Local time
Today, 01:46
Joined
Mar 13, 2014
Messages
60
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?
 

arnelgp

error reading drive A:
Local time
Today, 16:46
Joined
May 7, 2009
Messages
10,542
no posible senor.
you can update through automation.
 

Robster

Registered User.
Local time
Today, 01:46
Joined
Mar 13, 2014
Messages
60
thanks.
How would i do that?
 

arnelgp

error reading drive A:
Local time
Today, 16:46
Joined
May 7, 2009
Messages
10,542
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
 

Users who are viewing this thread

Top Bottom