Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling Macro from one workbook to another (Xl '97 on..)

    I've written a macro in a workbook that I distribute to a user base and update it on a weekly basis. It is used to import data to create workbooks that the user then can play with. The user can also use the macro to add additional imported data to those workbooks in the future.

    When the macro finishes its work I close it and leave the updated/new workbook active.

    It is at this final stage (just before I close my macro) that I would like to call a macro that these users might create. They might want it to format and sort the data.

    How can I do this without them adding a module or subroutines to my code? I don't think that's a good idea since when I send them an update it will wipe out their code.

    Thanks for any help you might have!!

    dingo

  2. #2
    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: Calling Macro from one workbook to another (Xl '97 on..)

    Just an idea, not sure of all details:

    Run a macro at the end called "USER" (or someother such name). If it is not in your workbook, it should call one in the user's module. I am not sure how to trap the error if "user" does NOT exist in the user's workbook. I will have to ponder that. (you could put one in all the user's workbooks and work with them to edit it or just leave the sub user(): end sub to do nothing)

    Steve

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    Thanks for the suggestion: I tried something like that but it didn't work. I added a public sub in the other WB and then tried to call it but got an error.

    I'm having a vague recollection of reading where someone described a method to actually generate a subroutine on the fly and then call it (could have been a dream though - my days/nights are getting like that lately). If that's possible I thought of having the user code his macro and get it working like he wants it and then saving it as a text file. I would then read that ascii file and somehow magically convert it into a sub in my own WB and then call it.

    Is that last idea possible or have I just completely blown a head gasket?

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    Hi Dingo,

    I don't suppose you'd want to execute the user's macro without them having a say in the matter - that might not be a good career move. So you'd need to identify the available macro (if there is one) in the user's workbook and give the user a choice as to whether their macro should be run.

    You'd also need to allow for the possibility that the user has more than one macro, requiring usage in a particular sequence, or optionally, and that some subs might not be meant to be run on their own. Then, what would you do about macros requiring parameters to be set (eg a range selected)?

    Some users might also have their code stored in personal.xls instead of in the files being worked on, which creates additional issues.

    In the end, this can become fairly complex. So, do you really want to do this, or would it be better to leave the users to decide for themselves what they want to do without such helpful prompting?

    Cheers?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    To
    -open a new module called Constants
    - paste some code into it:

    Sub WriteModule()
    'Main macro document has to be opened!!!
    With ActiveWorkbook.VBProject
    On Error Resume Next
    .VBComponents.Remove .VBComponents("Constants")
    With .VBComponents.Add(vbext_ct_StdModule)
    .Name = "Constants"
    .CodeModule.InsertLines 2, "Global Const sCodeChars as string=" & Chr(34) & "ABCDE" & Chr(34)
    .CodeModule.InsertLines 3, "Global Const sDatabasePath as string=" & Chr(34) & "C:TEMP" & Chr(34)
    .CodeModule.InsertLines 4, "Global Const sdocumentpath as string=" & Chr(34) & "C:TEMP" & Chr(34)
    .CodeModule.InsertLines 5, "Global Const ssettingspath as string=" & Chr(34) & "C:TEMP" & Chr(34)
    .CodeModule.InsertLines 6, "Global Const stemplatepath as string=" & Chr(34) & "C:TEMP" & Chr(34)
    .CodeModule.InsertLines 7, "Global Const sYearSep as string=" & Chr(34) & "-" & Chr(34)
    .CodeModule.InsertLines 8, ""
    .CodeModule.InsertLines 9, "Global Const b4digitYear as boolean=" & Chr(34) & "True" & Chr(34)
    .CodeModule.InsertLines 10, "Global Const bYear as boolean=" & Chr(34) & "True" & Chr(34)
    .CodeModule.InsertLines 11, ""
    .CodeModule.InsertLines 12, "Global Const inumdigits as integer=6"
    End With
    End With
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    Yes, I do want to do this. But I would control it by having to user use a specific Name for the Macro. If that macro is not there then I can't run it. If it is there then that's the permission you're talking about. Its his data so whatever he puts in there he is responsible for.

    Cheers!

  7. #7
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    AHHH!

    Just what I was "dreaming" about! So it must not have been a dream?

    So if the user saves his module as a text file with a specific name then I could test for its presence and if there read it in and construct a Sub? Then I could just call User_Macro ? and that would be it?

    If so, that's probably the ideal solution. I would wrap the code with some error handling stuff and be all set.

    Thanks!

  8. #8
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    Interesting solution but how would I handle the case where I create a new workbook? Would I have to have that workbook open along with mine and the new one? How would the UserMacro know which sheet in which workbook to work on? Could I pass a sheet name to UserMacro?

    Thanks!

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    IMHO, it is a bad idea to pull in text, add the code, and run it.
    One reason is that the code might require the loading of some libraries and by just loading the text you would not know about such libraries or other dependencies.

    I've not tried this in Excel, but, in Word, you could have the user supply you with a text that is the name/file spec of a project that would be added as a Reference in your code. The referenced project would have to contain Subs/Functions/Whatever that used procedure names and arg lists you specified.

  10. #10
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    Yo cannot do this with macros. A macro is a Public Sub with no args.
    The user written code has to use a Sub name that you specify and must use an arg list that you specify.

    P.S. You could allow variations in the Sub name, but I see no point in doing that. The arg list must be known so that you can set/retrieve whatever.

  11. #11
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    Yes, I see!

    I think my reply to your earlier post is the best direction - do you agree? (the one about copying a sub from one project to another)

    Thanks!

  12. #12
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    <P ID="edit" class=small>(Edited by WebGenii on 24-Sep-02 11:01. To add link to site)</P>I see what you mean...

    Hmmm..

    I found this late last nite: http://www.cpearson.com/excel/vbe.htm#CopyModule

    Is this along the lines of what you're talking about? ( edit: The part about copy modules between projects)

    This looks "safer"..

    Comment?

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    One thing to be aware of... I was once doing something similar - using VBA to create code - and ran into problems with anti-virus checking. If I saved my file to my hard drive, it saved OK, but when I tried to save it to a network drive, the code to create the code would get clobbered.

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

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    Oh, yes. That would be the infamous Office plug-in. Can be a PITA. Is reknown for causing trouble with Excel. Incomplete saves, irritatingly slow closing, .....
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Calling Macro from one workbook to another (Xl '97 on..)

    I suspect it would be much simpler to import a module a user has exported. Might even not be detected by the NAV Office plug-in.
    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
  •