Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    universal macro? (Excel 2003)

    I know that it is possible to create macros that are stored in a "personal workbook", that can be executed in any workbook I use. My boss wants to create workbooks for each of our sales reps to be housed on a shared server; he also wants to create macros that will automatically update the data for each rep when they open their workbook. Is it possible to create a sort of "mother workbook" that would store the macros, rather than having to store them within each reps workbook? Need to know ASAP.

    Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: universal macro? (Excel 2003)

    The workbooks will have to contain at least a Workbook_Open event procedure, but this could call external macros.

    You could store the common macros in a workbook, and save that as an add-in (.xla). If you place the add-in in a shared network folder, all reps can install the add-in, so that the code will be available.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: universal macro? (Excel 2003)

    An add in is a great solution to this query. Just a question! QUOTE>>""The workbooks will have to contain at least a Workbook_Open event procedure, but this could call external macros."" Are you saying that the Workbook_Open will call on macro's contained in the add-in file to automate the necessary operations!?
    Regards,
    Rudi

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

    Re: universal macro? (Excel 2003)

    Well, you could also simply add a reference to the macro workbook in each of the files that needs that macro workbook. Opening any of the files will automatically cause the macro workbook to load as well.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: universal macro? (Excel 2003)

    Yes.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: universal macro? (Excel 2003)

    That's a good idea.

  7. #7
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: universal macro? (Excel 2003)

    The boss says: "the workbooks that we create will be brand new exports from Access queries each day. So anything that we do to the workbooks in the rep directories on the server would be overwritten the next day when we re-export the queries. I thought that maybe instead of exporting queries we could append each day

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

    Re: universal macro? (Excel 2003)

    Why not fetch the data from Access instead of push it to Excel?
    Data, get external data, new database query
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: universal macro? (Excel 2003)

    Largely, because our people are relatively comfortable with Excel, but only about 4 of us know how to use Access. Also, reps don't have Access on their computers. Currently, we work with Excel pivot tables. Our sales data comes from a Foxpro dbase. We pull the data into Access in order to create the tables we need, then use pivot tables to manipulate the data. We find working with Excel is much simpler than using Access. I have been refreshing the pivots each day after new files are pulled, then emailing the reports to the Sales Manager. She in turn disseminates each reps data. Our goal is to create a way for the reps to access their own data on a common server, and to automate the process as much as possible.

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

    Re: universal macro? (Excel 2003)

    You misunderstood what I wrote. I meant to say, why push the data from Access to Excel, when you can also pull the data into Excel from Access. No need to have access for that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Lounger
    Join Date
    Jul 2003
    Location
    Notre Dame, Indiana, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: universal macro? (Excel 2003)

    Well, then, that is what I already do. I pull the data from Access to Excel. My boss thinks that we should do exports from Access to Excel so that we will have static copies of each days data for look-back purposes. For example, the Sales Manager has a weekly coaching meeting with each Sales rep. At that time she wants to compare where they are today with where they were last week at the same time, with regard to their goal for the month and quarter.

    We want to automate pulling the files from the Foxpro database into Access (a process that is further complicated by the fact that the Foxpro dbase resides on Mac, but everything else we do is on PC), and also automate pushing from Access to Excel. If there are better ways to do what we need to accomplish, we're open to suggestion, but that is the boss's vision right now.

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

    Re: universal macro? (Excel 2003)

    It would be a breeze to create two sheets which fetch data: one with today's and one with yesterday's (or last week's or whatever). Then you could easily base charts or reports on those two sheets. Presto. Then if you're at a new day, you refresh both sheets and your overview is ready to be printed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: universal macro? (Excel 2003)

    If the Sales Rep's name in the Access DB is tied into the Excel file name, then a generic SQL could be written into the Workbook_Open event in a "template". The issue would then simply become archiving the Excel files on a daily basis. The code for archiving would have to open the files before archving - in case a Rep didn't open her/his file that day. HTH
    Gre

Posting Permissions

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