Range problem

[Bismark]

Hi!

I've have recently faced a problem with RANGE.

I have a macro in mdb-file, which opens an existing excel workbook. Then it merges two cells and makes the workbook visible. However when i am closing opened workbook by myself, process "EXCEL.EXE" remains intact in the list of process and continuies its work. It causes future problems. The code is presented below:



Public Sub PROBLEM_WITH_RANGE()

Dim objXLS As Excel.Application
Dim currWB As Excel.Workbook
Dim pathdir as String

pathdir="C:\work.xls"

Set dbPay = Application.CurrentDb
Set objXLS = New Excel.Application
Set currWB = objXLS.Workbooks.Open(pathdir, 0)

...........................

ActiveSheet.Range(ActiveSheet.Cells(2, 5), ActiveSheet.Cells(2, 6)).Select
Selection.Merge

...........................

currWB.Save
objXLS.Visible = True

Set currWB = Nothing
Set objXLS = Nothing

End Sub


You see, it is no good if you substitute merge-method by justify-methor or any other. But if you remove the code with "RANGE" the workbook will fully be closed by my mouse click. Moreover I know that all variables should be disosed to close "EXCEL.EXE" process, thus the question in my opinion shoud be:

HOW CAN I DISPOSE "RANGE"?

I'll be very glad if you help me to find a key for this problem door.

[Norie]

Try this.

Public Sub PROBLEM_WITH_RANGE()

Dim objXLS As Excel.Application
Dim currWB As Excel.Workbook
Dim pathdir As String

pathdir = "C:\work.xls"

Set objXLS = New Excel.Application
Set currWB = objXLS.Workbooks.Open(pathdir, 0)

With currWB.ActiveSheet
.Range(.Cells(2, 5), .Cells(2, 6)).Merge
End With

currWB.Save
objXLS.Visible = True

Set currWB = Nothing
Set objXLS = Nothing

End Sub

[malik641]

I think Norie's code will work. And I wanted to mention that you didn't close a connection / Set = Nothing the following:

Set dbPay = Application.CurrentDb

It's kinda just hanging there. I've never used "CurrentDb" before but I think you need to close the connection of dbPay before exiting the procedure.

...which is mostly why I think Norie's code will work :yes

[Bismark]

Thank you for your help.

The case


With currWB.ActiveSheet
.Range(.Cells(2, 5), .Cells(2, 6)).Merge
End With

has helped. I wonder, why i haven't checked out "With" or the full path. It seemed to me that I had checked full path and It hadn't helped. However thanks a lot. Spesial Thanks to Norie.
Some times a small fault makes huge code useless. It happened in my case.

[Norie]

malik

The code I posted wasn't really anything to do with closing connections or setting variables to nothing.:)

The problem as far as I could see was that somethings eg. Activesheet didn't have references to their parent object. eg. currWB

Apparently that causes problems.

There's a good write up on the issue on Tushar Mehta's website.

I'll see if I can find a link, but you should be able to find by googling.

[Zack Barresse]

Apparently that causes problems.

Of course it causes problems! It's like giving the code a vehicle to drive but telling it to drive blind. It will find the first thing it comes across - no control whatsoever. :)

[malik641]

malik

The code I posted wasn't really anything to do with closing connections or setting variables to nothing.:)

The problem as far as I could see was that somethings eg. Activesheet didn't have references to their parent object. eg. currWB

Apparently that causes problems.

There's a good write up on the issue on Tushar Mehta's website.

I'll see if I can find a link, but you should be able to find by googling.
Whoooops :doh:Read too quickly...my bad.

And I'll check out the site, thanks :)