Closing Excel from Access VBA

[Isaac]

Still unlikely - Excel uses the reference to Range object without any qualification.

In fact I'd counter that having a whole load of WS. sprinkled all over the place would actually make it harder to spot the problem.

It's the point at which you are typing it and seeing intellisense where I think the error would have been more likely avoided in the first place.
With blocks generally require the developer to have a good handle on the heirarchy that they are dealing with. Setting variables and actually using them is much easier for a newbie IMO.

Regardless David, I can agree to disagree if you can

 

[gemma-the-husky]

Added to what @The_Doc_Man said, never forget that an error handler is not reentrant. Once you get an error you must ideally use a short block of code in the handler to manage the error, and then resume. If you just goto, and forget to resume any further error will be ignored which can produce difficult to trace problems.

 

[REZ]

.Range(.Range("E1"), .Range("E1").End(xlDown)).Offset(0, 1).Select

same thing. sometimes it works and some times it throws error 462 at that line.
Excel is still not closing

 

[REZ]

If you just goto, and forget to resume any further error will be ignored which can produce difficult to trace problems.

How do I get it to catch the next errror.
I put "Debug.Print Err.Number" at the end so i can see what happened. but its not stopping at the error

 

[cheekybuddha]

Please show us the code as you have it now.

 

[REZ]

Please show us the code as you have it now.

Dim fullpath As String
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 GoTo ErrorHandler
Set xl = GetObject(, "excel.application")
Set WB = xl.workbooks.Open(fullpath)
Set WS = WB.sheets("frmexpenses")

  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)).Offset(0, 1).Select
   With Selection
   .Value = "=C1 & ""-"" & A1"
    .Copy
   .Offset(0, 1).PasteSpecial xlPasteValues
    End With
    .columns("A:A").Delete
    .columns("B:B").Delete
    .columns("D:D").Delete
    .columns("d:D").Select
    With Selection
   .Cut
   End With
    .columns("B:B").Select
    With Selection
   .Insert Shift:=xlToRight
   End With
    .columns("A:B").ColumnWidth = 30
    .Range("A1:D1").Interior.Color = RGB(221, 221, 221)
    .Range("A1:D1").Borders.Color = RGB(0, 0, 0)
    .Range("A1").Select
        End With
Debug.Print Err.Number

 xl.Visible = True

 WB.Close
  
 xl.Quit
 
Set WS = Nothing
Set WB = Nothing
Set xl = Nothing
ErrorHandler:
If Err.Number = 429 Then
Set xl = CreateObject("excel.application")
Resume Next
Else
End If

 

[theDBguy]

Hope you don't mind, I've reformatted your post to use code tags to make it easier to read.

 

[Gasman]

I want to select the column down to the last row that has data, so that i can offset it and write a formula concatenating 2 cells in the next column

And doesn't just the last part of that do that?

Edit: I get it to run successfully if you put the full stop in front of the Range keyword where they are missing?

I am still puzzled as to how the xl.columns actually works.

 

[REZ]

Hope you don't mind, I've reformatted your post to use code tags to make it easier to read

Thank you, I can't figure out how to do it

 

[Isaac]

can't figure out how to do it

 

[Isaac]

I am still puzzled as to how the xl.columns actually works

which would have probably come to the fore of OP's mind if they were typing xl. instead of a with block, as it would be just visually clearer that there was no columns belonging to xl

 

[Gasman]

If you click that, a new window will open and you paste your code into that.
I myself just type [ code ], then paste the code then type [ /code ] which does the same thing. NB I had to put a space around the square brackets else it would be taken as code.

 

[REZ]

If you click that, a new window will open and you paste your code into that.
I myself just type [ code ], then paste the code then type [ /code ] which does the same thing. NB I had to put a space around the square brackets else it would be taken as code.

At least I've learnt something new!
I still can't get the excel application to close, but I seem to have a "work around"

 

[theDBguy]

Thank you, I can't figure out how to do it

See if this page helps. Scroll down to the code tag.

BB codes

The list of BB codes you can use to spice up the look of your messages. This page shows a list of all BB codes that are available.

www.access-programmers.co.uk

Edit: Oops, looks like I forgot to send this.

 

[gemma-the-husky]

In your errorhandler, if you have error 429, you are calling createobject("excel.application*). If that call fails it won't produce another trappable error, as you are still running the error handler. It will just fail. You have to resume to a suitable point to reset the error handler again. Maybe that's the issue. It can take a lot of time to get a error handler to work correctly.

 

[Gasman]

which would have probably come to the fore of OP's mind if they were typing xl. instead of a with block, as it would be just visually clearer that there was no columns belonging to xl

Yet it works?
Try it yourself.

 

[The_Doc_Man]

same thing. sometimes it works and some times it throws error 462 at that line.
Excel is still not closing

Error 462 is complaining about object variable issues. Which is to say that one of the expressed or implied objects in a qualification sequence is wrong or broken or unplugged. For that to happen, something isn't working right in the early part of the code. Since I can't duplicate your setup, I can't be more specific than that. I'll say it now without fear of strong contradiction: Excel can be unusually picky about qualifiers sometimes.

 

[ebs17]

You might think Excel would be difficult.

Closing Excel from Access VBA

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...

www.access-programmers.co.uk

If you don't read, you can guess a lot.

Code in #46:

With Selection
With xl.Selection
Selection is an Application object and so must
1) at all and
2) be referenced to an application object.
Since you can reference objects (ranges, etc.) and thus address them specifically, you often do not need to explicitly activate and select and can then largely do without the Selection object.

WB.Close True ' or
WB.Close SaveChanges:=True
If you change the workbook, the query about saving should be answered immediately.

 

[gemma-the-husky]

Error 462 is complaining about object variable issues. Which is to say that one of the expressed or implied objects in a qualification sequence is wrong or broken or unplugged. For that to happen, something isn't working right in the early part of the code. Since I can't duplicate your setup, I can't be more specific than that. I'll say it now without fear of strong contradiction: Excel can be unusually picky about qualifiers sometimes.

Could an unhandled error cause that by resetting the programme and destroying all variables, similar to discussions we have had about the benefits of removals?

 

[ebs17]

Excel can be unusually picky about qualifiers sometimes.

I haven't had a case for this yet. But you have to program cleanly and completely.

MS Word is a little more critical. I know the statement of a Word expert who instantiates a new, self-created Word instance in its own class in order to then dispose of this instance cleanly and completely.