new books

[Guest]

I was wondering if there is any way to reset the new book count in excel with
a macro. Currently I have a macro that creates a new book, adds data to it,
and then saves it with a new name. What I have noticed is that if I run the
macro a few times the new book comes up as book1, book2, book3. Is there any
way a macro could reset the counter so that every time Book1 comes up.

 

[Guest]

Not that I am aware of.

 

[NickHK]

If it's really that important to you, the only way I know is to close Excel
and reopen it.
Not really worth it.
You could make a template called "Book1.xlt" and open that each time if you
really want to keep "Book1.xls" everytime.

NickHK

 

[Guest]

Thanks for the response. There has to be a way rather than to close right
out of excel and reopen I will keep looking thank you.
--
Thanks!
Shane W

If it's really that important to you, the only way I know is to close Excel
and reopen it.
Not really worth it.
You could make a template called "Book1.xlt" and open that each time if you
really want to keep "Book1.xls" everytime.

NickHK

 

[NickHK]

That's the way Excel works.
If you create a new workbooks, it increments the number each time, only
resetting when Excel closes.
Is it really such a big deal if you are only .SaveAs anyway ?

NickHK

Thanks for the response. There has to be a way rather than to close right
out of excel and reopen I will keep looking thank you.

 

[Guest]

I have a change link macro. If the current link is to book1 (unknown
destination) then the macro opens up a new book (book1) in order to execute
properly. This is the only way to make it work. I could go into the change
link in the tool bars and do it that way successfully every time but I have
many individuals using my spreadsheets that have limited knowledge in excel.
As it stands right now one can only change link once with my macro. If they
open another file and try to change the link book2 comes up and the change
link fails. That's why I would like to figure out a way to reset the counter.

 

[NickHK]

Why not change it open a template called Book1 (or whatever) instead of
Workbooks.Add.
Then oyu always have the same.
The "template" doesn't have to contain anything special, just the name.

NickHK

 

[Guest]

I have tried that but you can only do the action once unless you close excel
completely the counter goes to book2 if you do the action again. Hence the
reason I was wondering how I can reset the counter. Currently I have set my
macro up to only do the action once so that an error does not occur. That
will have to do until I figure out another way.

 

[NickHK]

If all your code only works on the basis that Excel new books are always
called 'Book1", then there is something wrong with your logic and/or code.
It really should not any difference what the name is.

You can ActiveWorkbook, ThisWorkbook or Set your own object reference to the
new book, so this should not be a concern.

NickHK

 

[Guest]

Do me a Favor NickHK. Don't respond to any of my posts. It's apparent that
you have no idea what I am asking and your responses are not helpful.

 

[NickHK]

Shane,
I'll gladly not waste any more of more on my time on your inane drivel.

NickHk