Force excel to open a new workbook as book1 not as book 2,3

[Dinictus]

Hi,

This a a nice one. I have recorded a series of macro's and put them together to make a nice reporting tool. These macro get data from other places, rename sheets, etc.

Here's the problem: The whole things only works when excel is shut down and started up again. This is because the macro's work with specific workbook numbers. (so if I open a new workbook )(book 1) and close it again, the new workbook is called book2. The macro start with creating a new workbook which must be called workbook1.


This means that I must restart excel to reset the "new workbook" number to 1 again.

Is there any way in which I can force excel to always name a new wordbook
workbook1 (provides a workbook1 is nog already open)

I hope I made this a bit clear. It's seems quite difficult but i think there is a easy solution.

Thnks!

 

[pcc]

Re: Force excel to open a new workbook as book1 not as book

Why bother with what it's called. If you create the workbook, then use

newwb=ActiveWorkbook.Name

It will always work whatever the name

 

[Dinictus]

Re: Force excel to open a new workbook as book1 not as book

In this context:?


Workbooks.Add
newwb = ActiveWorkbook.Name

?

It doesn't seem to work. It doesn't rename the workbook. (can't set read only property)

The moment you add a workbook it gives the name and it always is workbook 1 or more.

Thnks.

 

[pcc]

Re: Force excel to open a new workbook as book1 not as book

It's not supposed to. It assigns newwb as a VAriable that is the name of the new workbook. Then, if you want to activate it, you use
Windows(newwb).activate instead of
Windows("Sheet1.XLS").ACTIVATE

 

[Dinictus]

Re: Force excel to open a new workbook as book1 not as book

I'm sorry but i still don't understand.

Here's a part of the code where it obviously goes wrong:

Workbooks.Add
Windows("blabla").Activate
Cells.Select
Selection.Copy
Windows("book1").Activate
ActiveSheet.Paste


Now if i already have a wordkbook open, the new workbook which is openen with this code will automatically be named workbook2, hence Windows("book1").Activate will result in a error.

 

[pcc]

Re: Force excel to open a new workbook as book1 not as book

Workbooks.Add 
newwb=activeworkbook.name ' the name of the new one
thisworkbook.activate ' this workbook is the one that contains the code
Cells.Select 
Selection.Copy 
Windows(newwb).Activate 
ActiveSheet.Paste