e-mailing excel files with macros - problem 2

[bikerted]

Could one of you good people advise on how one a can send an excel file with customized toolbars and related macros without getting rude and perplexing messages about the file already being open when it quite clearly is not - renaming the file brings out another similarly stupid message. Incidentally I sent with the main file the .xlb settings one so that the toolbars on my settings could be used by another. I'm sure I managed to do this successfully before, but can't remember how. I'm desparate to send this file, so please, please can someone assist?

Regards, Ted.

 

[VBAjedi]

Can you give us a bit more detail here? What version of Windows are you using, and what version of Excel? Are you sending the file from Excel's "File->Send To->Mail Recipient" menu, or are you creating a new email in Outlook and attaching the file from there?

VBAjedi

 

[bikerted]

I'm using Windows 2000 and Excel 2000. The e-mail is a new e-mail created in our network's Groupwise system - the problem file being attached. hope that helps.

Ted

 

[bikerted]

Sorry to reiterate my problem, but I'm desparately needing to e-mail an Excel file with customized toolbars(with several useful macros). Unfortunately my e-mail recipient tries to use the buttons,they get an erroneous message stating that the file is still open. I've even changed the file name but then a message comes up that the file could not be found. I'm working in Windows 2000 Professional and Excel 2000. The e-mail is being created in my organization's Groupwise system and the file is being sent as an attachment. Your assistance would be greatly appreciated.

Regards, Ted.

 

[PHV]

Have you tried to send your files in a zip archive, so the e-mail recipient don't tries to use the buttons ?

Hope This Help
PH.

 

[TheBitDoctor]

bikerted:

Each user has a file in their profile directory named "Excel.xlb" that holds their personal toolbar customizations. Excel opens this file automatically so your users can't open a second one without getting the file already open error you described.

To distribute a custom toolbar with a particular workbook you can attach that toolbar to the workbook itself. Search "Attach a custom toolbar to a workbook" in the Excel help system. NOTE: This only attaches the toolbar and it’s icons to the current workbook. It does not attach the VBA code behind the toolbar items. Also, if you look at the “Assign Macro” menu item you’ll see that it stores the complete path to the workbook that contains the code. So, even if you distribute a separate file with the code, if your users don’t place it within the same directory structure then Excel will give the “file not found” error when they click on any of your menu items that don’t use built in commands. The simplest way around this is to copy the VBA code to the same file as the menu. If you use the menu with multiple files then you’ll need to find a common location on each user’s machine to store the code. You might want to check out the “Application.LibraryPath” property in VBA. You should also check into attaching your menu and code to an XLA file and placing that in each users XLSTART directory. The catch here would be that your menu would be available to each user all the time, not just when they have one of your files open.

Ron

 

[bikerted]

Thank you both - particularly Ron for the detailed explanation. It looks like I've got some thinking to do abou how I use macro's/VBA code in my files. Maybe I'll have to learn how to program properly and create executable files for other users.

Regards, Ted.