Dave writes:
I found your excellent code to run a procedure on a target spreadsheet from
an originating spreadsheet on VB Express Forum. Â It works fine if both
files are on the same jump drive but fails on our work network and even if
the originating file is in Documents on my home computer. Â Is there any
security setting that you can think of that would cause this to happen?
For others following along, the source KB entry (you may need to create FREE login to get it) can be found at http://www.vbaexpress.com/kb/getarticle.php?kb_id=279
With regards to the security question, it would depend if this is an Office 2007/2010 instance. If so, then the folders where the target spreadsheets reside would need to be included in the "Trusted Locations". (Office button->Excel Options->Trust Center->Trust Center Settings->Trusted Locations.)
If this is a version of Excel prior to 2007, I can't think of anything that would be a security concern to prevent this. One thing that could potentially be an issue is using UNC paths instead of mapped drives, but I haven't tested that to be sure.
I can say that I use a variant of this macro every month to roll forward 12 inventory files in one shot. The source and the destination workbooks all reside on our network, but in different drives. The key parts of this:
sRollMacro = "'2009-07-31 16030-900 (Hotel Beverage).xls'!InventoryRollForward_Liquor"
Workbooks.Open (ThisWorkbook.Worksheets("Master").Range("rngSourcePath") & _
"2009-07-31 16030-900 (Hotel Beverage).xls")
Application.Run (sRollMacro)
I first used this in Excel 97, and it still works in 2007 today. (Haven't tested it in the 2010 beta yet, but I will in a few days.)Hope this helps!
