Closing a workbook from a macro doesn't close the workbook

[Guest]

This is in Excel 2003.

From one workbook I want to open another workbook, copy some data out and
then close the other workbook.

The code is like this -

set wkb = workbooks("C:\BlahBlah\AnotherWorkbook,xls").open

<do some things that all work fine>

wkb.close

<go off and do something different>

When run in the VBA code window this works wonderfully well. Exactly as one
would expect.

When this sub is assigned to a ctrl key and run from the worksheet pane all
works as expected with the exception that the external workbook is left open.
I'm obviously missing something but I haven't a clue what.
Can anyone oblige?

Cheers,

Dave

 

[Guest]

Dave, the only thing I can see is that you have Set the Wkb as an Object
variable with the open command incorporated. Maybe if you Set the variable
without the .Open command, then do Wkb.Open separately, it might close. I'm
just guessing since there doesn't seem to be anything wrong otherwise.

 

[Guest]

Actually I can now answer my own question.

After I posted my original question here I went browsing for a couple of
hours. A vaguely related issue on opening workbooks gave me a hint.

The problem is not in the code at all. It lies with the definition of the
key to which the macro is attached : using a shifted value causes exactly the
behaviour I observed. I was using ctrl-shft-q and changing this to ctrl-q
fixed the problem. Simple when you know it.

This is apparently a bug in Excel.


Cheers,

Dave