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?

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?

Thanks Andi

I didn't want any of the links/references to update though.

If I run the code above in Excel with UpdateLinks:=0 I still see values
in my column but if I run it in Access I lose the values....

Hi Brad,

Try objExcel.enableEvents=false before opening
Put to true again when finished
Regards

JY

"Brad" wrote in message
ups.com...
Thanks Andi

I didn't want any of the links/references to update though.

If I run the code above in Excel with UpdateLinks:=0 I still see values
in my column but if I run it in Access I lose the values....

No luck there either.

Another thing I've noticed is this:

In the excel macro I use the PasteSpecial function for pasting values
only. I copy the values in the column containing the function mentioned
above into another worksheet. This works fine when I run the excel
macro from Excel.

However If I run the Excel Macro from Access using this code:

objExcel.Workbooks.Open(FileName, False)
objExcel.Workbooks(FileName).RunAutoMacros xlAutoOpen

The values in the column do not get pasted correctly, instead it pastes
#NAME? into the new worksheet.

I can't for the life of me understand why.

Can you post all the code (excel and access)