Results 1 to 8 of 8
  1. #1
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Del Excel WkBk from macro called by that WkBk

    I'm a bit of an Excel VBA newbie, so I don't know if this suggestion is relevant, but it sounds like housing the code in an Excel add-in (.xla) rather than a template might avoid this problem.

  2. #2
    ralph_davies
    Guest

    Re: Del Excel WkBk from macro called by that WkBk

    You might be right but I don't have any experience with addins.

    I may have found a way around the problem but I need some help with syntax.
    If I can make all the calls to elements of my Project, which are in the AutoOpenMacro, specific to that project/workbook I may be able to force the running of the correct elements. This includes a userform.show. how can I make such statements specifically address the form in the Template as opened XXXX1 ?????????? Excel Help says you can do this but doesn't give examples I can relate to my problem.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Del Excel WkBk from macro called by that WkBk

    Hi Ralph,
    Adding Option Private Module to the declarations section of your modules will mean that public procedures (and variables) in that module will only be available within that project. That I think should help you. Personally though, I don't like AutoOpen or equivalent macros in templates for exactly this reason. My preference would be to have a central workbook that contains all your code and creates new quote workbooks based on a separate template, which contains only code required to make the workbook run (eg User defined functions etc.)
    Hope that helps - if not please repost.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    ralph_davies
    Guest

    Re: Del Excel WkBk from macro called by that WkBk

    Thanks for the idea but it appears that option private is the default for the object modules I am using in Excel 97.

    My problem is that the Original file and the Copy are identical since one is a copy of the other.

    If I could find a way to open the original as XXXX1 with no extension the problem no longer exists since I do not have to SaveCopyAs XXXX1 then Close the original.

    That

  5. #5
    ralph_davies
    Guest

    Del Excel WkBk from macro called by that WkBk

    *** Edited Geoff W- point to <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=33806&page=0& view=expanded&sb=5&vc=1#Post33806>cross-post in Excel forum</A> ***

    I have a really tricky Catch22 problem.
    I have a AutoOpen Macro embedded in a Template.
    The Template is used to create a Quote.
    The PriceList.xls from which the information is gathered for the Quote has a macros which references the workbook (XXXX1 no extension) as created by the template (XXXX.xlt)
    This is OK so far.
    I then create and save the Quote minus some confidential pricing info.
    I also save a copy of the Workbook as WSYYY.xls used to create the Quote so the user can amend it latter and generate a new Quote.

    That

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Del Excel WkBk from macro called by that WkBk

    Ralph,

    About the only way I could think of would be to have your function in another workbook which initiated the process. So workbook ABC opened workbook QuoteSource, and then created XXXX1.

    Failing that (and not having tried this), could the Original file use Application.Run to run a procedure in the Add-in, which would perform the copy and close? Sticky, I know- just a thought.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    ralph_davies
    Guest

    Re: Del Excel WkBk from macro called by that WkBk

    Thanks

    That is how I solved the problem.
    However the symptoms were not being caused by that.

    see my latest posting 34678 re Opening WBks with Macros [Post#: 34649 ]
    Thanks again for the help

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Closing thread- new thread opened

    Sorry,
    I hadn't realised the discussion had resumed in another thread. So I'll close this thread off and point to <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=vb&Number=34649&page=0& view=expanded&sb=5>this thread</A>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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