Solved

Excel 2007 Trusted Network Location not running VBA macros

Posted on 2013-12-02
9
1,408 Views
Last Modified: 2016-02-26
Hello all, I am perplexed by this.  I have an Excel 2007 spreadsheet which uses a VBA macro for various operations upon opening the spreadsheet.  The spreadsheet resides on a Synology network drive that has been listed in Excel as a Trusted Network Location.  The option to forbid all macros to be run (except those in a Trusted Location) has always been enabled as well.  It has all been working fine until a worker arrived at the office over the weekend to find 6 major circuit breakers tripped from some sort of power anomaly and all the computers and network equipment shut down.  He switched the breakers back on and the network came back to life but assigned the Synology network drive a different IP address from what it was before the power anomaly.  I came in today and found no one had access to the Synology drive and I assigned it the same IP it had before and made it static this time.  

Okay, now later in the day I tried using the excel spreadsheet in question and noticed that none of the macros were executed when it opened, as it normally would.  I removed the Trusted network location that was on the Synology drive and then added it again, hoping this would reset everything.  It did not work.  I have checked very carefully the macro and trusted location settings in the spreadsheet but it just won't execute the macros upon opening unless I set the macro settings to run any and all macros all the time.  I can't leave it with this dangerous setting but cannot get it to run the macros when in the trusted location on the network Synology drive???  Did something change on the Synology when the power went out?  Is there some hidden network change that I don't realize?   I am pulling my hair out to get this very important spreadsheet program to work again.

Thanks for any help and/or advice you might be able to suggest.  Please let me know if you need further information.  Thank you!
0
Comment
Question by:mjchevalier
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 4

Expert Comment

by:andrew_man
Comment Utility
Would you mind to attach your spreadsheet here?

Andrew Man from Hong Kong
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 150 total points
Comment Utility
Have you double checked the actual registry entries at HKCU\Software\Microsoft\Office\12.0\Excel\Security\Trusted Locations to make sure all the information there is correct? Also, have you rebooted since the change just to make sure?
0
 

Author Comment

by:mjchevalier
Comment Utility
Thanks Andrew Man and rorya, I am posting the spreadsheet in this post Andrew, and I will check the registry for the correct trusted locations when I am back in the office tomorrow.  Please let me know if there is anything else you can think of.  The W: drive is the network drive which has a UNC name of \\DISKSTATION\Working Directory\Marc C\.  Thanks guys.
Roll-to-Roll-Estimating.xlsm
0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 150 total points
Comment Utility
I did not see any reason why your code would fail to run.

That said, you might try using Workbook_Open and Workbook_BeforeClose event subs in preference to Auto_Open and Auto_Close.

Auto_Open and Auto_Close subs were used in Excel 95, but Microsoft replaced them in Excel 97 (with Workbook_Open and Workbook_BeforeClose), and has been discouraging their use ever since.

In revising the code to fit in Workbook_Open and Workbook_BeforeClose, I made explicit the reference to the first worksheet in the workbook. Your Auto_Open and Auto_Close subs ran on whichever worksheet was active at the time--which would cause a problem if somebody saved the workbook with Sheet2 or Sheet3 being active. I also got rid of all the statements that selected cells. They aren't required, and slow down the macro and make the screen blink. I also combined a number of statements where you were initializing a contiguous range of cells to the same value.

'This code must go in ThisWorkbook code pane. It replaces the Auto_Open and Auto_Close subs in Module 2
Option Explicit

Private Sub Workbook_Open()
'by Marc Chevalier
Dim ThisEstimate As Long
Dim ReadText As String
Dim StoreFile As String

StoreFile = "W:\Marc C\PSDPEstNo.txt"
'replace with another path,
'network folder if multi-user

'read previous number:
If Dir(StoreFile) = "" Then 'not found
    ThisEstimate = 1
Else
    Open StoreFile For _
        Input Access Read As #1
    While Not EOF(1)
        Line Input #1, ReadText
        ThisEstimate = Val(ReadText)
    Wend
    Close #1
End If
ThisEstimate = ThisEstimate + 1

'Store this number:
Open StoreFile For _
    Output Access Write As #1
Print #1, ThisEstimate
Close #1

