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?

Opening an Excel Spreadsheet from Access with VBA
 

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?

Opening an Excel Spreadsheet from Access with VBA
 

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....

Opening an Excel Spreadsheet from Access with VBA
 

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....

Opening an Excel Spreadsheet from Access with VBA
 

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.

Opening an Excel Spreadsheet from Access with VBA
 

Can you post all the code (excel and access)

Opening an Excel Spreadsheet from Access with VBA
 

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 befo=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.

Opening an Excel Spreadsheet from Access with VBA
 

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???

Opening an Excel Spreadsheet from Access with VBA
 

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.

Opening an Excel Spreadsheet from Access with VBA
 

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" wrote in message
ups.com...
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???