Non-portable Excel VBA macro behavior -
i'm using excel 2010 , adding small vba macro spreadsheet. (the purpose of macro take data on active sheet , export csv file, that's tangential question.) macro determines output path file using thisworkbook.path. added custom button quick access toolbar activate macro.
after getting working algorithm in place in experimental spreadsheet (test.xlsm, stored in 1 path), made copy of spreadsheet in path , renamed _database.xlsm. opened _database.xlsm , ran macro. surprise, file written original path, not new one. , looking down @ windows toolbar, saw excel had opened original file on in original path well.
after lot of jiggering around code tweaks, checking properties , such found prevent opening copied spreadsheet, removing quick access toolbar button, re-adding it, , saving file. questions are:
why isn't macro "independently portable" along spreadsheet? i.e. why copy maintain kind of tie original sheet?
is there way can create or modify macro make portable in sense?
if want toolbar/button travel file (ie. not link original version) need add file itself, not qat. qat "knows" actual file linked button to.
it might better add macro personal macro workbook , have operate on activeworkbook.
however, if want distribute other users, can keep macro in "database" workbook , add custom ribbon part. see: http://www.rondebruin.nl/win/s2/win001.htm
Comments
Post a Comment