call a sub from an xls file on the network

X

XDKYX

New Member
Joined
Oct 1, 2008
Messages
11
I'm trying to call a sub in an exce. file that resides on our network drive so that multiple users can access the programs that I develop and I can get VBA to open the file but I can't get it to open the sub titled "DCU". I keep getting the following error.
Cannot run the macro DCU. The macro may not be available in this workbook or all macros may be disabled.
Click to expand...
Here is my code that errors.
Rich (BB code): 
Option Explicit
Sub DESCRIPTION_CLEANER_UPPER()
Application.Visible = True
Workbooks.Open Filename:= _
        "\\Cansvp01\grp_01f\Common\Common-Parts\Prcng\Macros\Macros.xls"
Application.Run "DCU"
End Sub
Any help is greatly appreciated, maybe I'm calling it wrong or maybe there's another way to accomplish what I'm trying to do here.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Welcome to the Board!

Maybe try:

Rich (BB code): 
Application.Run "\\Cansvp01\grp_01f\Common\Common-Parts\Prcng\Macros\Macros.xls!DCU"

I've never ran a macro from a closed workbook, so I am not positive if it will work. I typically put them in my Personal.xls file.

Otherwise you may have to do this:

Rich (BB code): 
Option Explicit
Sub DESCRIPTION_CLEANER_UPPER()
Application.Visible = True
Workbooks.Open Filename:= _
        "\\Cansvp01\grp_01f\Common\Common-Parts\Prcng\Macros\Macros.xls"
Application.Run "Macros.xls!DCU"
End Sub
Hope that helps.
 
Upvote 0
Nice! The second one works! Now I'm curious as to how I get it to work and have the Macro.xls not visible. I tried changing the true to false in the application.visible = true but that just made my current sheet and the Macro.xls invisible. Any suggestions?
 
Upvote 0
Do a search for personal.xls. It is a workbook that stores macros that you use frequently and it is not visible.

Hope that helps.
 
Upvote 0
I do use the personal.xls for myself and my macros but I'm trying to put an excel file out there with a few macros in it on the network drive so that other users can link to it and use the macros within it. Like the code above.

Oh and thanks for the welcome!
 
Upvote 0
You can create an add-in or put a shortcut to that file in their xlstart folder I believe is what I have seen people do.
 
Upvote 0
If I have the file start up every time someone starts excel would that lead to usage problems? I mean can more than one user open the file at a time without getting that read-only error? I was thinking of having it open only as needed through code. What are your thoughts?
 
Upvote 0
You can make the actual file read-only by right clicking on it and selecting properties and on the general tab there should be an attribute for read-only. This way no one will get the read-only message and they cannot make changes to the actual file. If you ever want to add to the workbook more macros you will have to take off the read-only make changes and then set it back to read-only.

You can also digitally sign it with selfcert.exe and have them trust your signature if you want to go that far also.

Hope that helps.
 
Upvote 0
Genius man, absolute genius! One more (I think) final question. Excel now starts up the Macro.xls file because I put the shortcut in the XLSTART folder. The problem is that it is visible. Is there a way to make it not visible? I'm using 2007 so is there something I need to do in Excel itself?
 
Upvote 0
Maybe try putting in the code:

Code: 
Windows("Filename.xls").visible = false
Hope that helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

spacecaptainsuperguy
  • Question
Advice for linked files on network drive
Replies
0
Views
338
spacecaptainsuperguy
spacecaptainsuperguy
C
  • Solved
Macro works for one file, does not work for all files in the directory
2
Replies
13
Views
510
Alex Blakenburg
A
E
  • Question
Old XLSM files crashing Excel; looking for help to troubleshoot, please
Replies
9
Views
1K
jkpieterse
jkpieterse
starl
  • Solved
Calling Private Worksheet level module from standard module doesn't always work after changing sheet's CodeName
Replies
9
Views
722
starl
starl
H
  • Question
Macro to download zip, unpack and copy data
Replies
0
Views
97
HeinrichPaul
H

Forum statistics

Threads
1,215,180
Messages
6,123,502
Members
449,100
Latest member
sktz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top