With Worksheets(1)
    .Range("A16:A17").Value = "N"
    .Range("B1").Value = Format(Date, "mm/dd/yyyy")
    .Range("B2:B8").Value = " "
    .Range("B10").Value = "00" & ThisEstimate
    .Range("C15").Value = "Thin"
    .Range("C17").Value = "Standard"
    .Range("C20").Value = "None"
    .Range("C30:C31").Value = 0
    .Range("H2:H3").Value = 0
    .Range("J2:J4").Value = 0
    .Range("J6").Value = 1
    .Range("J10").Value = 0
    .Range("J11").Value = "None"
    
    .Range("C14").Value = "Light"
    .Range("D14").Value = 0
    .Range("F14").Value = 0
    .Range("H14").Value = 0
    .Range("J14").Value = 0
    .Range("L14").Value = 0

    .Range("E19").Value = 0
    .Range("G19").Value = 0
    .Range("I19").Value = 0
    .Range("K19").Value = 0
    .Range("M19").Value = 0

    .Range("A22:A25").Value = " "
    .Range("E22:E25").Value = 0
    .Range("G22:G25").Value = 0
    .Range("I22:I25").Value = 0
    .Range("K22:K25").Value = 0
    .Range("M22:M25").Value = 0

    .Range("C28:D28").Value = 0
    .Range("F28").Value = 0
    .Range("H28").Value = 0
    .Range("J28").Value = 0
    .Range("L28").Value = 0

    .Range("E29").Value = 0
    .Range("G29").Value = 0
    .Range("I29").Value = 0
    .Range("K29").Value = 0
    .Range("M29").Value = 0
    
    .Range("D31").Value = 0
    .Range("F31").Value = 0
    .Range("H31").Value = 0
    .Range("J31").Value = 0
    .Range("L31").Value = 0
End With

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ThisEstimate As Long
Dim ReadText As String
Dim StoreFile As String
Dim Fname As String
Dim Path As String

StoreFile = "W:\Marc C\PSDPEstNo.txt"

If Dir(StoreFile) = "" Then 'not found
    ThisEstimate = 1
Else
    Open StoreFile For _
        Input Access Read As #1
    While Not EOF(1)
        Line Input #1, ReadText
        ThisEstimate = Val(ReadText)
    Wend
    Close #1
End If

With Worksheets(1)
    If .Range("D32").Value > 0 Or .Range("F32").Value > 0 Or .Range("H32").Value > 0 Or .Range("J32").Value > 0 Or .Range("L32").Value > 0 Then
        .PageSetup.CenterHeader = "&B&18" & .Range("B2").Value & " Estimate"
        .PrintOut
    
        Path = "W:\MyPSDPEstimates\"
        Fname = .Range("B2").Value & "00" & ThisEstimate & ".xls"
        ActiveWorkbook.SaveAs Path & Fname
    End If
End With
End Sub

Open in new window

Roll-to-Roll-Estimating.xlsm
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Accepted Solution

by:
andrew_man earned 200 total points
Comment Utility
Dear Mjchevalier,

I just review your code, the code is so simple.  But, I hope you should check the existence and access right of below path.

W:\Marc C\
W:\MyPSDPEstimates\

By the way, I produced the same network environment in office.  I also can create the PSDPEstNo.txt

If you still cannot find the problem, please dump the error screen and attach to here.

Warmest Regards,

Andrew Man
PSDPEstNo.txt
0
 

Author Comment

by:mjchevalier
Comment Utility
I changed the Trusted location from the network W: drive to a new folder on the local C: drive and the spreadsheet now works.  For some reason the VBA code would not execute on the W: drive ever since the power surge and outage we had.  I can't explain why but it works now on the local drive.  Thanks to all for your help.
0
 

Expert Comment

by:balachan56
Comment Utility
I have a similar problem.
My code is merely meant to copy all sheets (with data, formulae, images and graphs) of one Excel workbook on a subfolder on
the network to another file on some other subfolder on the network and to retain the original formats and protection.

My Excel vba code works well on any subfolder of standalones (PC or Laptop) with Winx XP or Win2K7. Also, it works on Excel
97-2003 and Excel to 2013 on the standalones. However, on the network drives where it is actually meant to be used, it
behaves crazily. On win2k7 with Excel 97 it works well on the network, but certain images are ALWAYS lost (rid error). On
Win2k7 with Excel 2013, Excel states that many variables of the very same code do not exist!! and the code then behaves
eccentrically. A number of errors are thrown up. e.g. One is not supposed to get access to the sheet unless properly logged
in. However, now, a login error is thrown up and then if ignored, the user can enter and modify data! Do I have to declare ALL variables, even temporary ones with Option Explcit or in any other manner? Due to unrecognised variables, certain standard commands are not recognised and code that ought to have been bypassed, gets executed instead. Do I also have to make my local drive and all other network drives that are used as trusted locations?

I know that the thread is old. I'm not sure if I can submit my query here and will hence start a new thread. The help of
experts would definitely be appreciated. Thanks in advance.
0
 

Expert Comment

by:balachan56
Comment Utility
Thanks
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now