Ending an automation session between Excel and Access

[garry.oxnard]

I've seen variations of this problem posted on Google Groups when
trying to resolve my own, very similar problem. As far as I can see
no-one has had a eureka moment, but I'm getting desperate and hope
someone can help.

I've designed an Excel template which a lot of users will use to
generate an .xls file.. When the template is run as a document, a macro
button within the worksheet allows them to open up an Access 'Address'
database where they select an address, click a macro button (within
Access) and the address data is pasted into Excel. Access closes.

All works well, apart from the fact that the session between Excel and
Access seems to remain open. I see the previously saved .xls files
listed in the VBA project window (and an equal number of "UNSAVED VBA
Component" references in the VBA References box).

I have tried a variety of ways to end the session between Excel and
Access and explored the various suggestion made by others to a similar
problem, but I'm no further forward. The key parts of my code are as
follows:

1. Opening up the "AddressLookup" Access database from within Excel.
works fine.
Private Sub cmdAddress_Click()

ActiveSheet.Unprotect

Application.DisplayAlerts = False

ThisWorkbook.SaveAs "C:\Decent Homes Data Systems\" & "DHomes1.xls"
Application.DisplayAlerts = True

MsgBox "The Address Lookup database will now open. Please Click OK
and Wait"

Dim ac As Object
On Error Resume Next
Set ac = GetObject(, "Access.Application")

If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
ac.OpenCurrentDatabase "C:\Decent Homes Data
Systems\AddressLookup.mdb"
ac.UserControl = True
End If

AppActivate "Microsoft Access"
End Sub

2. Access opens and the user selectes an address an clicks the macro
button to send the address back into Excel and close Access. Works
fine.

3. The user clicks the "Save & Close" button in Excel to save the
worksheet and close Excel.

Private Sub cmdClose_Click()

Dim booVar As Boolean

booVar = booValid
strMessage = "Please enter a value in boxes highlighted in green."

Call Validate 'runs a checking procedure to prevent nulls in cells.

booVar = booValid
If Not booVar Then
'strMessage is one line procedure advising user to complete all cells
coloured green.
MsgBox strMessage, 0
Exit Sub
End If

Call CreateValues 'sets values in certain cells prior to delete of
worksheets.

Application.DisplayAlerts = False
ThisWorkbook.SaveAs "C:\Decent Homes Data Systems\" & Range("T2") &
".xls" 'saves workbook in name of unique cell reference

Application.Run "WZTEMPLT.XLA!Commit" 'runs template wizard to fire
record into another Access database. Works fine.

Call ShowToolbars 'turns toolbars on again to minimise file size.
Call DeleteButtons 'deletes macro buttons to minimise file size.
Call DeleteSheets 'deletes unwanted sheets to minimise file size.
Call DeleteForms 'deletes unwanted forms to minimise file size.

Application.DisplayAlerts = False

ActiveWorkbook.Save
Set ac = Nothing
Application.Quit
End Sub

It all works ok (a lot of work) but I just can't crack this final
problem. I would be very grateful if someone could help me out here. I
don't use the Google Desktop bar by the way. I have no COM Add-Ins
operating apart from the Template Wizard.

Thanks all.

 

[moon]

At stage 2., before the user clicks the macro button and before Access is
closed, press Ctrl-Alt-Delete. How many times you see MSACCESS.EXE in the
processes list?

 

[Tom Ogilvy]

I would suggest the opening of access and just reading the data into a
listbox in Excel using ADO

DAO/ADO
http://www.erlandsendata.no/english/index.php?t=envbadac

That should be cleaner and offer more control than just letting the user
rummage around Access.

You can also look here to see some discussion of using Automation.
OLE
http://www.erlandsendata.no/english/index.php?t=envbaole

 

[garry.oxnard]

I would suggest the opening of access and just reading the data into a
listbox in Excel using ADO

DAO/ADO
http://www.erlandsendata.no/english/index.php?t=envbadac

That should be cleaner and offer more control than just letting the user
rummage around Access.

You can also look here to see some discussion of using Automation.
OLE
http://www.erlandsendata.no/english/index.php?t=envbaole

To Moon and Tom
Thanks for your quick reply guys.

First - Moon. There is always only one instance of MSAccess.exe running
in the Task Manager, when I do as you suggest. When Access closes only
Excel remains. However, each time I run the spreadsheet and open Access
from within it, there is yet another reference in the VB window and the
item "Unsaved VBA Project in the References window. This only happens
when I open the Access session from within Excel.

Tom - with respect, the users (to be) of this programme won't be
'rummaging around in Access' believe me. The Address lookup database
has 215,000 plus records in it and users retrieve any given address
very quickly by selecting a street name from a combo (main form), after
which the subform containing related properties retrieves all related
addresses within seconds. The user then simply needs to click the
desired address for it to be copied to an unbound textbox at the top of
the form. It's fast and pretty idiot proof (fields are not editable and
there are validations to prevent nulls etc etc). I've worked long and
hard on it and understandably am reluctant to begin again considering
the deadline looming. Particularly when I'm apparently using the right
code. Why is so much of Microsoft's stuff so hit and miss?

