Open Excel Application, help please.

[Kib]

Hello,
I have a function:
Public Function ExcelCeiling(Number As Double, Significance As Double) As Double
ExcelCeiling = Excel.WorksheetFunction.Ceiling(Number, Significance)
End Function
that i use in my program
however i noticed it causes problems
After the function is run, if I hit
ctrl-alt-del
I see that there is an "Excel" application running.
If I end that task, and then try to run the function again.
It will not let me, I get an error that:
"The remote server machine does not exist or is unavailable"
How do I properly write this function so I do not have "Excel" left open, and can still run the function more than once?
I know this is confusiong, if more info or description is needed please let me know.
Thank you very much
Kib

 

[JoeMiller]

Excel has to run when you are calling for functions from excel in access, otherwise you can't get the functionality. The instance of excel should close when you exit access or close that mdb (not sure which).

Joe Miller
joe.miller@flotech.net

 

[Kib]

Yes, but how do I close it before the mdb closes? Can I do it inside the code automatically? Thanks.
Kib

 

[JoeMiller]

If you close it while it's running you'll get the error you described, so why would you want to close it?

Joe Miller
joe.miller@flotech.net

 

[Kib]

even if i close it after the functions have run completely?

 

[JoeMiller]

Well if you want to close it after your functions have completely run that's fine. But I don't see the reason for doing so unless you have such a disgustingly underpowered computer it is crippled by having the two apps open. As for how to close it, I don't know that one.

Joe Miller
joe.miller@flotech.net

 

[SetBuilder]

I don't know if you have this solved, but when I open a new instance of Excel from Access, I close it using the following command:
objXL.Application.Quit

Here is some code that opens a template, runs an Access query (rs), creates a worksheet in the template, populates the worksheet and closes everything.

Dim objXL As Object


' -------Open, and make visible the Excel Template (.xlt) which resides on the desktop
Set objXL = GetObject(XLT_LOCATION)
objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True
objXL.Application.DisplayAlerts = False
Set objSheet = objXL.Worksheets("Ship To FPA Data&quot
objSheet.Activate


'--------- Open the recordset, and activate the sheet in the template

'------------------------------------------------Delete the current sheet
objXL.Application.Sheets("Ship To FPA Data&quot.Select 'select sheet
objXL.Application.DisplayAlerts = False
objXL.Application.ActiveWindow.SelectedSheets.Delete 'delete sheet
'------------------------------------------------Insert a new blank sheet and rename it
objXL.Application.Sheets(1).Select 'Move to insert Point
objXL.Application.Sheets.Add 'Add new Sheet
objXL.Application.Sheets("Sheet1&quot.Select 'Select sheet
objXL.Application.Sheets("sheet1&quot.Name = "Ship To FPA Data" 'rename sheet
'------------------------------------------------Put in Column Headings
Set objSheet = objXL.Worksheets("Ship To FPA Data&quot
objSheet.Activate
'------------------------------------------------Put in Column Headings
With objXL.ActiveSheet
With .Rows("1:1&quot
.WrapText = True
.Interior.ColorIndex = 37
.Interior.Pattern = xlSolid
End With
.Columns("B:C&quot.HorizontalAlignment = xlRight
.Columns("G:O&quot.NumberFormat = "mm/dd/yy"
‘-----------------------------------put in data from rs
Do Until rs.EOF
.Cells(x, 1).Value = rs![Product Code]
.Cells(x, 2).Value = rs![Product Family]
.Cells(x, 3).Value = rs![SN]
.Cells(x, 4).Value = rs![Customer Name]

x = x + 1
rs.MoveNext

Loop
‘---------------------------------------------------autofit a column after data is there
.Columns("D&quot.AutoFit

End With
‘----------------------------------------close the query (source of data )
rs.Close
'----------------------------------- Set the save string, and save the spreadsheet
'strSaveAs = "C:\Windows\Desktop\" & Format(Date, "mmddyyyy&quot & ".xls"
strSaveAs = XLS_LOCATION
objXL.SaveCopyAs strSaveAs
'---------------------------------------------------------------------- Quit Excel
objXL.Application.DisplayAlerts = False
objXL.Application.Quit
'------------------------------------------------------------------------Clean up

Populate_Exit:
DoCmd.Hourglass False
Set objXL = Nothing
Set objSheet = Nothing
Set objRange = Nothing
Set rs = Nothing
Exit Sub
objXL.Application.Quit