excel - copy each sheet to separate cls files with path given -

i copy/save/export each of sheets separate vba file (which import later). intend because, have workbook beforeclose event delete worksheets except sheet1. before deleting want other sheets copied specified path. ofcourse same imported via worksheet click or box click event or whatever later.

i tried doesnt work!

activeworkbook.worksheets("abc").export filename:="c:\users\xyz\desktop\vbe\a"abc".cls 

can me this? rgds

what need vbe.

first, add reference microsoft visual basic applications extensbility.

then, add enable trust access vba project object model, in macro security settings.

and now, following code work:

sub save_cls()     dim c vbcomponent     f = freefile     each c in thisworkbook.vbproject.vbcomponents         if c.type = vbext_ct_classmodule             open c.name & ".cls" output f             print #f, c.codemodule.lines(1, c.codemodule.countoflines)             close f         end if     next end sub 

edit: generic function - put in vba module, available whole workbook , not attached single worksheet:

public sub savesheetcode(byval sheet worksheet, byval filename string)     dim c vbcomponent, f integer     set c = sheet.parent.vbproject.vbcomponents(sheet.codename)     f = freefile     open filename output f     print #f, c.codemodule.lines(1, c.codemodule.countoflines)     close f end sub 

usage:

 ... anywhere in book code  savesheetcode worksheets("sheet1"), "c:\vba\sheet1.cls"