Updating Excel from Access (1 Viewer)

Robster

Registered User.
Local time
Today, 03:18
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:18
Joined
May 7, 2009
Messages
19,169
no posible senor.
you can update through automation.
 

Robster

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:18
Joined
May 7, 2009
Messages
19,169
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