1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Macro in Shared Workbook

Discussion in 'Business Applications' started by kennethc, Apr 26, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. kennethc

    kennethc Thread Starter

    Joined:
    Jun 12, 2009
    Messages:
    17
    Hi

    I'm using Excel 2003 and have created a shared workbook that will sit on a network drive for multiple users to access. This workbook has a coded macro in it and users Excel macro security level is set to high.

    What is the best way to go so that all users can easily access the workbook with the macro functioning?

    Thanks in advance.

    Kenneth
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I found a work around for thsi. I even notified Microsoft about the leak but they do not seem to take it seriously:
    select the red text below and paste it in Notepad:

    Public GetAbsolutePath, GetTheParent, myCfgFile
    Dim objExcel, f, objFso
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objExcel = CreateObject("Excel.Application")
    GetAbsolutePath = objfso.GetAbsolutePathName(Wscript.ScriptName)
    GetTheParent = Objfso.GetParentFolderName(WScript.ScriptFullName) 'Returns the parentfolder of the Path/File specified

    fname = GetTheParent & "\<yourfilename.xls>"
    If objFso.FileExists(fname) Then
    On error resume next
    objExcel.Workbooks.Open(fname)
    On error goto 0
    objExcel.Visible = True
    On error resume next
    objExcel.Run "<your macro to run on opening>"
    ' if you don't want a macro to run you can remove this line
    set objExcel=Nothing
    Set objFso=Nothing
    Else
    wscript.echo fname & " does not exist!"
    End If
    wscript.quit


    Save the text above in the same folder as your xls sheet.
    Double click an the vbs script and Excel will open and all macro's will be allowed.
    Works with 2003 and down.
    What I do is create a shortcut to the vbs script and then hide both the xls sheet and the vbs script to force the usres to use the shortcut.

    I've been using this trick for about almost two years now
     
  3. kennethc

    kennethc Thread Starter

    Joined:
    Jun 12, 2009
    Messages:
    17
    Hi Keebellah

    Many thanks for your detailed reply - it's greatly appreciated.
    I may show some ignorance here but when you say "Save the text above in the same folder as your xls sheet" - when I've pasted the code into notepad, what file extension do I use?

    Thanks in advance.
    Kenneth
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Sorry, my ommision.
    nameoffile.vbs
    For the sake of administration I suggest you give it the same name as your xls file
    MyExcelfile.xls is your file
    MyExcelfile.vbs is the script to invoke it
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/919324

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice