if its open...close it

[Guest]

i have sheet workbook "register" and "commission"
i have a macro in "register" that closes commission.
however i need a if statement that say if "commission" isnt open...dont run

 

[Tom Ogilvy]

Dim bk as Workbook
On error resume next
set bk = workbooks("Commission.xls")
On error goto 0
if bk is nothing then exit sub

--
Regards,
Tom Ogilvy

i have sheet workbook "register" and "commission"
i have a macro in "register" that closes commission.
however i need a if statement that say if "commission" isnt open...dont

run

 

[JE McGimpsey]

If all you're doing is closing "commission":

On Error Resume Next
Workbooks("commission").Close SaveChanges:=False
On Error GoTo 0

 

[Dave Peterson]

If the workbook Commission had the .xls extension, I think I'd specify it:

On Error Resume Next
Workbooks("commission.xls").Close SaveChanges:=False

 

[JE McGimpsey]

Thanks, David. I occasionally forget which newsgroup I'm on - MacXL
files don't require extensions, of course, nor are they automatically
added by the OS.

 

[Harald Staff]

If the workbook Commission had the .xls extension, I think I'd specify it:

On Error Resume Next
Workbooks("commission.xls").Close SaveChanges:=False
On Error GoTo 0

That will err if the file extension is hidden. To be safe, use both:

On Error Resume Next
Workbooks("commission").Close SaveChanges:=False
Workbooks("commission.xls").Close SaveChanges:=False
On Error GoTo 0

Best wishes Harald

 

[Dave Peterson]

I just tried this in win98 with extensions hidden and visible.

It worked ok both ways. I've never seen the "commission.xls" version fail.

(I've never seen the "commission" version fail, either--but since I've been
reading the newsgroup, I always include the extension <vbg>.)

I have seen posts where just adding the extension makes a routine work.

 

[Tom Ogilvy]

Using the extension always works, regardless of the setting.

 

[Harald Staff]

Hi Dave

My Windows XP Home has not "hide known file extensions" enabled. But it has
all Excel versions since 5 on it, which may of course confuse it a little.
This is what happens here:

I open Excel2003. It generates an empty Book1. Caption says so.
Then I open Excel 2000. It generates an empty Book2 ! Now why is that ?
Then I open Excel XP. It generates an empty Book3. There's a pattern here...

I return to 2003. I save its file as "C:\Temp\Book1". Suddenly the caption
reads "Book1.xls". Then I run

Sub test()
MsgBox Workbooks("Book1.xls").Name
MsgBox Workbooks("Book1").Name
End Sub


and it errs on the non-.xls line. Now I go to Excel XP, Book3 is not saved
there, and run

Sub test()
MsgBox Workbooks("Book3.xls").Name
MsgBox Workbooks("Book3").Name
End Sub

It errs on the .xls line.
So everything behaves unexpected, but more important, it does not work both
ways. So my advice is still, "To be safe, use both".

And to be honest I really miss Win98 a lot, it is a no-nonsense OS. I use
WinXP only because I need an OS handles larger hard drives than 98 was
capable of and that runs Visual Basic.

Best wishes Harald

I just tried this in win98 with extensions hidden and visible.

It worked ok both ways. I've never seen the "commission.xls" version

fail.

 

[Tom Ogilvy]

I suspect it has more to do with using names like the default file names.
If you test it with different file names I believe you will find it always
works with the extension. Anyone using names like book1.xls uses that at
their own risk.

 

[Harald Staff]

Hi Tom

It was too obvious to be noticed: "Book1" is not "Book1.xls" until it's
actually saved to disc. So you're right, assuming that the workbook is saved
and not an unsaved temp workbook (I use those quite often). Which it's
obviously not in this case.

Best wishes Harald