disconnect XL - reminder on close

[lmv]

I want to have a reminder on program close no matter what is used to close it
to disconnect the linked xl sheets the utilities form OR the program close X.

Have you disconnected the xl sheets?

OR BETTER YET

Have a script run to automatically disconnect them IF they are connected.

Now I have a buttons on the Utilities form but each xl sheet is seperate.
'--------
Private Sub cmdDisOrig_Click()
On Error GoTo Err_cmdDisOrig_Click
Dim curDatabase As Object
Set curDatabase = CurrentDb

curDatabase.TableDefs.Delete "Co-Orig_xls"
MSGBOX "The Linked Orig Excel sheet has been disconnected"
Exit_cmdDisOrig_Click:
Exit Sub

Err_cmdDisOrig_Click:
MSGBOX "Error " & "CO-ORIG.xls is NOT Connected"
Resume Exit_cmdDisOrig_Click

End Sub
'-----------
Private Sub cmdDeletexl_Click()
On Error GoTo Err_cmdDeletexl_Click
Dim curDatabase As Object
Set curDatabase = CurrentDb

curDatabase.TableDefs.Delete "CO-new_xls"
MSGBOX "The Linked Excel sheet COnew has been disconnected"
Exit_cmdDeletexl_Click:
Exit Sub

Err_cmdDeletexl_Click:
MSGBOX "Error " & "EXCEL NOT Connected"
Resume Exit_cmdDeletexl_Click
End Sub
'____

Any ideas?

Thanks!

 

[Douglas J. Steele]

Try something along the lines of:

Sub DisconnectExcel()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim lngLoop As Long

Set dbCurr = CurrentDb()
For lngLoop = (dbCurr.TableDefs.Count - 1) to 0 Step -1
Set tdfCurr = dbCurr.TableDefs(lngLoop)
If InStr(tdfCurr.Connect, "Excel") > 0 Then
dbCurr.TableDefs.Delete tdfCurr.Name
End If
Next lngLoop
Set tdfCurr = Nothing
Set dbCurr = Nothing

End Sub

 

[lmv]

Thanks!
I wasn't sure what to do so I made a module and am calling it from the
Utility form close event. Funny it works fine in one of my db but the other I
get a VBA error... "expected variable or procedure not module".

Private Sub Form_Close()
Call DisconnectExcel
End Sub

Should I be doing something else? Is there a way to make it an event that
happens whenever Access shuts down even if my utility form isn't open?

Thanks.

 

[Douglas J. Steele]

A run-time error, or a compile error?

 

[lmv]

Compile error...
"expected variable or procedure not module"

 

[Douglas J. Steele]

What line of code is highlighted when you compile?

 

[lmv]

Call DisconnectExcel

What line of code is highlighted when you compile?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)





.

 

[Douglas J. Steele]

What did you name the module where the code exists?

It cannot be named DisconnectExcel.

 

[lmv]

That was the answer... I renamed it and its fine.
Thanks again!