Macro in Shared Workbook

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
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
 

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top