Solved

Excel 2007 Trusted Network Location not running VBA macros

Posted on 2013-12-02
9
1,523 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 150 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
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

 
LVL 81

Assisted Solution

by:byundt
byundt earned 150 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 200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article is a collection of issues that people face from time to time and possible solutions to those issues. I hope you enjoy reading it.
This article explains the fundamentals of industrial networking which ultimately is the backbone network which is providing communications for process devices like robots and other not so interesting stuff.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

615 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