Closing Excel from Access VBA (1 Viewer)

[REZ]

I'm trying to export an MS Form, and then edit it.
My code works fine the first time, but when I try it again I get errors.
It think its because the Excel application is not closing properly. I've searched all over to try get it to close, but its still always there in my task manager.
Everytime I run the code, there is another instance of Excel in the task manager, which tells me I'm not closing it properly, but I'm at a loss.
What am I missing?
After the first time running I get errors on the rows starting ".range" or ".selection"
Here is the code:
Private Sub BtnExport2_Click()

Dim xl As Object
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

FullPath = DLookup("hyperlinkbase", "tblusers", "id = " & Forms!frmmain!UserName) & "\TodaysPayments.xls"
DoCmd.OutputTo acOutputForm, "frmexpenses", acFormatXLS, FullPath

Set xl = CreateObject("excel.application")
Set WB = xl.workbooks.Open(FullPath)
Set WS = WB.sheets("frmexpenses")
With xl
.columns("A:B").Delete
.columns("D:E").Delete
.columns("E:E").Delete
.columns("F:I").Delete
.Range(Range("E1"), Range("E1").End(xlDown)).Select
.Selection.Offset(0, 1).Select
.Selection.Value = "=C1 & ""-"" & A1"
.Selection.Copy
.Selection.Offset(0, 1).PasteSpecial xlPasteValues
.columns("A:A").Delete
.columns("B:B").Delete
.columns("D").Delete
.columns("d").Select
.Selection.Cut
.columns("B:B").Select
.Selection.Insert Shift:=xlToRight
.columns("A:B").ColumnWidth = 30
.Range("A11").Interior.Color = RGB(221, 221, 221)
.Range("A11").Borders.Color = RGB(0, 0, 0)
.Range("A1").Select
End With
xl.Visible = True

WB.Close
xl.Quit

Set WS = Nothing
Set WB = Nothing
Set xl = Nothing

End Sub

 

[theDBguy]

What error messages were you getting?

 

[The_Doc_Man]

Set xl = CreateObject("excel.application")
Set WB = xl.workbooks.Open(FullPath)
Set WS = WB.sheets("frmexpenses")
With xl
.columns("A:B").Delete
.columns("D:E").Delete
.columns("E:E").Delete
.columns("F:I").Delete
.Range(Range("E1"), Range("E1").End(xlDown)).Select
.Selection.Offset(0, 1).Select

The object "xl" is an application object. Using "With xl" you are trying to perform actions on something that doesn't have columns, ranges, or selections. A WORKSHEET object (such as what you have called WS) has those things. Therefore, I think your "with" is with the wrong object. Just to be clear... yes, you could have done "WITH XL.WB.WS" and that would have been exact. But at least try "WItH WS" to see if that clears up stuff. And I'm puzzled that you said you got errors on the .Range or .Select items but DIDN'T mention errors on the .Columns code. That shouldn't have worked either.

