Hi there

I'm having some trouble with a bit of VBA code, and its confusing me.

The code is (something like)

sThisBook = ActiveWorkbook.Name

Set wkbSource = Workbooks.Open(sPath)

If Len(sThisBook) 0 Then
'... this makes the original workbook active
Workbooks(sThisBook).Activate
End If

(I've precied it slightly)

What I find is that:

a) If I am running this code in the context of a new blank "Book1.xls" then
after the other workbook has been opened only that book is open.

i.e.

workbooks.count is 1 and workbooks(1).name is the short version of the
variable sPath

Hence

Workbooks(sThisBook).Activate

fails with an error. Since its doing

Workbooks("Book1.xls").Activate

and that is no longer open.

However,

b) If I close book1 and click New so that I have "Book2.xls" then after the
..Open statement I have 2 workbooks

and the .Activate does work since "Book2.xls" IS still open.

Is Excel implicitly closing Book1 or am I missing something?

Perplexed in England

Rob

I've just run the below and it works fine! The code is written into a
book called xxxx.xls and once the code has run book xxxx.xls is
activated with PORTFOLIO being open aswell.

J

Option Explicit

Sub xxxx()

Dim sThisBook
Dim wkbSource

sThisBook = ActiveWorkbook.Name
Set wkbSource = Workbooks.Open("H:\Miscellaneous\PORTFOLIO.xls")

If Len(sThisBook) 0 Then
'... this makes the original workbook active
Workbooks(sThisBook).Activate
End If

End Sub

Jase

Drat. Thanks for doing that, but now I'm even more confused.

Rob

"jase" wrote in message
ups.com...
I've just run the below and it works fine! The code is written into a
book called xxxx.xls and once the code has run book xxxx.xls is
activated with PORTFOLIO being open aswell.

J

Option Explicit

Sub xxxx()

Dim sThisBook
Dim wkbSource

sThisBook = ActiveWorkbook.Name
Set wkbSource = Workbooks.Open("H:\Miscellaneous\PORTFOLIO.xls")

If Len(sThisBook) 0 Then
'... this makes the original workbook active
Workbooks(sThisBook).Activate
End If

End Sub

the name of the default workbook is not book1.xls or book2.xls. Simply
book1 or book2 without any extension.

Generally if you open a workbook with only the default workbook open (in a
pristine condition), then the default workbook is automatically closed. You
might write something to cell A1 or set Saved to False

sThisBook = ActiveWorkbook.Name
Activeworbook.Saved = False
Set wkbSource = Workbooks.Open(sPath)
workbooks(sThisBook).Activate
--
Regards,
Tom Ogilvy



"Rob Kings" wrote in message
...
Hi there

I'm having some trouble with a bit of VBA code, and its confusing me.

The code is (something like)

sThisBook = ActiveWorkbook.Name

Set wkbSource = Workbooks.Open(sPath)

If Len(sThisBook) 0 Then
'... this makes the original workbook active
Workbooks(sThisBook).Activate
End If

(I've precied it slightly)

What I find is that:

a) If I am running this code in the context of a new blank "Book1.xls"
then
after the other workbook has been opened only that book is open.

i.e.

workbooks.count is 1 and workbooks(1).name is the short version of the
variable sPath

Hence

Workbooks(sThisBook).Activate

fails with an error. Since its doing

Workbooks("Book1.xls").Activate

and that is no longer open.

However,

b) If I close book1 and click New so that I have "Book2.xls" then after
the
.Open statement I have 2 workbooks

and the .Activate does work since "Book2.xls" IS still open.

Is Excel implicitly closing Book1 or am I missing something?

Perplexed in England

Rob

Tom

Once again you have supplied the answer. Allow yourself a pat on the back.

Many many thanks

Rob
"Tom Ogilvy" wrote in message
...
the name of the default workbook is not book1.xls or book2.xls. Simply
book1 or book2 without any extension.

Generally if you open a workbook with only the default workbook open (in a
pristine condition), then the default workbook is automatically closed.
You
might write something to cell A1 or set Saved to False

sThisBook = ActiveWorkbook.Name
Activeworbook.Saved = False
Set wkbSource = Workbooks.Open(sPath)
workbooks(sThisBook).Activate
--
Regards,
Tom Ogilvy



"Rob Kings" wrote in message
...
Hi there

I'm having some trouble with a bit of VBA code, and its confusing me.

The code is (something like)

sThisBook = ActiveWorkbook.Name

Set wkbSource = Workbooks.Open(sPath)

If Len(sThisBook) 0 Then
'... this makes the original workbook active
Workbooks(sThisBook).Activate
End If

(I've precied it slightly)

What I find is that:

a) If I am running this code in the context of a new blank "Book1.xls"
then
after the other workbook has been opened only that book is open.

i.e.

workbooks.count is 1 and workbooks(1).name is the short version of the
variable sPath

Hence

Workbooks(sThisBook).Activate

fails with an error. Since its doing

Workbooks("Book1.xls").Activate

and that is no longer open.

However,

b) If I close book1 and click New so that I have "Book2.xls" then after
the
.Open statement I have 2 workbooks

and the .Activate does work since "Book2.xls" IS still open.

Is Excel implicitly closing Book1 or am I missing something?

Perplexed in England

Rob