Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2007 Trusted Network Location not running VBA macros

Posted on 2013-12-02
9
Medium Priority
?
1,548 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39691604
Would you mind to attach your spreadsheet here?

Andrew Man from Hong Kong
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 600 total points
ID: 39691823
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
ID: 39693919
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 81

Assisted Solution

by:byundt
byundt earned 600 total points
ID: 39694385
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
 
LVL 4

Accepted Solution

by:
andrew_man earned 800 total points
ID: 39694491
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
ID: 39695927
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
ID: 41481968
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
ID: 41482045
Thanks
0

Featured Post

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This program is used to assist in finding and resolving common problems with wireless connections.
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

719 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