Saving either XLS or PPT WITHOUT the associated MACRO code


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: Saving either XLS or PPT WITHOUT the associated MACRO code

  1. #1
    Arthur Wood Guest

    Saving either XLS or PPT WITHOUT the associated MACRO code


    I have a rather pressing neeed. I have developed a rather significat integrated
    application, used at very high levels in the Pentagon (US DOD), which involves
    a Access Database for data management, and both Excel and PowerPoint as output
    media - "Reports" are sent to senior staff in the form of Excel Spreadsheets,
    and are also posted to a Secure Internet site- the staff involved are very
    comfortable with Excel as a report Medium, and creating an Access report
    directly would NOT be an acceptable alternative to this part of the application.
    In addition, briefings to Senior DOD staff (up to and including the Secretary
    of Defense) are composed of sets of PowerPoint Slides, driven from the Database.
    I have created macros, in both Excel and PowerPoint to extract the relevant
    data from the database, and to create the necessary Workbooks, and/or PowerPoint
    Presntations (the PPTs are comosed of sets of 6 slides for each record in
    the database). The propblem that I am encountering is that when the WorkBook,
    or Presentation is Saved (by the exectuing code), the Macro code Module (and
    any associated User-Forms) are also saved. This causes the "file contains
    MACROS, enable/disable" message box to be displayed when the XLS or PPT files
    is subsequently opened(for obvious reasons,this is not acceptable, and setting
    the Security level lower IS NOT an option). Does anyone know of any way
    to have the code (VBA) ONLY save either the WorkBook (Without the assciated
    modules/User Forms), or the Slides (again without the associated Modules/User
    forms).

    What I have had to do, for the present, is to open each of the resulting
    files, either in Excel or PowerPoint, as appropriate, and manually delete
    the Modules/User forms. In the case of the WorkBook, this is not a big issue
    (only 1 workbook is involved), but in the case of the PowerPower Briefing
    sets, there can be as many as 65 separate sets of Briefing slides(of 6 slides
    each) created, and this manual appraoch can take close to an hour to complete.
    Needless to say, if there is any way to automate that process, it would
    be greatly appreciated.

    Arthur Wood



  2. #2
    Jim Cone Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code


    Arthur,

    Take a look at the Chip Pearson web site for "Programming To The Visual Basic
    Editor" (in Excel). I would assume that most of his code can be modified
    and used in PPT.

    http://www.cpearson.com/excel/vbe.htm

    HTH

    Jim Cone
    San Jose, CA

    ****************************************
    "Arthur Wood" <wooda@nospam.com> wrote:
    >
    >I have a rather pressing neeed. I have developed a rather significat integrated
    >application, used at very high levels in the Pentagon (US DOD), which involves
    >a Access Database for data management, and both Excel and PowerPoint as

    output
    >media - "Reports" are sent to senior staff in the form of Excel Spreadsheets,
    >and are also posted to a Secure Internet site- the staff involved are very
    >comfortable with Excel as a report Medium, and creating an Access report
    >directly would NOT be an acceptable alternative to this part of the application.
    > In addition, briefings to Senior DOD staff (up to and including the Secretary
    >of Defense) are composed of sets of PowerPoint Slides, driven from the Database.
    > I have created macros, in both Excel and PowerPoint to extract the relevant
    >data from the database, and to create the necessary Workbooks, and/or PowerPoint
    >Presntations (the PPTs are comosed of sets of 6 slides for each record in
    >the database). The propblem that I am encountering is that when the WorkBook,
    >or Presentation is Saved (by the exectuing code), the Macro code Module

    (and
    >any associated User-Forms) are also saved. This causes the "file contains
    >MACROS, enable/disable" message box to be displayed when the XLS or PPT

    files
    >is subsequently opened(for obvious reasons,this is not acceptable, and

    setting
    >the Security level lower IS NOT an option). Does anyone know of any way
    >to have the code (VBA) ONLY save either the WorkBook (Without the assciated
    >modules/User Forms), or the Slides (again without the associated Modules/User
    >forms).
    >
    >What I have had to do, for the present, is to open each of the resulting
    >files, either in Excel or PowerPoint, as appropriate, and manually delete
    >the Modules/User forms. In the case of the WorkBook, this is not a big

    issue
    >(only 1 workbook is involved), but in the case of the PowerPower Briefing
    >sets, there can be as many as 65 separate sets of Briefing slides(of 6 slides
    >each) created, and this manual appraoch can take close to an hour to complete.
    > Needless to say, if there is any way to automate that process, it would
    >be greatly appreciated.
    >
    >Arthur Wood



  3. #3
    Arthur Wood Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code


    Jim,

    Yes, that should work, though I womn't be able to tri it for real until Monday.
    Thanks for the link and the assistance. It will make me look like a real
    Wizard - and I will gladly give you the credit. Thanks again,

    Arthur Wood


    "Jim Cone" <jim_cone@att.net> wrote:
    >
    >Arthur,
    >
    >Take a look at the Chip Pearson web site for "Programming To The Visual

    Basic
    >Editor" (in Excel). I would assume that most of his code can be modified
    >and used in PPT.
    >
    > http://www.cpearson.com/excel/vbe.htm
    >
    >HTH
    >
    >Jim Cone
    >San Jose, CA
    >
    >****************************************
    >"Arthur Wood" <wooda@nospam.com> wrote:
    >>
    >>I have a rather pressing neeed. I have developed a rather significat integrated
    >>application, used at very high levels in the Pentagon (US DOD), which involves
    >>a Access Database for data management, and both Excel and PowerPoint as

    >output
    >>media - "Reports" are sent to senior staff in the form of Excel Spreadsheets,
    >>and are also posted to a Secure Internet site- the staff involved are very
    >>comfortable with Excel as a report Medium, and creating an Access report
    >>directly would NOT be an acceptable alternative to this part of the application.
    >> In addition, briefings to Senior DOD staff (up to and including the Secretary
    >>of Defense) are composed of sets of PowerPoint Slides, driven from the

    Database.
    >> I have created macros, in both Excel and PowerPoint to extract the relevant
    >>data from the database, and to create the necessary Workbooks, and/or PowerPoint
    >>Presntations (the PPTs are comosed of sets of 6 slides for each record

    in
    >>the database). The propblem that I am encountering is that when the WorkBook,
    >>or Presentation is Saved (by the exectuing code), the Macro code Module

    >(and
    >>any associated User-Forms) are also saved. This causes the "file contains
    >>MACROS, enable/disable" message box to be displayed when the XLS or PPT

    >files
    >>is subsequently opened(for obvious reasons,this is not acceptable, and


    >setting
    >>the Security level lower IS NOT an option). Does anyone know of any way
    >>to have the code (VBA) ONLY save either the WorkBook (Without the assciated
    >>modules/User Forms), or the Slides (again without the associated Modules/User
    >>forms).
    >>
    >>What I have had to do, for the present, is to open each of the resulting
    >>files, either in Excel or PowerPoint, as appropriate, and manually delete
    >>the Modules/User forms. In the case of the WorkBook, this is not a big

    >issue
    >>(only 1 workbook is involved), but in the case of the PowerPower Briefing
    >>sets, there can be as many as 65 separate sets of Briefing slides(of 6

    slides
    >>each) created, and this manual appraoch can take close to an hour to complete.
    >> Needless to say, if there is any way to automate that process, it would
    >>be greatly appreciated.
    >>
    >>Arthur Wood

    >



  4. #4
    Jonathan Martens Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code


    Hello Arthur,

    Is creating some kind of add in not a better and nicer way to handle your
    problem? It would save you all the extra work of deleting the macros because
    they simply aren't saved with the workbook but stored in a seperate file.

    Good luck!

    Jonathan

  5. #5
    Arthur Wood Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code


    May be possible, as I have discovered that the Code and Forms are only deleted
    FINALLY from the PPT or XLS after the code terminates. What I have is an
    application which generates as many as 60 separate sets of 6 slides per set
    (using the ActiovePresentation.SaveAs ... Method) and in taht case, EACH
    of the individual sets (saved PPTs has the Macro and the Forms code included
    in that PPT, even though the code has been run to Delete the code and forms
    from the ActivePresnetation. Beause the Code is still executing, the actual
    DELETE action does not truly occur, until the code terminates, at which time,
    I have already saved 60 PPTs, with the Macros embedded. This will still
    require opeing the PPTs manually, to remove the Macro code, and the forms)
    so that when the Briefing is actually run, the dreaded "The Presentation
    you are opeing contains macros..." message box does not appear (this message
    box, displayed to the Secretary of the Air Force, in a presentation, would
    cause some degree of concern, even though the cause is itself, benign).


    "Jonathan Martens" <j.martens@student.utwente.nl> wrote:
    >
    >Hello Arthur,
    >
    >Is creating some kind of add in not a better and nicer way to handle your
    >problem? It would save you all the extra work of deleting the macros because
    >they simply aren't saved with the workbook but stored in a seperate file.
    >
    >Good luck!
    >
    >Jonathan



  6. #6
    Rob Bruce Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code

    Hi Arthur,

    > FINALLY from the PPT or XLS after the code terminates. What I have is an
    > application which generates as many as 60 separate sets of 6 slides per

    set
    > (using the ActiovePresentation.SaveAs ... Method) and in taht case, EACH


    Not really sure why you are persisting your logic (VBA code) and your
    presentation (PPpresentations, Excel sheets) in the same place. Wouldn't it
    be better to have a set of templates with no code and a set of workbooks,
    add-ins, presentations, or maybe a VB app that does all of the manipulation
    of the templates via automation? Then you don't need to take code out of a
    document that never had any in the first place. Did I miss something?

    --
    Rob

    Wildly Out Of Date Excel VBA Programming Stuff:
    www.rb-ad.dircon.co.uk/rob/excelvba/




  7. #7
    Arthur Wood Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code


    Clearly a pure VB (with Office Automation) would be a better solution,. but
    in the Penatgon world, such an option is NOT possible. the Systems people
    for the Air Force have EACH and EVERY machine locked down so tight, the a
    user is give what is Euphamistically called the "Standard Integrated Desktop"
    - and access to the LOCAL C drive is absolutely Forbidden - Only approved
    applications can be loaded, by the Systems people, who hold ABSOLUTE Administrator
    Rights to each PC. As a developer, I would NEVER be granted the right to
    load VB onto MY PC, much less be able to distribute an application (built
    with VB to another PC - It would not be in the allowed "Standard Load".

    All in all, I am relatively new to the Office Automation world, haveing come
    to it somewhat indirectly (I have been a full-time VB developer for more
    then 6 years, and am now working on this Pentagon gig, where the closest
    I can get to VB is the fact that MS Office Professional is installed, as
    part of the "standard Load" on each PC in the office where I am working,
    thus allowing me to develop apps in VBA within Access or Excel or PowerPoint).
    As a result, a lot of what I have learned about Office and VBA within the
    Office Suite has been by trial-and-error. Largely by recording a Macro to
    solve some piece of the app, and then generalizing from there, based largely
    on my fairly complete understanding of VB itself. Until the most recent
    turn, it has been working rather nicely thank you, and the folks for whom
    I am doing the developement, are amazed at what can be accomplished in code,
    as all of this WAS being done PURELY MANUALLY - Imagine 65 sets of 6 slides,
    being maintained manually, for a briefing that was updated EVERY MONTH -
    that was keeping several people busy, almost full time, every month. Now,
    with the data being tracked in the Access database, and the slides being
    generated automatically from the PowerPOint Macros, the production time of
    those 65 sslide sets is down to 1 person, for a little over an hour each
    month, with some additinal time to keep the database updated.!!!! Guess who
    looks like a genius???

    Arthur Wood


    "Rob Bruce" <rob@rb-ad.dircon.co.uk> wrote:
    >Hi Arthur,
    >
    >> FINALLY from the PPT or XLS after the code terminates. What I have is

    an
    >> application which generates as many as 60 separate sets of 6 slides per

    >set
    >> (using the ActiovePresentation.SaveAs ... Method) and in taht case, EACH

    >
    >Not really sure why you are persisting your logic (VBA code) and your
    >presentation (PPpresentations, Excel sheets) in the same place. Wouldn't

    it
    >be better to have a set of templates with no code and a set of workbooks,
    >add-ins, presentations, or maybe a VB app that does all of the manipulation
    >of the templates via automation? Then you don't need to take code out of

    a
    >document that never had any in the first place. Did I miss something?
    >
    >--
    >Rob
    >
    >Wildly Out Of Date Excel VBA Programming Stuff:
    >www.rb-ad.dircon.co.uk/rob/excelvba/
    >
    >
    >



  8. #8
    Jonathan Martens Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code


    He Arthur,

    Three things to help you get along...

    1 - I believe there are ways of running VB code through VBA, as you have
    knowledge of the first.

    2 - There are sites on the internet where you can ask questions about Excel
    and VBA, for example this one, where I found a lot of expertise and useful
    tips: www.eng-tips.com and then specially the "Microsoft VBA - Visual Basic
    fo Applications" and the "Engineering Spreadsheets". As a VBpro you might
    also want to take a look at the Microsoft Visual Basic forum there. (You
    can find me there as "cactus13")

    3 - Also take a look at the Microsoft Developer Network (http://msdn.microsoft.com)
    specially at this one which displays best how to make an add-in in Excel
    (written for Office 97, but I believe it still works (there's more, but a
    bit harder to find unfortunately)

    Hope this helps you a step further...

    Jonathan

  9. #9
    Rob Bruce Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code

    Hi Arthur,

    > Clearly a pure VB (with Office Automation) would be a better solution,.

    but
    > in the Penatgon world, such an option is NOT possible. the Systems people
    > for the Air Force have EACH and EVERY machine locked down so tight, the a
    > user is give what is Euphamistically called the "Standard Integrated

    Desktop"

    <...>

    OK, good reason not to build a VB app wrapper for your application, but my
    main point still stands - You can do all of the manipulation of your reports
    from OUTSIDE of them. Code to add, change and save a PowerPoint presentation
    should exist in an entirely separate presentation (or Excel file or Word
    document or Access database). In this way your presentations never have any
    code in them and never need to be 'cleaned' before being handed to the
    ultimate client.

    A couple of other points strike me: How long before the Pentagon realises
    the real (and potentially destructive) power of 'only macros' and rolls out
    an office suite with no macro language at all? And why does it not surprise
    me that senior figures (some with their fingers pretty close to THE button)
    like to see the world as a series of simplified PowerPoint slides rather
    than acknowledge its wonderful but tragic complexity...

    --
    Rob

    Wildly Out Of Date Excel VBA Programming Stuff:
    www.rb-ad.dircon.co.uk/rob/excelvba/




  10. #10
    Sam Barrett Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code


    Arthur, the macro that you need is at the bottom. It cycles through all of
    the open workbooks and presentations and asks to delete the code. If you
    say yes, all macros, events, classes, and userforms are deleted. The best
    place to put this macro is in the personal macro workbook, personal.xls.
    When you place the code there, you will also need to use the Tools | References
    menu to add a reference to Microsoft Visual Basic for Applications Extensibility
    and Microsoft Power Point. HTH --Sam

    Option Explicit

    Sub DeleteAllMacroCode()
    ' First Excel
    Dim xlBook As Excel.Workbook
    Dim rsp As Integer
    Dim vbComp As VBIDE.VBComponent
    For Each xlBook In Excel.Workbooks
    If UCase(xlBook.Name) <> "PERSONAL.XLS" Then ' Skip personal macro
    workbook
    rsp = MsgBox("Delete code in " & xlBook.Name, vbYesNoCancel,
    "Delete")
    If rsp = vbCancel Then Exit Sub
    If rsp = vbYes Then
    For Each vbComp In xlBook.VBProject.VBComponents
    If vbComp.Type = vbext_ct_Document Then
    With vbComp.CodeModule
    .DeleteLines 1, .CountOfLines
    End With
    Else
    xlBook.VBProject.VBComponents.Remove vbComp
    End If
    Next vbComp
    End If
    End If
    Next xlBook

    ' Now PoPo
    Dim ppApp As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    On Error Resume Next
    Set ppApp = GetObject(, "PowerPoint.Application")
    If Err.Number = 0 Then
    On Error GoTo 0
    For Each ppPres In ppApp.Presentations
    rsp = MsgBox("Delete code in " & ppPres.Name, vbYesNoCancel,
    "Delete")
    If rsp = vbCancel Then Exit Sub
    If rsp = vbYes Then
    For Each vbComp In ppPres.VBProject.VBComponents
    If vbComp.Type = vbext_ct_Document Then
    With vbComp.CodeModule
    .DeleteLines 1, .CountOfLines
    End With
    Else
    ppPres.VBProject.VBComponents.Remove vbComp
    End If
    Next vbComp
    End If
    Next ppPres
    Set ppApp = Nothing
    End If
    On Error GoTo 0
    End Sub


  11. #11
    Arthur Wood Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code


    Rob,
    Thank you for the nudge in the "right" direction. I have found that by
    simply adding a Declaration of a NewPres object (of type Presentation), and
    the instatiation of that object, inside my main recordset processing loop,
    along with the assignment of the associated Template, so that each of the
    slides in the "6-Pack" has the proper appearance ( AF logo and formatting),
    that each of the slide sets, when saved in the code, does in fact NOT have
    the macro code included, which was what I was after in the first place.
    Thanks for the pointers.

    as for the other comment ("How long before the Pentagon realises
    the real (and potentially destructive) power of 'only macros' and rolls out
    an office suite with no macro language at all?" - Probably won't happen for
    a VERY VERY long time, given the fact that the movement of the powers that
    be in this Building, requires about as much effort as would be required to
    MOVE the Building, ITSELF, 5 feet from its current location!!!)

    And as for weaning the higher ups from their dependance on Briefings in PowerPoint
    - that will happen when the first event (moving the PENTAGON itself), happens.
    You've heard the expression "Management by wandering around"?, here the
    expression is "Management by PowerPoint". It is their "safety-blanket" and
    just like trying to take a 1 year old's blanket away from him/her, so too,
    you would get precisely the same reaction if you tried to take away PowerPoint
    - or another such tool. It is too easy to skim through a set of slides -
    so it MUST be the reight thing to do.

    JMHO

    Arthur Wood



    "Rob Bruce" <rob@rb-ad.dircon.co.uk> wrote:
    >Hi Arthur,
    >
    >> Clearly a pure VB (with Office Automation) would be a better solution,.

    >but
    >> in the Penatgon world, such an option is NOT possible. the Systems people
    >> for the Air Force have EACH and EVERY machine locked down so tight, the

    a
    >> user is give what is Euphamistically called the "Standard Integrated

    >Desktop"
    >
    ><...>
    >
    >OK, good reason not to build a VB app wrapper for your application, but

    my
    >main point still stands - You can do all of the manipulation of your reports
    >from OUTSIDE of them. Code to add, change and save a PowerPoint presentation
    >should exist in an entirely separate presentation (or Excel file or Word
    >document or Access database). In this way your presentations never have

    any
    >code in them and never need to be 'cleaned' before being handed to the
    >ultimate client.
    >
    >A couple of other points strike me: How long before the Pentagon realises
    >the real (and potentially destructive) power of 'only macros' and rolls

    out
    >an office suite with no macro language at all? And why does it not surprise
    >me that senior figures (some with their fingers pretty close to THE button)
    >like to see the world as a series of simplified PowerPoint slides rather
    >than acknowledge its wonderful but tragic complexity...
    >
    >--
    >Rob
    >
    >Wildly Out Of Date Excel VBA Programming Stuff:
    >www.rb-ad.dircon.co.uk/rob/excelvba/
    >
    >
    >



  12. #12
    Rob Bruce Guest

    Re: Saving either XLS or PPT WITHOUT the associated MACRO code

    Hi Arthur,

    > You've heard the expression "Management by wandering around"?, here the
    > expression is "Management by PowerPoint". It is their "safety-blanket"

    and
    > just like trying to take a 1 year old's blanket away from him/her, so too,
    > you would get precisely the same reaction if you tried to take away

    PowerPoint
    > - or another such tool. It is too easy to skim through a set of slides -
    > so it MUST be the reight thing to do.


    I now have this enduring mental image of a PC sitting on the big desk in the
    Oval Office. On the screen is an MS Word document that contains nothing more
    than a big, red ActiveX CommandButton object labelled "Nuke Saddam...". Upon
    clicking the button a messagebox appears asking "You are about to obliterate
    the very cradle of human civilisation. Are you sure you want to do this?
    *Click Yes to execute this deeply ironic action. *Click No to give yourself
    time to reconsider."

    Being the absolute professional that you are, you have ensured that the
    messagebox also contains a checkbox labelled "Don't ask me this question
    again"

    Of course, on this side of the Atlantic we do things differently. Deep
    within Her Majesty's Ministry Of Defence there is a guarded room containing
    an intricately finished mahogany lever. When pulled, this lever operates a
    complex system of pulleys and gears that eventually sends a mechanical Morse
    code message to our fleet of Trident submarines; "If you wouldn't awfully
    mind, chaps, could you send a shot across the bow of this Hussein fellow?"

    Sorry. It's been that kind of day.

    --
    Rob

    Wildly Outdated Excel VBA Programming Stuff:
    www.rb-ad.dircon.co.uk/rob/excelvba/



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center