Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    create workbook wo macros (2000+)

    Hi All,

    I have a workbook that I need to update bi-weekly based on status meetings. Actually, my workbook has no formulas in it; myh predecessor chose to track status info in Excel.

    I've created a few macros to make my update job a little easier (may post something about that later).

    I then send the workbook out to a bunch of people. Of course, they get the workbook with the macro and have to deal with the "open macro" msg. I have no idea what their macro security settings would be. However, they have no need for any of the macros - in effect, they're using this in read-only mode.

    It seems to me that it shouldn't be too hard to create a macro (in my workbook) that creates a new workbook that is a copy of my version WITHOUT the macros. But there are a few other things I'm not sure of so thought I'd see if there was a general solution (searched the archives and didn't see anything obvious).

    Easiest way to state the rqmt is to create a copy of the workbook w/o the macros. This is to include:
    - all page setup options (like use rows x-y for headers, orientation, etc) for all sheets
    - freeze pane settings
    - comments
    - headers/footers
    - etc

    The name of the new workbook should be the same as the existing workbook with the word "public" (or something else I'll dream of) appended to the end of the name. Folder should be the same as the folder for my "private" version.

    It has 4 or 5 sheets but could have more. Would a straight copy of each sheet keep the above settings?

    TIA

    Fred

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create workbook wo macros (2000+)

    Fred
    I'd consider looking at the problkem a different way. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If the Macros are "yours" then it would seem logical to question why they are stored in the workbook in the first place. <img src=/S/duck.gif border=0 alt=duck width=23 height=23>
    Clearly it may be that some are based around event procedures and have to be there - but most general purpose macros could be reasonably stored in Personal.xls or some other place that belongs solely to you.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create workbook wo macros (2000+)

    This is a relatively easy task if there are no macros behind sheets:

    Thisworkbook.Sheets.Copy
    Activeworkbook.SaveAs "DRIVE:PathToCopyWithoutMacrosWorkbookWithoutMacro s.xls"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: create workbook wo macros (2000+)

    Andrew,

    I like to put only those macros that are very general and can be re-used in many workbooks into Personal.xls. These macros would only be used for this workbook-can't see application beyond this. I guess I could go that way if necessary.

    Fred

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: create workbook wo macros (2000+)

    Jan Karel,

    Not bad <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    It was late at night so I didn't want to experiment. I now just tried recording a macro that included selecting all the sheets and doing a copy to a new workbook. I got the following code:
    Sub Macro2()
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Sheets("Sheet3").Activate
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
    End Sub

    Of course, this doesn't save to a new workbook file, just sets up another Bookx.xls window. I like your code better.

    But.

    I may have worksheet macros. In another thread that you and Steve responded to my question, I am considering adding the worksheet save date to the footer. This was done by a Workbook_SheetChange macro. I don't know if that would count.

    With respect to that: suppose I do use your suggestion (or Steve's) to modify the header/footer for the sheet change and now have a macro that appends the date to the sheet's footer (left, middle, right is irrelevant for now). When I create the macro-less workbook (assuming it can be done now that a Workbook macro is involved), what would be in the new workbook if I look at the custom footers "collection"? Would I see for each sheet a footer with the header as modified by the code in my private workbook? I don't think this matters if the other people don't see such a footer but not sure yet; I certainly want the footer to print for them as it does for me.

    I hope I'm not changing the rqmts too much (not at all?). I do want the footers to copy over to the "public" workbook. If the above creates a problem, I'll just go with the custom footers without the worksheet change.

    Also, just curious - is there a "footer collection" given that a workbook could have several custom footers (ditto headers).

    Thanks.

    Fred

    PS: congrats on Name Manager making it to the top. Very useful utility.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create workbook wo macros (2000+)

    The Workbook_SheetChange event lives in the Thisworkbook Class module, so you won't be copying that one over.

    I don't know what the headers/footers do when you change them (whether or not that is added to the custom list), best to just try this and see what happens.

    I wouldn't know about a footer collection, have you tried the object browser?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: create workbook wo macros (2000+)

    Jan Karel,

    1. The Workbook_SheetChange event lives in the Thisworkbook Class module, so you won't be copying that one over.

    great!

    2. I don't know what the headers/footers do when you change them (whether or not that is added to the custom list), best to just try this and see what happens.
    DId a little experiment.
    -opened new workbook and added 1 to A1. Looked at headers and footers. Seems like they are 16 "standard" items (book name, sheet name, page# and combinations thereof) that exist for all workbooks.
    - added your code to the Workbook module and changed A1. New header for "Sheet Modified" added to Page Setup headers list at bottom of drop down.
    - closed workbook
    - opened another new workbook and added 1 to A1. List of headers and footers did NOT include the "Sheet Modified" header but did have all the "standard" ones (with bookname changed appropriately)
    - added your code and changed A1 again. New header added again.

    3. I wouldn't know about a footer collection, have you tried the object browser?
    I usually get a headache and nothing useful when looking at the obj browser. Tried it again and no better success. There is a pagesetup class and a centerheader object. But I'm no closer to knowing if there's a collection. Looking at Excel's page setup dialog, I see the 16 headers per above. One would think these have to be somewhere - maybe in the registry. I tried modifying one of the existing ones and it just added another custom header to the list, not modifying the one I started with (kind of like a custom format). So another question would be how to access even the "user defined" headers? Something like sh.pagesetup.centerheader(2) does not work.

    Fred

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create workbook wo macros (2000+)

    I wouldn't know, frankly, I too have never explored that header stuff yet.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: create workbook wo macros (2000+)

    Andrew,

    I forgot to ask - you mention

    >but most general purpose macros could be reasonably stored in Personal.xls or some other place that belongs solely to you.

    other than Personal.xls and the workbook, what other places that belong solely to me might I put the macros??

    As stated in earlier response, I don't want to use Personal.xls. But I created a menu for the macros and assigned a macro to each of five menu items. Even tho the assignment dialog only shows, for example, "Add_Bottom_Border", when I next look at the assigned macro in the assign dialog, it has the full path name of the workbook that I'm using for my work and the macros. It goes w/o saying that changing the filename or moving it creates problems.

    I'm now considering using a Workbook open to create the menu, add the menu items, and assign the macros. I've done this before for creating a toolbar, adding buttons and assigning macros to the buttons. I guess they're similar. Not sure what else to do at this point.

    thks

    fred

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: create workbook wo macros (2000+)

    Jan Karel,

    bad news. I tried your macro on my real worksheet. Seemed to go ok.

    Then I tried it the manual way (selected sheets, right click, select the "move or copy" and do the copy steps). I get a msg saying cells with >255 chars won't be copied and I need to do a cell copy. Checked back on your macro (and mine) and they do the same thing except WITHOUT any warning.

    This is being done in Excel 2000 so don't know if XP+ doesn't have this limit.

    Fred

  11. #11
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create workbook wo macros (2000+)

    Fred
    I haven't explored Excel in depth - but thought that Addins were another option.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create workbook wo macros (2000+)

    Oops, indeed. This problem is around for all Excel versions.

    The only workaround I know is to first copy the sheets as showed earlier, then copy the cells again.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: create workbook wo macros (2000+)

    Jan Karel,

    So it sounds like you do the copy first, then do a cell by cell (not that many-maybe a few hundred at most per sheet), sheet by sheet (about 4-5) search in the original file for cells with >255 characters? Ugh.

    Hmmm - change in direction? Copy the file (I need to keep the master with the macros) and delete the module in the copy for sending to others. Or time to resort to personal.xls, as Andrew originally suggested.

    Fred

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: create workbook wo macros (2000+)

    You don't have to do a cell by cell copy. You can select the usedrange and then copy that. The problem is copying the sheet, not copying the range of cells. You don't have to search the length either.

    Steve

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create workbook wo macros (2000+)

    Removing the macro does require allowing access to Visual Basic projects (macro security).

    The option to put all code in a separate workbook is very likely the simplest and best solution. Whether you need to make that an addin remains to be seen, that depends on whether the macros need to be available all the time.

    What I often do is have a button on a toolbar that points to a workbook with its macro. Then when I need the macro, I click the button and presto, the workbook with the macro will be opened. Often I do that with addins too, so I don't have to wait for them to load, but can quickly access them by hitting the button.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •