excel closes after started from outlook macro 1

[wingster]

hi!

i start excel from an outlook macro and write some data in a sheet. but when the macro is done, excel closes... how can i bring excel to "live" on after the termination of the outlook macro?

thank you

greetings from germany

wingster

 

[dsi]

It may help if you post some of your code.

 

[wingster]

Set excel_sheet = CreateObject("Excel.Sheet&quot
excel_sheet.Application.Workbooks.Open ("tabelle.xlt&quot

excel_sheet.Application.cells(1, 2) = "am"
excel_sheet.Application.cells(1, 3) = "um"
excel_sheet.Application.cells(1, 4) = "von"
' and so on...


after i did this several times my makro ends, end excel ends too... it starts, and the cells get filled up with data... i just want them to stay longer than my macro is running...

 

[dsi]

Do you CreateObject more than once? How does your code end? Do you set your objects to Nothing when you are done?

Dim xlApp As Excel.Application
Dim wb As Workbook, ws As Worksheet

Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.WindowState = xlMaximized

'Open the Data File
Set wb = xlApp.Workbooks.Open("C:\YourFile.xls")

'Select the Desired Sheet
Set ws = wb.Sheets("Sheet1")
ws.Select

'Modify Cells Here
ws.Cells(1, 2) = "am"
ws.Cells(1, 3) = "um"
ws.Cells(1, 4) = "von"
        
'SKIP - Close/Save Changes/Exit Excel
'wb.Close True
'xlApp.Quit

'Clear Objects
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

 

[wingster]

i tried to rewrite my code like yours, but it didn't work as a macro in outlook... i set my objects to nothing, this is the last statement in my code.

 

[jonscott8]

Option1, Instead of

Dim excel_sheet as Object

you can use

Static excel_sheet as object

The drawback to this is that Excel will close when Outlook closes.

Option2, Instead of using automation to create the process like so

Set excel_sheet = CreateObject("Excel.Application&quot

you can use the Shell function and then the GetObject function

Dim oExcel As Object
Dim sPath As String
Set oExcel = CreateObject("Excel.Application&quot
sPath = oExcel.Path & "\excel.exe"
oExcel.Quit
Set oExcel = Nothing

Shell sPath
Set oExcel = GetObject(, "Excel.Application&quot
'do whatya gotta do Jon Hawkins

 

[wingster]

works!

thank you!

greetings from rainy germany

wingster