Portable Spreadsheets
Posted by Cort Chilldon on November 16, 2001 6:45 AM
Help (I apologize for the longwinded explanation, but it confounds me...)!!
I have a spreadsheet that has a custom toolbar attached to it. Different buttons on the toolbar have different macros assigned to them. I would like to be able to move this .xls file so that the macros assigned to buttons on the toolbar will still function. Currently, when I move the file, the buttons are trying to open the macros where the original file used to be, and I want it to update these macro references whenever I move this file. Basically, I would really really like to create a "portable" spreadsheet with attached toolbars that function.
Posted by Gregc on November 16, 2001 7:49 AM
XLA
Try saving the the workbook as an Add in. Instead of .XLS it is .XLA and your macro and buttons will be available to every spreadsheet you open on a particular computer. I haven't done it much, but I believe that is what you are looking for. Excel help should have some pretty good information on it.
Posted by Jack on November 16, 2001 7:54 AM
Your question hides the question but load macros into personal.xls in VBA Editor then yoy carry them about in every xls file you can assign fom there
Posted by Rob Jackson on November 16, 2001 8:05 AM
Ok, here's a couple of ideas, but it depends on exactly what your doing as to which, if any will apply.
1) Do not change the filename when moving the file, this way, once the file is opened the references will still work.
2) When you attatch a tolbar to a macro, it 'connects' to it in such a way that if you move that spreadsheet by 'Save As' in a different location, it will automatically look for the spreadsheet in that new location. So assuming what your doing is using one computer and moving the file around different drives, using save as will keep you on track.
3) It would appear your main problem is opening the file, it is looking in the wrong place. Therefore I assume you have a 'workbook.open' line somewhere in your macro's. This is probably not necessary as when you attatch a macro to a toolbar, clicking the toolbar will automatically open the 'connected' workbook to run the macro. This in conjunction with point 2 should keep you on track.
4) The final option although this can cause problems is to remove all specific workbook and sheet names so the macro rns on the current sheet. Although this will then allow the functions to be run on any sheet not necessarilly one that requires it.
I hope this helps and I haven't totally missed the point.
Posted by Cort Chilldon on November 16, 2001 8:36 AM
:The toolbar is attached to the spreadsheet,not the macro. The problem is, each button on the toolbar might make a reference to, for example, "'ADI.XLS'!PrintJuly", then when I move the file, without changing the name, the reference contained on the button on the custon button bar would change to, "G:\Excel\Files\'ADI'.xls!PrintJuly"
The problem is, the spreadsheet is no longer there, it might be on C:\ or another persons computer. I need to be able to save this spreadsheet on a disk, and pull it up on someone eles's computer who has Excel 2000, and have all the macros assigned to buttons on the custon toolbar know that I've moved the file to a new place. Thanks for the input so far, but nothing seems to work. The only thing that works is if I decide where I want to move the file, make a new folder, open the spreadsheet in its original location, and save workbook to this new folder and then save changes to the Excel file, but then the original file doesn't work. HELP!!!
