×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to close Excel from Access using VBA?
3

How to close Excel from Access using VBA?

How to close Excel from Access using VBA?

(OP)
Hi All
I have tried to use automation between Access and Excel for creating charts. It works all right until I close the Excel application. I close Excel with .quit as described in Help and Excel disappears from applications in the task manager, however, it continues to run under processes, which creates problems the next time I open Excel. I use Access 2000 and the code is below.

I would be very pleased if any one could help me with solving this problem.
Lena



Dim objXLApp As New EXCEL.Application
Dim objXLBook As EXCEL.Workbook
Dim objResultsSheet As EXCEL.Worksheet
Dim objChartSheet As EXCEL.Worksheet

objXLApp.Workbooks.Add
Set objXLBook = objXLApp.ActiveWorkbook
objXLBook.Sheets(1).Select
objXLBook.Sheets(1).Name = "Data"
objXLBook.Sheets(2).Select
objXLBook.Sheets(2).Name = "Charts"
Set objResultsSheet = objXLBook.Worksheets("Data")
Set objChartSheet = objXLBook.Worksheets("Charts")

' Code transferring data from Access to Excel and creates a number of charts in Excel

objXLBook.Close 'SaveChanges:=False
objXLApp.Quit
             
Set objResultsSheet = Nothing
Set objChartSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

RE: How to close Excel from Access using VBA?

That's exactly the same problem I have now. Did you find a solution in the meantime? You could help me a lot. Thank you. Robert

RE: How to close Excel from Access using VBA?

I have the same problem. I think one solution could be to find the windows handle with the following API and then close that handle.

Right after you create the Excel object you do this:

hWndXl = FindWindow("XLMAIN", AppExcel.Caption)

Then you get the handle and it just matter of closing process with that particulat handle. Well that what i'm trying to figure out. But i'm not finish yet.

Steven
pseriksen@yahoo.no

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

RE: How to close Excel from Access using VBA?

(OP)
Hi There

Just realised that the discussion on how to open and closing Excel from Access is still going on.

Mine is working now. I have these two routines one to open and one to closing Excel. In between I have a fair bit off code so this might not be enough but try this first – Steven I think you are making your life more complicated than it has to be.

This code is made in 97 but works now in 2000.
Lena

Public objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objResultsSheet As Excel.Worksheet
Dim objChartSheet As Excel.Worksheet

Public Sub CreateExcel()

Set objXLApp = CreateObject("excel.application")
Set objXLBook = objXLApp.Workbooks.Add

objXLBook.Sheets(1).Select
objXLBook.Sheets(1).Name = "Data"
objXLBook.Sheets(2).Select
objXLBook.Sheets(2).Name = "Charts"
Set objResultsSheet = objXLBook.Worksheets("data")
Set objChartSheet = objXLBook.Worksheets("charts")

End Sub

Public Sub SaveFig()
On Error GoTo Checkerror


objXLApp.Quit
Set newChart = Nothing
Set objResultsSheet = Nothing
Set objChartSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

Exit Sub

Exit_Checkerror:
        Exit Sub
     
Checkerror:
        Resume Exit_Checkerror
End Sub

RE: How to close Excel from Access using VBA?

I've think i've solved my problem now. It's seems like a matter of using the right command for doing the right thing or else it creates another instance of the object, so at the end when you close it you're not able to close the second instance.

One thing i haven't solved it to sort the worksheet i create from Access. If i try to sort with this command:

ObjXL.Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I'm not able to close Excel, without this command everything is ok. This is how i did it:

Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet
        
        
Set ObjXL = CreateObject("Excel.Application")
Set ObjXLBook = ObjXL.Workbooks.Open("Path & navn of xl file")
Set ObjXLSheet = ObjXLBook.Worksheets("Name of sheet")

..
..

ObjXLBook.Save
Set ObjXLSheet = Nothing
Set ObjXLBook = Nothing
ObjXL.Quit
Set ObjXL = Nothing

This works fine for me, without the sort stuff. It's bothering me allot since i create a Pivot table and i wish to sort it.

Steven

RE: How to close Excel from Access using VBA?

I've solved it in another way. Silly of me not discover that solution earlier. I just did the sort in Access, before exporting it to Excel as pivot table. So now everything is just perfect.

It's seems like using the Xl object creates another instance of Excel, so when you close it you're not able to close the second.

Anyway i'm happy now.

Thanks

Steven

RE: How to close Excel from Access using VBA?

Dear all,
I had the same problem and tried all your suggestions but did not work.
Now I found the solution and am glad to share it with you all:

