Excel update

[Mikk]

I have a form which is based upon a query which is based
upon a table that is linked to an Excel spreadsheet that
is linked to various other spreadsheets. Yes it is a
pain, but it is something I inherited at my position. Is
it possible to create an expression upon opening the
Access form that pulls up the Excel file and saves the
newly linked file ALL WHILE hiding this procedure from
the Access user?
Any helpful hints on the code behind this?

 

[Tim Ferguson]

Is
it possible to create an expression upon opening the
Access form that pulls up the Excel file and saves the
newly linked file ALL WHILE hiding this procedure from
the Access user?

Doesn't the form do this anyway. Linking to a "table" in an Excel sheet
does not make the Excel application visible; and changes will be sent back
to the sheet, subject to the usual rules.

What exactly is not happening that you think should happen?

Tim F

 

[Mikk]

Tim,
The problem that I have is that the Excel file that
Access is linking has links of its own that it needs to
refresh. So with this in mind, it is necessary to pull
up this file to establish new data links so that Access
will thus have this new data.
I have tried the following code but can't figure out how
to save the spreadsheet and then close. Am I on the
right track here?

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
objXL.Workbooks.Open ("file")
Set objXL = Nothing

 

[Tim Ferguson]

The problem that I have is that the Excel file that
Access is linking has links of its own that it needs to
refresh.

My immediate reaction is just that the architecture here needs to be re-
thought. Relying on long chains of OLE links is just asking for corruption,
in my book! Would it not be possible to get all the data posted into the
Jet database (which is, after all, designed for sharing) and then getting
the Excel and stuff to read the data from there?

Failing that, skip the excel stage, and get Access to read the stuff direct
from wherever the Excel is getting it?

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

You need to catch a reference to the actual workbook:

Set wbk = objXL.Workbooks.Open ("file")

I don't know the object model well enough off top of my head, but I'd be
looking for something along the lines of

wbk.OLELinks.Update xlUpdateAutomatic

or, more probably

For Each obj In wbk.EmbeddedObjects
If TypeOf(obj) Is WhatYouAreLookingFor Then
obj.Update
End If
Next obj

and then

wbk.Close xlSaveChanges

but you'll have to look up the Excel VBA Help for details.

Hope that helps


Tim F