Finally, just as advice... if you are debugging code, NEVER EVER turn off warnings. Turn them off when the code is working. (Although if it is working you wouldn't get any warnings anyway.) Warnings are an annoyance to users but a well-received friend to developers.

 

[CJ_London]

Please use code tags to preserve formatting- this sort of thing is really annoying advice hides the actual code


columns("D").Delete
.columns("d").Select

 

[ebs17]

Dim xl As Object
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

This is stylistically inaccurate. Are you using early binding or late binding?

My code works fine the first time, but when I try it again I get errors.

This is typical for improper referencing of objects. If you run this code in Excel, it will probably work better because Excel knows itself and its objects and has an existing error tolerance to cover up certain errors.
However, if the code is executed from outside (Access), the external application does not know the objects, but must be referenced cleanly and always completely.
As already said: Columns, rows, cells and ranges are objects of a worksheet, not of an Excel instance and not objects of a workbook.
The Selection object is in turn an Application object.

With WS
   .Columns("A:B").Delete
   .Columns("D:E").Delete
   .Columns("E:E").Delete
   .Columns("F:I").Delete
   .Range(.Range("E1"), .Range("E1").End(xlDown)).Select  ' <= look for .Range instead of Range in the arguments
   xl.Selection.Offset(0, 1).Select
   ...

WB.Close

After all the actions, you should say whether you want it to be saved or not without Excel having to ask. Such a query dialog can be hidden and block all further progress.

 

[Gasman]

What happens when you delete columns D and E, F becomes E, and you are deleting that as E , G then becomes E and you are deleting what was H as F ?
That is why you should walk your code.

 

[ebs17]

Content: You're just deleting it.
Maybe you should export a planned query instead of some collapsed entity.

 

[REZ]

Thanks All for your responses.

Doc Man, I tried chaning it to With WS, but I got Errors even on the first try.

So I changed it back to With xl, I'm getting Error 1004 "Method Range of Object Global Failed".
Where have I turned off Warnings?

CJ London, how do I use code tags?

Ebs17, I have no idea what early and late binding are. Can you explain please?
My original code had them all as Object, after searching through Forums I made some changes - this was one of them. I guess that was wrong.
I've been using basic code for several years now, but I'm kind of groping in the dark with this one.

Re, using a planned query instead, I thought about it, but the form is first being filtered by the user, how do I get those filters to move over to a query? I thought it would be easier to do it this way, as the export is for the "Boss" who is quite particular about excel formatting, so I would have to Format any export anyway.

And non of the replies really answers my real query, how can I get the excel application to close properly?

 

[ebs17]

And non of the replies really answers my real query

Since I won't repeat myself, you should repeat working through the answers.

 

[Gasman]

Well it is never going to be xl
Perhaps With wb.ws or xl.wb.ws ?

You need to say WHAT errors you are getting. They are generally a clue.

 

[ebs17]

how do I get those filters to move over to a query?

sSQL = "SELECT * FROM QueryX WHERE " & Me.Filter

Instead of * you would of course only list required columns.

 

[REZ]

Set xl = CreateObject("excel.application")
Set WB = xl.workbooks.Open(FullPath)
Set WS = WB.sheets("frmexpenses")
With xl
.columns("A:B").Delete
.columns("D:E").Delete
.columns("E:E").Delete
.columns("F:I").Delete
.Range(Range("E1"), Range("E1").End(xlDown)).Select
.Selection.Offset(0, 1).Select

The object "xl" is an application object. Using "With xl" you are trying to perform actions on something that doesn't have columns, ranges, or selections. A WORKSHEET object (such as what you have called WS) has those things. Therefore, I think your "with" is with the wrong object. Just to be clear... yes, you could have done "WITH XL.WB.WS" and that would have been exact. But at least try "WItH WS" to see if that clears up stuff. And I'm puzzled that you said you got errors on the .Range or .Select items but DIDN'T mention errors on the .Columns code. That shouldn't have worked either.

Finally, just as advice... if you are debugging code, NEVER EVER turn off warnings. Turn them off when the code is working. (Although if it is working you wouldn't get any warnings anyway.) Warnings are an annoyance to users but a well-received friend to developers.

With Xl.WB.WS gives me this error "Run time error 438, Object doesn't support this property or method"

 

[REZ]

Ok, I did some more reading on early/late binding and got some help with excel and finally got it to work.

This is my code:
Dim xl As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

FullPath = DLookup("hyperlinkbase", "tblusers", "id = " & Forms!frmmain!UserName) & "\TodaysPayments.xls"
DoCmd.OutputTo acOutputForm, "frmexpenses", acFormatXLS, FullPath
On Error Resume Next
Set xl = GetObject(, "excel.application")
If Err.Number <> 0 Then
Set xl = CreateObject("excel.application")
End If
Set WB = xl.workbooks.Open(FullPath)
Set WS = WB.sheets("frmexpenses")
With xl

Rest is same as before.
It works nicely and isn't opening a new instance of Excel each time.
(It would still be nice to know why it isn't closeing properly at the end of the code, but at least it works for now)

Thanks for all your time

 

[The_Doc_Man]

The reason you are not getting new copies of Excel is of course that now that you have started using GetObject, you are just re-using the copy you opened before. But when you are finished, you will still have one leftover "dangling" copy of Excel.

You have one of the more dangerous lines of code to use for developing and debugging - "On Error Resume Next" - which is the same as turning off warnings. Your code will still trigger errors but now the "last-chance" error handler won't get involved. That is the error handler inside Access itself. When it triggers, it means that your sub has bombed out at some instruction. That is also why you get the particular format of error message you reported and why your code stops running. At that point, your sub's VBA is not running. Only Access is running.

If you know how to single-step your code in debug mode, you could set a breakpoint on the line that says "xl.Visible = true" then single-step through that and the next two lines (the last of which is the "xl.quit"). After each step you could test the ERR object to see if you have an error pending. That would tell you where you went wrong. Note: If that first line ALSO has an ERR.NUMBER or ERR.DESCRIPTION other than 0/blank after it executes, your proper place for the breakpoint is earlier in the code because something has already gone wrong before that point. You might even profitably put the breakpoint on the "Fullpath = " line or the "DoCmd" line and single-step from there just to be sure you catch and identify the real errors.

For the record, Excel error 1004 is a catch-all that it uses when it doesn't really know or can't tell you specifically which object is wrong. I've tripped over that one so many times that I think "1004" is imprinted in my toes by now. What it is telling you is that something is wrong with a qualifier or an explicit chained qualifier (such as XL.WB.WS) or an implicit chained qualifier. Implicit chaining is when you define something based on another object. Your definition that says "WS = WB.something-or-another" is IMPLICIT chaining of qualifiers - i.e. a qualifier "buried" in the definition.

As a result, if some object goes wrong, you are no longer working with the thing on which you thought you were working. Only an app object can be quit, so I'm thinking that something else has invalidated the XL object (implied pointer) before that time. Which is why XL.QUIT doesn't work.

 

[Gasman]

I cannot believe that with xl and using .columns work as well, but it does?
However you do not have option explicit as FullPath was not defined.

 

[CJ_London]

CJ London, how do I use code tags?

Paste your code to your post, highlight it and click the </> button

 

[Gasman]

Paste your code to your post, highlight it and click the </> button

This works?

.Range("E1").End(xlDown).Select

 

[CJ_London]

This works?

huh?

 

[Gasman]

huh?

O/P had

.Range(Range("E1"), Range("E1").End(xlDown)).Select

and I got the Global error

however just using the second part works. I am not even sure you can use two parts like that anyway?

 

[REZ]

Doc Man, you're 100% correct - of course.
I've been looking at it for so long, I didn't even realise that putting in "on error resume next" would just skip over the problem, and the result is not at all what I need. Though I still don't understand how I've turned off warnings besides that?

So I'm back to where I started.

I'm stepping through as you suggested
I'll let you all know what I come up with