Closing Excel from Access VBA

[Isaac]

xl.Quit is right, but your code has so many errors you will never even get to that point, which is why people are trying to help you with those errors too.

"With Xl" makes no sense, because .columns is not a property or method of the excel application! it should be with ws

to start with.

Also, in case the user has opened another workbook in that same excel instance, you may wish to do something like this:

if xl.workbooks.count=0 then
   xl.quit 'quit whole app
else
   wb.close true 'only close the wb you opened
end if

 

[Isaac]

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

No no no. it's not a hierarchy like that.

Once WS has been properly set, you just code "with ws".

and ebs is right, don't mix early and late binding. since you seem to be OK using early binding, i'd recommend that in your case as follows:

dim xl as excel.application
dim ws as excel.worksheet
dim wb as excel.workbook

set xl = new excel.application
set wb = xl.workbooks.open(path)
set ws = wb.sheets(1) ' (2, 3, etc)

with ws
..column
end with

 

[REZ]

Thansk to everyones suggestions, here is my code 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)).Select
With Selection
.Offset(0, 1).Select
End With
With Selection
.Value = "=C1 & ""-"" & A1"
.Copy
.Offset(0, 1).PasteSpecial xlPasteValues
End With
.columns("A:A").Delete
.columns("B:B").Delete
.columns("D").Delete
.columns("d").Select
With Selection
.Cut
End With
.columns("B:B").Select
With Selection
.Insert Shift:=xlToRight
End With
.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

ErrorHandler:
If Err.Number = 429 Then
Set xl = CreateObject("excel.application")
Resume Next
End If

This works because as someone said earlier, its using the instance of excel left hanging from the previous time.
I've tried getting Err numbers, but its always 0.
So why will Excel still not close?

P.S. sorry, I still haven't figured out how to tag it as code

 

[Isaac]

try wb.close True
instead of leaving it up to chance and the user interaction for the potential save prompt

 

[Gasman]

And that range(range works for you?

 

[theDBguy]

Thansk to everyones suggestions, here is my code now:

Just in case it helps, have you seen this?

MS Access - VBA - Export RecordSet to Excel | DEVelopers HUT

How to Export an MS Access RecordSet to Excel using Excel Automation via plain vanilla VBA including advance features and formatting.

www.devhut.net

 

[cheekybuddha]

@ebs17 gave you the answer earlier, but you missed it - you have an unqualified reference to an Excel object (Range):

' ...
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
'      ^            ^
'      |            |
'  Missing dots before Range
'...

Add the missing dots before 'Range' and it should correct the problem.

 

[REZ]

WB.close true does not make a difference
and GasMan that range thing does seem to work.

I can't see anything in that link that will help

cheeky, like this?
.Range(Range("E1"), .Range("E1").End(xlDown)).Select
It doesn't make any difference

 

[cheekybuddha]

.Range(Range("E1"), .Range("E1").End(xlDown)).Select
It doesn't make any difference

Sorry I missed at first that there were 2 instances - edited my post to reflect that.

It should be:

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

 

[Isaac]

Using With blocks saves you almost zero typing ("ws") and makes it more confusing, I'd avoid them at this stage if I were you.
You would be able to much more clearly see the problem if you weren't using a with block, I personally almost never use them, because IF your variables are declared correctly, they save almost zero typing and make little sense.

wb.close True may not solve the problem, but you still shouldn't just leave it to chance - either close the workbook and save it or don't save it, but don't just leave it out

 

[cheekybuddha]

You would be able to much more clearly see the problem if you weren't using a with block

Unlikely in this situation - copy the code from the Excel macro-recorded procedure, and paste without realising the nature of the issue.

 

[Isaac]

Unlikely in this situation - copy the code from the Excel macro-recorded procedure, and paste without realising the nature of the issue.

Still, if it were re-written without the with block, the problem would be much easier to see.

Of course if the re-writing isn't going to happen, due to pasting and hitting f5, then yeah, I agree

It's just advice to newcomers in general that using a with block is useless in most cases, and particularly troublesome for them. IMHO.

 

[Gasman]

WB.close true does not make a difference
and GasMan that range thing does seem to work.

I can't see anything in that link that will help

cheeky, like this?
.Range(Range("E1"), .Range("E1").End(xlDown)).Select
It doesn't make any difference

Just what are you trying to determine with that line?

 

[cheekybuddha]

Still, if it were re-written without the with block, the problem would be much easier to see.

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.

 

[The_Doc_Man]

No no no. it's not a hierarchy like that.

You are right, of course. That's what I get for answering too late at night. It CAN be a hierarchy but not like they were defined, since the definitions already implicitly contained a parental object reference. A hierarchy such as excel-instanct.workbook-name.worksheet-name is an example of a reference chain. That is the thing to which I referred.

The WB reference in REZ's code "contains" an "Excel-instance" object as part of the definition. The WS reference "contains" a workbook reference, so it has the WB as part of ITS definition. But that IS a transitive relationship, so WS also contains (through WB) the reference to the Excel-instance object. When something goes wrong with one of the prior objects in a chain of qualifiers (expressed or implied), the tail-end object will look OK but you will get error 1004 when you use it.

By the way, that happens a lot in Word as well.

@REZ - my earlier comments about turning off warnings was simply that somehow you weren't getting warnings I thought you should have gotten so I thought they must be somehow disabled. But Gasman reports that he can duplicate that behavior, so chalk up that comment to faulty expectations of code behavior.

 

[REZ]

Just what are you trying to determine with that line?

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

 

[REZ]

I tried using .selection instead of with blocks, but that gave me a compile error befor anything even tried to run

 

[cheekybuddha]

Avoid .Selection as much as possible - reference the range objects directly. (Though you might need it in this particular case).

Did you correct the other Range reference?

 

[REZ]

That .range line randomly throws error 462 sometimes.
Other times it works fine, i don't know what makes it error

Yes I corrected it

 

[cheekybuddha]

What happens if you try it like this:

  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").Cut
    .columns("B:B").Insert Shift:=xlToRight
    .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
  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
  End If