Running Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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 xlsheet As Excel.Worksheet

'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 xlsheet = 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
 
Excel.Application.Visible = True
Click to expand...

This line is, I think, the one that forces Access to create a new implicit
Excel.Application object; and you can't set it to Nothing because you can't
see it. It will keep the Excel app open until Access itself closes.

You should use the object variable that you actually do own:

xlApp.Visible = True

Another couple of comments: you just don't need most of the other objects
you declare -- the xlWorkbook and xlSheet variables are never used
(apparently) and your code will be more leak-proof by getting rid of them:

With xlApp.Workbooks
.Open("c:\docs_etc\POs.xls")

.Open("c:\docs_etc\INVs.xls")

Excel in particular seems to be vulnerable to implicit object handles
getting left behind, so you have to make sure that everthing is properly
referenced by your own variables.

Hope that helps


Tim F
 
Kevin K. Sullivan said:
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


open
Click to expand...
 
To help keep everything clean, I usually use some error handling like this:

Private Sub Command32_Click()
on error goto Err_Command32_Click
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

'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

Exit_Command32_Click:
'These release your object variables to clean up memory
Set xlsheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing
Exit Sub

Err_Command32_Click:
xlApp.Quit 'This will close Excel if there is an error so excel.exe isn't
left in memory
msgbox err.description
Resume Exit_Command32_Click

End Sub
 
Back
Top