I do appreciate your assistence however, and the link you provided is
excellent.
Any more comments would be gratefully received (from you or anyone else
out there). Even Bill Gates.

Thanks again.
Garry

 

[NickHK]

I would say look at stage 2 where Access writes to Excel.
As you are not passing a reference of the instance of Excel to Access,
Access has to find it. How does do that ?
Are you releasing all references to Excel from Access ?
Are they all fully qualified ?

Whilst not wrong, why use AppActivate when you have a reference to Access :
ac.Visible=true 'Or whatever

NickHK
P.S. I would agree with Tom that a couple of line of code and ADO would
replace all of your Access automation. But if you don't want to go that
road, that's up to you.

 

[garry.oxnard]

I would say look at stage 2 where Access writes to Excel.
As you are not passing a reference of the instance of Excel to Access,
Access has to find it. How does do that ?
Are you releasing all references to Excel from Access ?
Are they all fully qualified ?

Whilst not wrong, why use AppActivate when you have a reference to Access :
ac.Visible=true 'Or whatever

NickHK
P.S. I would agree with Tom that a couple of line of code and ADO would
replace all of your Access automation. But if you don't want to go that
road, that's up to you.

Hi Nick
I agree that the problem lies with the point at which Access writes to
Excel. It seems clear that closing Access down in the way that I do
(using the macro button) does not end the session - hence the "Unsaved
VBA Project" messages in VB References etc. I just wish I had more
skills to understand what to do. Every attempt to release references to
Excel from Access makes no difference. I've tried using the
CurrentDb.Connection.Close line, which logically should end connections
between Excel and Access - but this fails. It's all a bit past my skill
level I'm afraid.

As for Access referencing Excel, the only way I could get it to work is
as follows (don't be too critical - I'm a monkey banging on a
typewriter.).

Private Sub cmdExport_Address_Click()

If IsNull(Me.txtAddress) Then
MsgBox "Please select an Address by clicking on the ""Street Name""
field and choosing a Street. Then click the UPRN of the property you
wish to export."
Exit Sub
End If

Dim strSQLCase As String
Dim strXLCase As String
Dim strFieldCase As String
Dim strCellCase As String
Dim strXLPath As String

strXLPath = "C:\Decent Homes Data Systems\DHomes1.xls"

strFieldCase = "'" & txtNumber & "'"
strCellCase = "InputSheet$T2:U2"
strSQLCase = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellCase & "] SET F1=" _
& strFieldCase & ";"


CurrentDb.Execute strSQLCase, dbFailOnError


Dim strSQLAddress As String
Dim strSQLX As String
Dim strSQLY As String
Dim strSQLUPRN As String
Dim strFieldAddress As String 'variable to receive contents of
Address field
Dim strFieldX As String 'variable to receive contents of X
field
Dim strFieldY As String
Dim strFieldUPRN As String


Dim strCellRef As String 'variable which holds Excel cell
ref you want to copy data to.
Dim strCellX As String 'variable to receive contents of X
field.
Dim strCellY As String
Dim strCellUPRN As String

strXLPath = "C:\Decent Homes Data Systems\DHomes1.xls"
strFieldAddress = "'" & txtAddress & "'"
strCellRef = "InputSheet$I3:U3"
strSQLAddress = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellRef & "] SET F1=" _
& strFieldAddress & ";"

strFieldX = "'" & txtX & "'"
strCellX = "InputSheet$J27:J27"
strSQLX = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellX & "] SET F1=" _
& strFieldX & ";"

strFieldY = "'" & txtY & "'"
strCellY = "InputSheet$J28:J28"
strSQLY = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellY & "] SET F1=" _
& strFieldY & ";"

strFieldUPRN = "'" & txtUPRN & "'"
strCellUPRN = "InputSheet$J29:J29"
strSQLUPRN = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellUPRN & "] SET F1=" _
& strFieldUPRN & ";"

CurrentDb.Execute strSQLAddress, dbFailOnError
CurrentDb.Execute strSQLX, dbFailOnError
CurrentDb.Execute strSQLY, dbFailOnError
CurrentDb.Execute strSQLUPRN, dbFailOnError

Dim Greg As String
strSQLAddress = ""

CurrentDb.Close

Application.Quit acQuitSaveNone

End Sub


Please note that there are 5 items of data being written to matching
cells in Excel (the Address, the Unique Ref, the Unique Property Ref No
and two geo-co-ordinates so that data can be used in a GIS). Hence the
repetition of code and more variables than you can shake a stick at.
I'm not against doing what you and Tom suggest (opening of access and
just reading the data into a listbox in Excel using ADO) but (a) I
don't know how to do this and (b) I'm worried that the users of the
system would be faced with an endless list of addresses making it very
time consuming to trace the address they require).

