workbooks.open problem

[Rob Kings]

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

 

[jase]

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

 

[Rob Kings]

Jase

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

Rob

 

[Tom Ogilvy]

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

 

[Rob Kings]

Tom

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

Many many thanks

Rob