if its open...close it
 

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

if its open...close it
 

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


"choice" wrote in message
...
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

if its open...close it
 

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

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


In article ,
choice wrote:

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

if its open...close it
 

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



JE McGimpsey wrote:

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

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


In article ,
choice wrote:

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

--

Dave Peterson

if its open...close it
 

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.


In article , Dave Peterson
wrote:

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

if its open...close it
 

"Dave Peterson" skrev i melding
...
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

if its open...close it
 

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.

Harald Staff wrote:

"Dave Peterson" skrev i melding
...
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

if its open...close it
 

Using the extension always works, regardless of the setting.

--
Regards,
Tom Ogilvy

"Harald Staff" wrote in message
...
"Dave Peterson" skrev i melding
...
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

if its open...close it
 

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 he

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

"Dave Peterson" skrev i melding
...
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.

if its open...close it
 

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.

--
Regards,
Tom Ogilvy

"Harald Staff" wrote in message
...
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 he

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

"Dave Peterson" skrev i melding
...
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.

if its open...close it
 

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

"Tom Ogilvy" skrev i melding
...
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.