Keep Excel open after app has ran

[Soniaski]

I have an application that creates and formats an Excel spreadsheet, then saves it and closes down Excel. It's been working fine. Now, I have been asked to keep the Excel file open after the application has ran.

While this is sloppy, I can easily do this by not closing out my reference to Excel. The problem comes in when the user needs to create another spreadsheet. The instance of Excel stays in the task manager even after the user has closed out the original spreadsheet.

Is it possible to clear the instance of Excel out of the task manager while still keeping the Excel file open?

 

[Rx_]

Here is one way:
http://access.mvps.org/access/api/api0018.htm
Paste the code in a general module

Then to open "C:\Book1.xlsx"
fHandleFile "C:\Book1.xlsx", WIN_NORMAL

Private Declare Function apiShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
    As Long
'***App Window Constants***
Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 3            'Open Maximized
Public Const WIN_MIN = 2            'Open Minimized
'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&
'***************Usage Examples***********************
'Open a folder:     ?fHandleFile("C:\TEMP\",WIN_NORMAL)
'Call Email app:    ?fHandleFile("mailto:dash10@hotmail.com",WIN_NORMAL)
'Open URL:          ?fHandleFile("[URL]http://home.att.net/~dashish[/URL]", WIN_NORMAL)
'Handle Unknown extensions (call Open With Dialog):
'                   ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
'Start Access instance:
'                   ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
'****************************************************
Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
 
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

 

[Soniaski]

Wow! That worked beautifully! Thank you so much for your help. Such a clean solution!

 

[Soniaski]

I've been using this apiShellExecute function with great success. Now I've come across a new dilema... The word document that I am opening is password protected. I want to be able to pass the password through using the apiShellExecute function. I've done some research out on the World Wide Web and can't find anything.

Any ideas? Not sure if this should be posted in a new thread or not...

 

[Rx_]

Tough question! I couldn't find anything either. Posted this up on the MSDN site listed below. Will let you know if anything useful shows up.

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/723839c1-9ed9-4964-996e-5d7e43d04f6e

 

[Rx_]

This was actually on this forum. Nobody is going to like the Sendkey with clear text password. If this is the only way to make it work for now, at least consider creating a variable for the password text and populate the variable with some process that makes it difficult to read.
ShellExecute hwnd, "Open", strdbName, "", "C:\", 3
Sleep (700)
SendKeys "password", True
SendKeys "{ENTER}", True

 

[Soniaski]

Thank you Rx... Let me try to play with this. Great idea on the variable holding the password.