Close workbook

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Oldjay - 15 Apr 2008 13:44 GMT

I started a post but it became too complicated. So here is my problem
I have 3 or more open worksheets
One is named Tempdata.xls and is not active.
In the active workbook I have the following macro by Alex-
Sub CloseTempData()

> Dim wkb As Workbook
>
> For Each wkb In Workbooks
> If wkb.Name = "Tempdata.xls" Then
> wkb.Close
> End If
> Next wkb
>
> End Sub

This steps thru the if - then but doesn't see and close Tempdata.xls

Reply to this Message

Joel - 15 Apr 2008 14:16 GMT

Normally when I open more than one workbook I refer to the workbook that is
running the macro with Thisworkbook. After I open a second workbook I
automaticaly set a variable to the new book like the code below. OPening or
adding a workbook the active workbook swithes to the new (or open) workbook.

workbooks.open filename:=abc.xls
set newbk = activeworkbook

or
workbooks.add
set newbk = activeworkbook

then close these books with
newbk.close

You problem is a temporary workbook that is not savved doesn't have the xls
extension.

> I started a post but it became too complicated. So here is my problem
> I have 3 or more open worksheets
[quoted text clipped - 12 lines]
>
> This steps thru the if - then but doesn't see and close Tempdata.xls

Reply to this Message

Dave Peterson - 15 Apr 2008 14:33 GMT

Or even:

Dim newbk as workbook
set newbk = workbooks.open(filename:="abc.xls")
or
set newbk = workbooks.add

> Normally when I open more than one workbook I refer to the workbook that is
> running the macro with Thisworkbook. After I open a second workbook I
[quoted text clipped - 30 lines]
> >
> > This steps thru the if - then but doesn't see and close Tempdata.xls

Signature

Dave Peterson

Reply to this Message

Oldjay - 15 Apr 2008 16:08 GMT

I don't understand
All the workbooks might not have been opened at the same time.
The tempdata.xls might have been open the day before.
Where do I put this code?

> Or even:
>
[quoted text clipped - 37 lines]
> > >
> > > This steps thru the if - then but doesn't see and close Tempdata.xls

Reply to this Message

Dave Peterson - 15 Apr 2008 16:35 GMT

How do you know which files are open and which files should be closed?

If you only go by the name, then:
workbooks("somename.xls").close savechanges:=false 'true

If you're opening the files in your code, you can use the code to keep track of
what was opened and know which one you closed.

I don't understand how you know which one is which.

> I don't understand
> All the workbooks might not have been opened at the same time.
[quoted text clipped - 46 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message