Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening an Excel Spreadsheet from Access with VBA
Can you post all the code (excel and access)
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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??? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening an ACCESS db from the VBE in EXCEL | Excel Worksheet Functions | |||
Opening linked spreadsheet file in Access | Excel Programming | |||
Opening Excel from Access | Excel Programming | |||
Opening Access from Excel | Excel Programming | |||
Opening Excel via Access | Excel Programming |