Solved

Close not visible Excel workbook in ACCESS2010

Posted on 2013-12-20
2
403 Views
Last Modified: 2013-12-20
I have a process that if it is interrupted can leave the workbook open but not visible  So, if the user runs he process again, I get a a read only message, but there is nothing they can see to close.  I got the below from another question on EE, but how do I change it to only close the one specific Excel.  That is, I want to have the workbook's name in this procedure.  Also, I commented out the WScript.Sleep 2000 as (1) I don't know what it does and (2) the process did close workbooks without it.  But I want it to only close the one and then exit the procedure.  The name of the workbook to close is "UploadTemplate".

Sandra

Public Sub CloseExcel()
On Error Resume Next
Dim objOffice As Object
Dim objWindow As Object
Dim WBook As Object

While Err.Number = 0
    Set objOffice = GetObject(, "Excel.Application")
    objOffice.DisplayAlerts = False
    For Each objWindow In objOffice.Windows
        objWindow.Activate
        Set WBook = objOffice.ActiveWorkbook
        WBook.Saved = True
        WBook.Close
    Next
    objOffice.DisplayAlerts = True
    objOffice.Quit
    Set objOffice = Nothing
''    WScript.Sleep 2000
Wend
MsgBox "Done"


End Sub
0
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39732564
I have a process that if it is interrupted can leave the workbook open but not visible
How does the process get interrupted? By that, I mean if you're opening a workbook that is not visible, then obviously you're doing this through automation, and if so, then you have control over this, and should be able to shut down your code gracefully if it fails.

In general, you do this:

dim xl As New Excel.Application
Dim wb As Excel.WorkBook
etc etc

On Error GoTo Err:

<code here>

Exit:
  On Error Resume Next
  Set wb = Nothing
  xl.Close
  Set xl = Nothing
  Exit Sub ' or Function
Err:
  <code here to handle error>
  Resume Exit
0
 

Author Closing Comment

by:ssmith94015
ID: 39732688
Yes, it was invisible.  Thanks for the suggestion, it was driving me crazy.  

Sandra
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question