And I'm not getting paid to do all of this. Talk about ageing before
your time.

Any help would be (oh so) gratefully received.
Thanks again
Garry.

 

[NickHK]

I don't use this syntax, which presumably works, but

strFieldCase = "'" & txtNumber & "'"
strCellCase = "InputSheet$T2:U2"
strSQLCase = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellCase & "] SET F1=" _
& strFieldCase & ";"

looks strange to me, as there is no WHERE clause.

Why not link your Excel file to the Access and then the update is much
easier.
UPDATE LinkedTable SET FieldName=NewValue WHERE
IDorWhatever=TheRecordYouWantToUpdate

NickHK

I would say look at stage 2 where Access writes to Excel.
As you are not passing a reference of the instance of Excel to Access,
Access has to find it. How does do that ?
Are you releasing all references to Excel from Access ?
Are they all fully qualified ?

Whilst not wrong, why use AppActivate when you have a reference to Access :
ac.Visible=true 'Or whatever

NickHK
P.S. I would agree with Tom that a couple of line of code and ADO would
replace all of your Access automation. But if you don't want to go that
road, that's up to you.

Hi Nick
I agree that the problem lies with the point at which Access writes to
Excel. It seems clear that closing Access down in the way that I do
(using the macro button) does not end the session - hence the "Unsaved
VBA Project" messages in VB References etc. I just wish I had more
skills to understand what to do. Every attempt to release references to
Excel from Access makes no difference. I've tried using the
CurrentDb.Connection.Close line, which logically should end connections
between Excel and Access - but this fails. It's all a bit past my skill
level I'm afraid.

As for Access referencing Excel, the only way I could get it to work is
as follows (don't be too critical - I'm a monkey banging on a
typewriter.).

Private Sub cmdExport_Address_Click()

If IsNull(Me.txtAddress) Then
MsgBox "Please select an Address by clicking on the ""Street Name""
field and choosing a Street. Then click the UPRN of the property you
wish to export."
Exit Sub
End If

Dim strSQLCase As String
Dim strXLCase As String
Dim strFieldCase As String
Dim strCellCase As String
Dim strXLPath As String

strXLPath = "C:\Decent Homes Data Systems\DHomes1.xls"

strFieldCase = "'" & txtNumber & "'"
strCellCase = "InputSheet$T2:U2"
strSQLCase = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellCase & "] SET F1=" _
& strFieldCase & ";"


CurrentDb.Execute strSQLCase, dbFailOnError


Dim strSQLAddress As String
Dim strSQLX As String
Dim strSQLY As String
Dim strSQLUPRN As String
Dim strFieldAddress As String 'variable to receive contents of
Address field
Dim strFieldX As String 'variable to receive contents of X
field
Dim strFieldY As String
Dim strFieldUPRN As String


Dim strCellRef As String 'variable which holds Excel cell
ref you want to copy data to.
Dim strCellX As String 'variable to receive contents of X
field.
Dim strCellY As String
Dim strCellUPRN As String

strXLPath = "C:\Decent Homes Data Systems\DHomes1.xls"
strFieldAddress = "'" & txtAddress & "'"
strCellRef = "InputSheet$I3:U3"
strSQLAddress = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellRef & "] SET F1=" _
& strFieldAddress & ";"

strFieldX = "'" & txtX & "'"
strCellX = "InputSheet$J27:J27"
strSQLX = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellX & "] SET F1=" _
& strFieldX & ";"

strFieldY = "'" & txtY & "'"
strCellY = "InputSheet$J28:J28"
strSQLY = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellY & "] SET F1=" _
& strFieldY & ";"

strFieldUPRN = "'" & txtUPRN & "'"
strCellUPRN = "InputSheet$J29:J29"
strSQLUPRN = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCellUPRN & "] SET F1=" _
& strFieldUPRN & ";"

CurrentDb.Execute strSQLAddress, dbFailOnError
CurrentDb.Execute strSQLX, dbFailOnError
CurrentDb.Execute strSQLY, dbFailOnError
CurrentDb.Execute strSQLUPRN, dbFailOnError

Dim Greg As String
strSQLAddress = ""

CurrentDb.Close

Application.Quit acQuitSaveNone

End Sub


Please note that there are 5 items of data being written to matching
cells in Excel (the Address, the Unique Ref, the Unique Property Ref No
and two geo-co-ordinates so that data can be used in a GIS). Hence the
repetition of code and more variables than you can shake a stick at.
I'm not against doing what you and Tom suggest (opening of access and
just reading the data into a listbox in Excel using ADO) but (a) I
don't know how to do this and (b) I'm worried that the users of the
system would be faced with an endless list of addresses making it very
time consuming to trace the address they require).

And I'm not getting paid to do all of this. Talk about ageing before
your time.

Any help would be (oh so) gratefully received.
Thanks again
Garry.