Opening an Excel Spreadsheet from Access with VBA

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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)

Andi

In relation to the PasteSpecial issue

I have this code in my Access Database


Public objExcel as Excel.Application

******
Set objExcel = New Excel.Application
objExcel.Visible = True

objExcel.Workbooks.Open ExcelMacroPath & ExcelMacName, False,
False
objExcel.Workbooks(ExcelMacName).RunAutoMacros xlAutoOpen

This initiates the Excel Macro containing this code

'Open the Working version of the file as an editable copy
Workbooks.Open ExcelMacroPath & ExcelMacName, False, False

'Ensure the correct sheet is activated
Worksheets("Final").Activate

'Select and Copy data
Range("A1").Select
intLastCell = Selection.End(xlDown).Row
Range("A1:S" & intLastCell).Select
Selection.Copy

'Insert a new sheet in which to paste data
Worksheets.Add before:=Sheets(1)
'Rename the sheet
Worksheets(1).Name = "New"
'Paste copied data into the sheet
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

and so on....


As mentioned the excel spreadsheet contains a column with a User
Defined Function
If I start this whole process from my Access code the end result is
that I lose the values from the column with the UDF.

If I start by opening the Excel spreadsheet and running the macro it
all works fine.

This seems to be exactly the same problem as I had in my first post.

I've only just discovered that the User Defined Function I've been
rabbiting on about is actually a function supplied by an add-in. It's
NETWORKDAYS()

Could this be something to do with the problem? Maybe I need a
reference to this Anaysis Pack addin in my Access Database???

Whatever the problem is, it seems I can get around it by Shelling Excel
and then using GetObject() to make a connection with the open Excel
session. If I do this everything seems to work fine.

Brad,
AFAIK starting Excel by automation, as you are, does not load addins
automatically. You have to load them yourself. e.g.
Application.AddIns("Analysis ToolPak").Installed = True
Using Shell, starts XL "normally" hence the addin is loaded.

NickHK

"Brad" <[email protected]> wrote in message
news:[email protected]...
> I've only just discovered that the User Defined Function I've been
> rabbiting on about is actually a function supplied by an add-in. It's
> NETWORKDAYS()
>
> Could this be something to do with the problem? Maybe I need a
> reference to this Anaysis Pack addin in my Access Database???
>