Re: Running Excel

From: Derek Wittman (DerekWittman_at_discussions.microsoft.com)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 11:23:02 -0700

Kevin,
I've gotten away from the SHELL command and am stuck with what appears to be
Excel.exe remaining in memory, even after I close down the application
appropriately. And sometimes, I don't even have the option to close it
appropriately.

Here's my new code:

Private Sub Command32_Click()
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xl*** As Excel.Work***

'These commands export the data into spreadsheets.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Q_InvoiceList", "c:\documents and settings\" & fOSUserName() & "\my
documents\" & ProjectName & "INV.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_POList",
"c:\documents and settings\" & fOSUserName() & "\my documents\" & ProjectName
& "POs.xls", True

Set xlApp = CreateObject("excel.application")
Set xlWorkbook = xlApp.Workbooks.Open("c:\documents and settings\" &
fOSUserName() & "\my documents\" & ProjectName & "POs.xls")
Set xlsheet = xlWorkbook.Sheets(1)
Set xlWorkbook = xlApp.Workbooks.Open("c:\documents and settings\" &
fOSUserName() & "\my documents\" & ProjectName & "INVs.xls")
Set xlsheet = xlWorkbook.Sheets(1)
Excel.Application.Visible = True
     'Sometimes the code 'hangs' here at the preceeding line
Set xl*** = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
     'I thought this cleared the application from memory, but it still shows
up in the Task Manager processes, even after I EXIT it manually.
End Sub

Any further help would be appreciated!
Thank you!
Derek

"Kevin K. Sullivan" wrote:

> Derek,
>
> When you use Shell, you lose all subsequent control of the shelled program.
> Instead, use automation:
> (air code)
> ------
> Private Sub Command32_Click()
> Dim xlApp as Object
> Dim strYourFileNameHere As String
>
> strYourFileNameHere = "c:\documents and settings\dwittma\my documents\New
> Jersey CIPINVs.xls"
> Set xlApp = CreateObject("Excel.Application")
> xlApp.Open strYourFileNameHere
> Set xlApp = Nothing ' set the reference to nothing
> End Sub
> -----
> Alternatively, you could use Shell and send the path to the workbook because
> Excel uses the first parameter as a workbook name to open:
>
> Shell Chr(34) & strPathToExcel & Chr(34) & " " & Chr(34) & strPathToWorkbook
> & Chr(34), vbMaximizedFocus 'use double-quote character to protect for
> spaces in filenames
>
> HTH,
>
> Kevin
>
>
> "Derek Wittman" <DerekWittman@discussions.microsoft.com> wrote in message
> news:3B9575A0-A8FB-4DBA-A724-17AF7487B8EF@microsoft.com...
> > Good morning,
> > I'm trying to run Excel and open a workbook or 2 for my manager. It's
> from
> > a command button on a form that will export the query to Excel, then open
> the
> > workbooks.
> >
> > I know the code for the exportation of the query to Excel. I can get
> Excel
> > to open. Just not with opening a file. Can someone please help me with
> the
> > code?
> >
> > Private Sub Command32_Click()
> > DoCmd.SetWarnings False
> > Call Shell("c:\program files\microsoft office\office\excel.exe",
> > vbMaximizedFocus)
> > Workbooks.Open ("c:\documents and settings\dwittma\my documents\New Jersey
> > CIPINVs.xls")
> > End Sub
> >
> > Thank you!
> > Derek
>
>
>
>