Thats not what I mean:
in the Access VBA IDE -->Tools-->References find Microsoft Excel Object
Library and check it
Then you can do stuff like
var=Excel.Application.WorksheetFunction.Sum(2 + 3)
which will return 5
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
Have you tried just setting a reference to Excel in the Access VBA code
rather than instantiating Excel?
No. It never even occurred to me. Didn't know it was possible.
Or could it be a matter of semantics?
Here's what I mean by "Instantiating Excel":
======================================================
Public Function Excel_Start(ByRef theSS As Excel.Application) As
Boolean
3000 DebugStackPush mModuleName & ": Excel_Start: "
3001 On Error GoTo Excel_Start_err
' PURPOSE: - Start an instance of MS Excel or use an existing
instance
' - Leave "theSS" pointing to the Excel Basic engine
' behind the newly-opened document
' ACCEPTS: - Pointer to the spreadsheet TB used by calling routine
' RETURNS: True/False depending on success
'
' NOTES: 1) We do not want to keep opening up new instances of
Excel every time this routine
' is called, so we do the "= Nothing" check to see if
theSS has already been set.
' OTHOH the user may have closed that instance of
Excel, leaving theSS pointing to
' NeverNeverLand. Experimentation shows that an error
2753 is generated in this case.
' Hence the error trap and the "userClosedExcel"
switch.
'
'SAMPLE:
' ?SpreadSheetOpenExisting("D:\Dev\SEI\DataSource
\BuySell.xls", gExcelApp)
3002 Dim userClosedExcel As Long
Dim serverNotExist As Long
Dim oktoproceed As Boolean
Const oleError = 2753
Const rpcServerUnavailable = -2147023174
Const remoteServerNotExist = 462
Const docAlreadyOpen = 1004
Excel_Start_loop:
' ---------------------------------------------------
' Create an instance of Excel
3010 If (theSS Is Nothing) Or (userClosedExcel = 1) Then
3011 Set theSS = CreateObject("Excel.Application")
'3012 With theSs
'3013 .Workbooks.Add
'3014 .ScreenUpdating = True
'3015 .Visible = True
'3016 End With
3019 End If
' ---------------------------------------------------
' Open up the spreadsheet
3999 Excel_Start = True
Excel_Start_xit:
DebugStackPop
On Error Resume Next
Exit Function
Excel_Start_err:
Select Case Err
Case 2772
MsgBox "Unable to locate Microsoft Excel program. Please
notify your administrator", 16, "Cannot Open MS Excel"
Resume Excel_Start_xit
Case oleError, rpcServerUnavailable
If userClosedExcel = 0 Then
userClosedExcel = userClosedExcel + 1
Resume Excel_Start_loop
Else
BugAlert True, "Unable to open MS Excel. Suspect user
may have closed existing instance."
Resume Excel_Start_xit
End If
Case remoteServerNotExist
If serverNotExist = 0 Then
serverNotExist = serverNotExist + 1
Set theSS = Nothing
Resume Excel_Start_loop
Else
BugAlert True, "Unable to open MS Excel. Suspect user
may have closed existing instance."
Resume Excel_Start_xit
End If
Case docAlreadyOpen
BugAlert True, ""
Case Else
BugAlert True, ""
Resume Excel_Start_xit
End Select
Resume Excel_Start_xit 'Shouldn't be needed, but just in
case.....
End Function
======================================================
I'll try to give it a shot sometime today and report back.