Const ERR_APP_NOTRUNNING As Long = 429
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
 If Err = ERR_APP_NOTRUNNING Then
    Set xlApp = New Excel.Application
 End If



Good Luck

Dynasty

RE: How to close Excel from Access using VBA?

Did anyone ever find a solution to this?  Dynasty's solution works great to run if you have an Excel app still running from the previous execution.  I'm still stuck with the problem of trying to get Excel to close after execution, as I have to be able to open an Excel spreadsheet after execution.

RE: How to close Excel from Access using VBA?

All,

I had the same issues with trying to control Excel 2000 from Access 97. The Microsoft Excel 9.0 Object Library ActiveX control does not close the spreadsheet file as described in several books, MSDN or the online help file examples.

To get it to work, I discarded the Excel.Application ActiveX approach and simply used variable type Object to control Excel.

Thanks to the excellent users on this forum with great tips, here is the basic code I used to finally make it work. This code does the following:

1) Opens an existing spreadsheet
2) Shows it on screen
3) Writes some data from a table to several different worksheet cells
4) Selects a worksheet and 'Autofits' the cells
5) Saves the existing file
6) closes it

Private Sub ExcelCode()

Dim appExcel As Object
Dim workBook As Object
Dim workSheet As Object

' Open an existing spreadsheet
Set appExcel = GetObject("C:\Accounting\File1.xls")

' Show spreadsheet on screen
appExcel.Application.Visible = True
appExcel.Parent.Windows(1).Visible = True

' Loop through record set and populate cells with data
Set rst = db.OpenRecordset("tbl_xref")
rst.MoveFirst
Do Until rst.EOF
  Set workSheet = appExcel.Worksheets(rst![Tab])
  workSheet.Cells(rst![CellRow], rst![CellColumn]).Value = rst![Value]
  rst.MoveNext
Loop

' Autofit all columns on a worksheet
Set workSheet = appExcel.Worksheets("wks1")
'workSheet.Cells.Select  NOTE:this line's code doesn't work!
workSheet.Cells.EntireColumn.AutoFit

' Turn prompting OFF and save the sheet with original name
appExcel.Application.DisplayAlerts = False
appExcel.Application.Save
appExcel.Application.DisplayAlerts = True
appExcel.Application.Quit

' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing

End Sub


Hope this helps...

flize

RE: How to close Excel from Access using VBA?

flize,

I think you forgot to "set" workbook...I get an error...fyi

Scott

RE: How to close Excel from Access using VBA?

I found this solution at the Microsoft knowlegde database (Microsoft knows his bug)number "Q187745: BUG: Microsoft Excel Does Not Repaint Properly with Automation".

It says:
This article was previously published under Q187745
SYMPTOMS
When automating Microsoft Excel 97, Excel 2000, or Excel 2002, if the application window has been made visible and the user manually closes it, Excel will not repaint correctly the next time the application window is made visible again.
CAUSE
This problem occurs when a user attempts to quit a running instance of Excel while an Automation client still has a reference to the application object for that instance. By design, Excel does not quit an instance of itself unless all external references are released; if a user tries to quit Excel manually, the application window is merely hidden so that the Automation client may continue working. If, however, the Automation client attempts to make Excel visible again, the application window will not be displayed properly and repainting will not occur.
RESOLUTION
A workaround is to set the ScreenUpdating property of the application object to True after you have made the window visible. This will force Excel to repaint its client area so that it will be displayed properly.
STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.
MORE INFORMATION
Steps to Reproduce Behavior
Start a new Standard EXE project in Visual Basic. Form1 is created by default.
On the Project menu, click References, and then select the Microsoft Excel 8.0 object library. For Excel 2000, select Microsoft Excel 9.0 object library, and for Excel 2002, select the Microsoft Excel 10.0 object library.
Place a CommandButton on Form1.
Copy the following code to the Code Window of Form1:       Private oApp As Excel.Application

      Private Sub Command1_Click()
         oApp.Visible = True
      End Sub

      Private Sub Form_Load()
         Set oApp = CreateObject("Excel.Application")
         Command1.Caption = "Show Excel"
      End Sub




      Private Sub Form_Unload(Cancel As Integer)
         Set oApp = Nothing
      End Sub
                        
On the Run menu, click Start, or press the F5 key to start the program.
Click on the Command button to make Excel visible. Close Excel by pressing the Close button on Excel's title bar, or by selecting Exit from the File menu. Now press the Visual Basic Command button again, and note that Excel does not paint itself correctly.
Repeat the steps again with the Command button's code modified as follows:       Private Sub Command1_Click()
         oApp.Visible = True
         oApp.ScreenUpdating = True
      End Sub

