Lookup Open in VBA help (look under Excel, not VBA if it gives you options)

Here are the options for the UpdateLinks:=

0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references


Try changing UpdateLinks:=0 to UpdateLinks:=3

HTH

Andi


"Brad" wrote in message
oups.com...
Hi

I'm having a problem when I try to open a spreadsheet from Access 2003.

The Excel Spreadsheet contains some links to external data and also a
column that contains a user defined function.

If I use the following code in Excel VBA the spreadsheet opens without
a problem and I can see values in the column with the user defined
function

Workbooks.Open(fileName:="File Location and Name", UpdateLinks:=0,
ReadOnly:=True)

however if I use the exact same code in Access VBA ie.

Set objWorkbook = objExcel.Workbooks.Open(fileName:="File Location and
Name", UpdateLinks:=0, ReadOnly:=True)

it opens the spreadsheet and my column with the user defined function
contains #NAME? for every cell.

I've tried a load of different ways to open it from Access but the same
problem happens everytime.

What does Excel do differently that allows the spreadsheet to open as
Read-Only with the correct values displayed in that column?