Opening and closing excel from Access with VBA

[Vanguard]

Hi again,

I've got another problem that has been illuding me for a while now. It happens when I try and open excel from access. The first time it works like a charm but after closing excel the excel process still runs in the background. When I run the macro again excel flashes on the screen quickly and disappears, the process still running in the task manager.

Has anyone else had this problem before or know of how I might fix it?


Private Sub Command181_Click()
'Opens drawing register and updates values

Dim ApXL As Object
Dim xlWB As Object
Dim xlWS As Object


Dim ProjectNumber As String
Dim ProjectName As String

Dim Year As String
Dim Prefix As String

'Lookup and set variables from access record for job number.
ProjectNumber = DLookup("[Job Number]", "ProjectT", "[Job Number] = [Forms].[Projects Explorer].[Job Number]")
ProjectName = DLookup("[Project Name]", "ProjectT", "[Job Number] = [Forms].[Projects Explorer].[Job Number]")
FirstName = DLookup("[First Name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
LastName = DLookup("[Last name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
Company = DLookup("[Company Name]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
StreetAddress = DLookup("[StreetAddress]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
Suburbvar = DLookup("[Suburb]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
Statevar = DLookup("[State]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")
PostCode = DLookup("[PostCode]", "ClientsT", "[ClientID] = [Forms].[Projects Explorer].[Client]")

'Obtain year from job number code.
If Len(ProjectNumber) = 5 Then
Prefix = Left(ProjectNumber, 2)
Year = "20" & Prefix
Else
Prefix = Left(ProjectNumber, 1)
Year = "200" & Prefix
End If

'Open .xls file for pre macro job numbers.
If ProjectNumber <= 14072 Then
Set ApXL = CreateObject("Excel.Application")
Set xlWB = Workbooks.Open("E:\Data\jobfiles\" & Year & "\" & ProjectNumber & "\Standard Forms\" & ProjectNumber & " Drawing Register.xls")
ApXL.Visible = True

'Open .xlxm file for post marco job mumbers.
Else

Set ApXL = CreateObject("Excel.Application")
Set xlWB = Workbooks.Open("E:\Data\jobfiles\" & Year & "\" & ProjectNumber & "\Standard Forms\" & ProjectNumber & " Drawing Register.xlsm")
ApXL.Visible = True

Set xlWS = xlWB.Worksheets("Sheet1")
xlWS.Activate

rspCreate = MsgBox("Would You like to import project specific information from the projects database?", 65540, "ASD Projects Database")
If rspCreate = vbYes Then
'Project name
xlWS.Range("D4").Value = [ProjectName]
'Project nunber
xlWS.Range("D5").Value = [ProjectNumber]
'Client name & address line 1
xlWS.Range("B54").Value = [Company] & " " & [FirstName] & " " & [LastName]
'Client name & address line 2
xlWS.Range("D54").Value = [StreetAddress] & " " & [Suburbvar] & " " & [Statevar] & " " & [PostCode]
'Attn name
xlWS.Range("D56").Value = [FirstName] & " " & [LastName]
End If

ApXL.Visible = True

End If

Set xlWS = Nothing
Set x1WB = Nothing
Set ApXL = Nothing


End Sub

[gmayor]

Quickly glancing at your code, that code starts Excel and opens a workbook, but it doesn't close the workbook or Quit the Excel application? Thus if you run it again it opens another instance of Excel and tries to open the workbook which is already open in the first instance.

If Excel is running it is in any case faster to access the running application rather than create a new one


On Error Resume Next
Set ApXL = GetObject(, "Excel.Application")
If Err Then
'bStartApp = True 'set a boolean value if the code starts excel which can be used to deternine whether to quit excel when the code is run
Set ApXL = CreateObject("Excel.Application")
End If
On Error GoTo 0

[Vanguard]

Thanks for your help gmayor.

Yeah you are right about the program not automatically closing. The idea is to open up a workbook and populate it with data from the access database from which point you can enter other job related information then save print to pdf and close excel.

It is as if after it opens that the macro or some part of it still has ownership of the excel "session" (not sure of correct terminology) and keeps the excel process running after the user has manually closed excel. Does that make sense?

I will incorporate the code example for loading excel. It's something that was on my wish list that I hadn't gotten around to figuring out how to do yet. I didn't think it would have had an effect on the problem I'm having but I'll let you know after I incorporate it.