RE: How to close Excel from Access using VBA?

I've searched the web for hours trying to figure out how to open, print, then close the excel file.  This is all triggered by a button on the switchboard.  Well, this code seems to do the trick.   Only one problem, when i open another excel file manualy excell doesnt seem to repaint itself.  I tried adding:
        appExcel.Application.ScreenUpdating = True
like jvantichelt says refering to MSDN article Q187745 above in his post.  But still no luck.  The odd thing is.  when i automatically open a file through this script again it works with no problems.  It seems like you need to end the Excel.exe proccess after each time you run the script, is there a way to do that?

And I'm sure i did the print object wrong with the with statement, but it works, is there an eiser way to do it?  also if I set the Application.visible=false then printing the active window doesnt work.  Is there a way to hide excel, to make it open in the background?

CODE

Function OpenExcelFile()
    Hourglass = True
    Dim appExcel As Object
    Dim objExcel As Object
    Set objExcel = ActiveSheet
    Dim workBook As Object
    Dim workSheet As Object
    Dim ExcelFile As String
    
    ExcelFile = "c:\test.xls"
    
        ' Open an existing spreadsheet
        Set appExcel = GetObject(ExcelFile)
        ' Show spreadsheet on screen
        appExcel.Application.Visible = True
        appExcel.Parent.Windows(1).Visible = True
        
        'Print the active worksheet
        With objExcel
             ActiveSheet.PrintOut
        End With
        
        ' Turn prompting OFF and save the sheet with original name
        appExcel.Application.DisplayAlerts = False
        'appExcel.Application.Save
        'appExcel.Application.DisplayAlerts = True
        appExcel.Application.Quit

        ' Release objects
        Set workSheet = Nothing
        Set workBook = Nothing
        Set appExcel = Nothing
        Set objExcel = Nothing
        Hourglass = False
End Function

RE: How to close Excel from Access using VBA?

I am having the same problem where an instance of Excel remains open but hidden (viewable in Task manager - processes).  I am opening an existing spreadsheet within Access, populating it with data, and then closing it. Actually I have a loop where I update about 40 spreadsheets before I close the app.  I don't want to make Excel visible during this process so don't need .Visible or .ScreenUpdating. The proposed solution to close Excel that works for some people does not work for me:

Do Until  ...

..
..

xlBook.Save
xlBook.Close
Set xlBook = Nothing
..
Loop  ' loop thru all spreadsheets

xlApp.Quit
Set xlApp = Nothing
End Sub

Any other suggestions on how to close the hidden instance of Excel without using Task Manager or re-booting.  Thanks in advance.

RE: How to close Excel from Access using VBA?

Yeah, there has to be some way to kill the process itself.  Maybe finding the window handle and sending the kill message?

RE: How to close Excel from Access using VBA?

I was faffing around with this problem for an hour or two also. I am using winxp and access and Excel XP. I found that if you have a excel function call that is not called off the excel application object then the thing hangs. Look below as all the functions hang off the objExcel object.

Before you do anything fancy try this code.

    Dim objExcel As Excel.Application
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Add
    objExcel.Visible = False
    objExcel.range("A1") = "WSH was here"
    objExcel.DisplayAlerts = False
    objExcel.Workbooks(1).SaveAs ("R:\zvezek1.xls")
    objExcel.DisplayAlerts = True
    objExcel.range("A1:A2").Select
    objExcel.Quit
    Set objExcel = Nothing

Then check your processes.

This should work for you.

What I found was that leaving a line in the code like.....

range("A1") = "WSH was here"
 instead of  ...
objExcel.range("A1") = "WSH was here"

will stop excel closing properly.

Hope this may help someone.

RE: How to close Excel from Access using VBA?

I have a similar problem and after trying all the suggestions giving I have been unable to resolve it.  I am using Win98, excel 2000, and vb6.  I am opening an existing excel file from vb, editing a large number of cells, and closing it.  I am doing the editing in a loop.  When the loop variable is small everything works as expected.  When I increased the variable excel no longer closes.  It remains in the running processes.  When I increase it even more, I get a component request pending error.  Excel stops responding to the vb program.  I thought I had an issue with RAM but I increased that and it didn't seem to make a difference.  Any ideas?
My code:

Public xlApp As Excel.Application
Public xlWb As Excel.Workbook
Public xlWs1 As Excel.Worksheet
Public xlWs2 As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open("C:\WINDOWS\desktop\Tonya\2Counters\FreqData.xls")
Set xlWs1 = xlWb.Worksheets(1)
Set xlWs2 = xlWb.Worksheets(2)

