repost: error 'document already opened'

  • Thread starter Thread starter jeffP
  • Start date Start date
J

jeffP

Trying to run a macro from a custom toolbar I get the error
"A document with the name "myworkbook" is already open. You cannot open two
documents .....blah,blah,blah.
This error is happening w/ my custom toolbar. I have a few macros that run
from Tool>Macro ,a custom menu bar item and a custom toolbar just fine.
But, if I save the workbook w/ SaveAs to another directory (same filename)
for backup I cause a problem. When I re-open the original workbook and try
to run macros from the custom toolbar I get the above error. It's because
the path has been changed and points to the directory where the backup was
put. This doesn't affect the macro, nor running the macros from the menu bar
.. Just the custom toolbar. I have the following code for opening the toolbar
w/ Workbook_Open event

With Application
.CommandBars("BudgetBar").Visible = True
additional code
end with

and this with Workbook_BeforeClose
Application.CommandBars("BudgetBar").Visible = False

I'm thinking it's because the object is Application and should be something
smaller (workbook?) but I"m obviously confused and any and all help is
always appreciated.
 
When you saved the workbook that contained the macros for the toolbar to a new
location, then the buttons in pointed at the new file location.

So now you have two identical looking buttons (created from two different
workbooks) that point at different workbooks.

You could either reassign the macros for each button in the new toolbar or maybe
a little more work to start (but easier later on), would be to create the
toolbar on the fly--when you open the workbook with the macros.

John Walkenbach has a nice workbook that actually creates another option on the
worksheet menubar.
http://j-walk.com/ss/excel/tips/tip53.htm

It's called menumaker. It's really simple to use and looks very
nice/professional.

If I want a toolbar, I use this as my starting point when I'm building toolbars:
http://groups.google.com/[email protected]
 
Hi Dave,
I use John's MenuMaker and it's terrific! I was supplimenting w/ a custom
toolbar (same macros mostly) when I ran into this problem. I'm still
confused. I save the original file /path (C:\2005\budget.xls) then SaveAs
C:\2005\backup\budget.xls. If I open either file , macros run from the
custom toolbar BOTH point to the copy in the backup directory. It seems
that the file in the backup directory would point there, but the original
would point to C:\2005\budget.xls ? No?
I'll try the other link you provided and go from there.
Thanks,
jeffP
(e-mail address removed)
Dave Peterson said:
When you saved the workbook that contained the macros for the toolbar to a new
location, then the buttons in pointed at the new file location.

So now you have two identical looking buttons (created from two different
workbooks) that point at different workbooks.

You could either reassign the macros for each button in the new toolbar or maybe
a little more work to start (but easier later on), would be to create the
toolbar on the fly--when you open the workbook with the macros.

John Walkenbach has a nice workbook that actually creates another option on the
worksheet menubar.
http://j-walk.com/ss/excel/tips/tip53.htm

It's called menumaker. It's really simple to use and looks very
nice/professional.

If I want a toolbar, I use this as my starting point when I'm building toolbars:
http://groups.google.com/[email protected]
Click to expand...
 
Just a guess, but I wonder what the buttons pointed to before you saved it in
either spot.

(I'm guessing faulty testing <bg>.)
Hi Dave,
I use John's MenuMaker and it's terrific! I was supplimenting w/ a custom
toolbar (same macros mostly) when I ran into this problem. I'm still
confused. I save the original file /path (C:\2005\budget.xls) then SaveAs
C:\2005\backup\budget.xls. If I open either file , macros run from the
custom toolbar BOTH point to the copy in the backup directory. It seems
that the file in the backup directory would point there, but the original
would point to C:\2005\budget.xls ? No?
I'll try the other link you provided and go from there.
Thanks,
jeffP
(e-mail address removed)
Click to expand...
 
faulty testing....me.....?..it couldn't be <bg> but the answer to your
guess: They pointed to the macro without a path, but when I first
encountered this problem I set the macro to original file name
(budget.xls\mymacro) but did not include any path. I'm off try it

--
jeffP
(e-mail address removed)

Dave Peterson said:
Just a guess, but I wonder what the buttons pointed to before you saved it in
either spot.

(I'm guessing faulty testing <bg>.)
Click to expand...
 
Dave,
I'm getting more confused each time. The macro on the toolbar of the
original file picks up the path of the backup directory when I SaveAs
(c:\2005\backup\budget.xls\mymacro) but the file in the backup directory is
just the filename&macro (budget.xls\mymacro). Furthermore, when I SaveAs and
rename the file in the backup directory (c:\2005\budgettest.xls) the macro
on the toolbar points to the original file AND opens the original file to
run the macro from the toolbar (it avoids the 'document already opened '
problem because of the different name)!
I'm lost here. I'm working on using your code to build and delete a custom
toolbar everytime the file opens or closes(as does John's Menumaker)


--
jeffP
(e-mail address removed)


Dave Peterson said:
Just a guess, but I wonder what the buttons pointed to before you saved it in
either spot.

(I'm guessing faulty testing <bg>.)
Click to expand...
 
In my light testing, if I did my work within xl, then xl is smart enough to
"reassign" the macros to the new location.

But if I did my work outside of excel (in windows explorer), then xl won't even
know about the change and if I moved (or renamed) the file, then excel didn't
know about it and caused trouble.

I'm gonna guess that some of the work you did outside of excel broke the
assignment the next time you opened excel.

(I think you've found the reason that building the toolbar/menu options on the
fly is much safer.)
Dave,
I'm getting more confused each time. The macro on the toolbar of the
original file picks up the path of the backup directory when I SaveAs
(c:\2005\backup\budget.xls\mymacro) but the file in the backup directory is
just the filename&macro (budget.xls\mymacro). Furthermore, when I SaveAs and
rename the file in the backup directory (c:\2005\budgettest.xls) the macro
on the toolbar points to the original file AND opens the original file to
run the macro from the toolbar (it avoids the 'document already opened '
problem because of the different name)!
I'm lost here. I'm working on using your code to build and delete a custom
toolbar everytime the file opens or closes(as does John's Menumaker)
Click to expand...
 
Dave,
I'm using your toolbar maker and I'm now a confirmed 'on the fly' toobar and
menu maker!
Thanks for all your help.
 
Back
Top