Closing All Excel from Access

[nick1408]

Hi Guys,

I've been getting some help on this code from Stack Overflow but work doesn't let me acces that site. My code is as follows:

Public Sub CloseAllExcel(Close1 As Boolean)
'On Error GoTo handler

   Dim xl As Excel.Application
   Dim wb As Excel.Workbook

   Dim strPath As String
   strQueryName = "Closed Excel"
strPathUser = Environ$("USERPROFILE") & "\Desktop\"
strPath = strPathUser & strQueryName & ".txt"



   If Close1 Then

   Dim fso As Object
   Set fso = CreateObject("Scripting.FileSystemObject")

   Dim oFile As Object
   Set oFile = fso.CreateTextFile(strPath)



   Do While xl Is Nothing
      Set xl = GetObject(, "Excel.Application")
      For Each wb In xl.Workbooks

       oFile.WriteLine xl.ActiveWorkbook.FullName
         wb.Save
         wb.Close
      Next
       oFile.Close
       Set fso = Nothing
       Set oFile = Nothing
      xl.Quit
      Set xl = Nothing
   Loop

   Exit Sub

   Else
   Dim FileNum As Integer
   Dim DataLine As String

   FileNum = FreeFile()
   Open strPath For Input As #FileNum

     While Not EOF(FileNum)
        Line Input #FileNum, DataLine
        Workbooks.Open DataLine
     Wend
   Exit Sub
   End If

'handler:
'   If Err <> 429 Then 'ActiveX component can't create object
'      MsgBox Err.Description, vbInformation
'   End If

End Sub

Private Sub Toggle0_Click()
CloseAllExcel True
End Sub

Private Sub Toggle1_Click()
CloseAllExcel False
End Sub

The bottom two subs are to activate the actual code. The issue I have is as follows.:

Set xl = GetObject(, "Excel.Application")

Runtime Error 429. This happens when there is no Excel windows open. I feel this is something to do with the Do While line.

ofile.close

Runtime error 91. This happens after all Excel windows have been closed. This appears to be closing Notepad but it isn't actually open (there is a notepad.exe process but no actual screen to see). Also, if I comment this line out the loop is endless. I think this is due to an excel.exe process still being open. Ulitmately, I want to kill all the excel processes but this code is what I want as the precursor so all workbooks can be reopened again later. I know I am close as I can get ti to all work within an Excel enviroment, I just can't work it out for Access.

Thanks guys.

 

[isladogs]

The first issue is easily fixed.

Code:
Set xl = GetObject(, "Excel.Application")
Runtime Error 429. This happens when there is no Excel windows open. I feel this is something to do with the Do While line.

You use GetObject when Excel is open
When closed you need to use CreateObject instead

To fix, add the following code in the error handler section

Err_handler:
   If Err.Number = 429 Then
    'Excel is not running; open Excel with CreateObject
      Set xl = CreateObject("Excel.Application")
      Resume Next
    Else
        MsgBox Err.Description, vbInformation
    End If

I'll try & look at the other issue tomorrow

 

[isladogs]

One more thing - have you checked your VBA references?

On my machine, the reference Microsoft Office 12.0 Authorization Control (for Access 2007) is listed as MISSING. This looked odd as other references are 14.0 (2010 version)

I updated the missing reference to 14.0 & that fixed the message box that flashed up when Excel wasn't running

 

[nick1408]

Hi Ridders,

Thanks for the help. The first bit is fine as I only want Excel to close if it is already open. I don't need to open it then close it again. I think something along the lines of

If xl = nothing then

This isn't correct obviously, an IF statement interrogating while Excel is open should be fine.

I have since changed the reference from 12 to 14 and still get the same error.

 

[isladogs]

You raised error 429 as an issue ii the first post. That's why I responded to it