'begin speed optimization
xlApp.Calculation = xlCalculationManual
xlApp.ScreenUpdating = False

'Loop
'Edit spreadsheets here
'End Loop

'end speed optimization
 xlApp.Calculation = xlCalculationAutomatic
 xlApp.ScreenUpdating = True
 
 xlWb.Save
 xlWb.Close
 xlApp.Quit
 Set xlWs1 = Nothing
 Set xlWs2 = Nothing
 Set xlWb = Nothing
 Set xlApp = Nothing

RE: How to close Excel from Access using VBA?

If you don't want to have problems when opening excel files try:

Dim objXLApp As New Excel.Application
    Dim objXLBook As Excel.workBook
    Dim currentWorksheet As Excel.workSheet
    Const ERR_APP_NOTRUNNING As Long = 429
    
    On Error Resume Next
    'Set objXLApp = CreateObject("Excel.Application")
    Set objXLApp = GetObject(, "Excel.Application")
    If Err = ERR_APP_NOTRUNNING Then
        Set objXLApp = New Excel.Application
    End If
    
    objXLApp.Application.DisplayAlerts = False
    objXLApp.Application.ScreenUpdating = True
    
    'on ouvre le fichier où ont été exportées les données
    Set objXLBook = objXLApp.Workbooks.Open(FileName:=strFileName)

'code
'----
'---
'---

objXLBook.Save
objXLBook.Close
objXLApp.Application.DisplayAlerts = True
objXLApp.Quit
    
Set currentWorksheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

I don't know why but if you turn on ScreenUpdating = True everything works fine when you want to use excel after having opened it with access!

RE: How to close Excel from Access using VBA?

I've been trying to access Excel objects from Access for a while now, and this code is the first thing I've seen that's supposed to do what I'm looking for.  When I try to run some of this code tho, excel.application isn't recognized in Access.  Is there a library I have to enable for this to work?

RE: How to close Excel from Access using VBA?

Yes, go to Tools/References and click on Microsoft Excel 9.0 Object Library, or whatever version you have.

John Borges

RE: How to close Excel from Access using VBA?

Hi,

I notice in most of these solutions you are defining a new excel object, then opening a workbook.  This actually spwans another instance of excel within itself which cannot be closed easily.  If you don't create a new instance initially it seems to work better.  Here is some code that works fine for me.

    Dim xlWorkBook As Workbook
     
    Set xlWorkBook = Workbooks.Open("C:\stats\login.csv")
    
    xlWorkBook.Application.Visible = True
    
'your code here
'Worksheets(1).Select
'Sheets("login").Select
    
    xlWorkBook.Application.DisplayAlerts = False
    
    xlWorkBook.SaveAs "c:\stats\loginIMPORTED.csv"
    xlWorkBook.Application.Quit

Hope this help someone.

RE: How to close Excel from Access using VBA?

..darnit..

The only reason my code was working was because I was breaking out of the program rather than allowing it to run through, obviously when I stopped it any processes it has spawned are automatically quit.

I have been battling with this for 2 days now and it's causing me problems.  If the EXCEL process is open then I can't open any other sheets, they freeze, I did actually manage to get round this by using:

    xlWorkBook.Application.IgnoreRemoteRequests = True

This leaves the EXCEL.EXE process in the background but will not affect any other sheets at all.  It doesn't spawn a new process when the code is run again either.

An acceptable compromise I would say.

RE: How to close Excel from Access using VBA?

The problem with the IgnoreRemoteRequests = True option is that it disables the ability to open a workbook by simply double-clicking on it.  Also, if set to false after the execution of the code, the problem reoccurs.  Is there any other solution for this problem that doesn't remove one bit of functionality for the sake of another?

Burglar

RE: How to close Excel from Access using VBA?

After pulling my hair out for the last 2 days, ratsstar69's solution actually worked.  I thought I had tried it but I missed one range statement.  When I added the Objxl.range instead, it started working.  Thanks a lot.

RE: How to close Excel from Access using VBA?

OK this is probably very simple, but I have just got a new PC and this code JUST TO OPEN EXCEL e.g.

Dim objXLApp As Object
Dim objXLBook As Object


Set objXLApp = CreateObject("Excel.Application")
objXLApp.Workbooks.Add
Set objXLBook = objXLApp.ActiveWorkbook


 used to work on my old PC, however it now will not work on my new one.
- I have tried enabling the TOOLS>>REFERENCES in the library for Excel
- Was wondering if  there is some sort of enhanced security in the latest version of excel to prevent other programs from controlling it??

Any ideas gratefully recieved!!

Dan

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close