[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Issue with Macro Enabled workbooks

Posted on 2011-10-20
24
Medium Priority
?
313 Views
Last Modified: 2012-05-12
(Standard XLS) If the workbook has Macros and is opened in Excel 2003 it shows up Read Only but if it’s a standard workbook it opens in normal mode. I turned all the macro security settings as low as they could go checked to see if the file had read only pegged anywhere nothing. Any ideas?
0
Comment
Question by:mattparrill
  • 11
  • 9
  • 2
  • +1
23 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 37001151
can u post the workbook ?
gowflow
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37001452
It is possible to save spreadsheets so they can only be opened as read-only.  

If you "save as" the read only spreadsheet after opening it, and then save it as a normal spreadsheet, making sure you check the security options when the sheet is open and they are macro friendly that should work around the read only setting.

Let me know if that doesn't solve it for you!
-SA
0
 

Author Comment

by:mattparrill
ID: 37017953
SA I need the workbook to not be read only even doing a save as to create a new file still the workbook will show up read only my client needs to write to the file. gowflow Its all workbooks with macros not just one and it has financial data so I cannot post sorry.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37018190
It is possible to make a file read only from windows.  If you go to the file in windows explorer and check the properties, does it say read only there?  If so unclick and see if that soves the problem.

0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37018205
A final thing you may want to try, it may be that you don't have the proper folder permissions to read and write.  

Try copying the file to your desktop, and then trying the above steps.
0
 

Author Comment

by:mattparrill
ID: 37020007
I have copied the file to local desktop same results. I have given the file the Everyone and the file does not have read only checked. Keep in mind this is happening to more than one file.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37020377
Ok,  I don't think that there are any settings in Excel 2003 to make all macro enabled worksheets open in a specific way.

I think that is saved in each workbook.  You might want to look for something like this in the vba of the worksheet:

Workbooks.Open Filename:="C:\Documents\MyFile.XLS", ReadOnly:=True

Open in new window


If you can't find that code or something similar:
If this is happening on a bunch of files, it makes me wonder about how Excel is being invoked.  

Or if there is some anti-virus program installed on the workstation that is forcing macro enabled worksheets to open as read only.

Hope one of those works for you... I'm out of ideas.
0
 

Author Comment

by:mattparrill
ID: 37024087
Well you take the same workbooks and try it on different machines and they open just fine. I'm not sure what could cause this myself.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37031241
Ok,

Well I've done a little looking around, and it appears that there is one more thing that can cause this.  If this file was originally created on a later version of Excel and saved with features that are not supported by 2003.  

I would make sure that you have all the updates for 2003 installed on this workstation.  SP3 and the file format converter plus the compatibilty pack SP1.  

And then open the file on the workstation that originally created it and make sure that you aren't including any unsupported features.

-SA
0
 

Author Comment

by:mattparrill
ID: 37038836
Hey thanks for the idea it was on SP3 but hadn't installed compatibility pack just yet they are in 2003 format but though it was worth a shot to have that compatibility pack on there and its service pack still showing up in read only though
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37039119
what is the OS and excel version on the machine it can be opened on vs the machine it can't be opened on?
0
 

Author Comment

by:mattparrill
ID: 37046741
they are identical windows 7 64 bit office 2010 with a sub copy of excel 2003 installed because of formatting issues with Peachtree the other machine runs with no issues.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37047622
Two more ideas:

This type of thing can be set in a group policy.  And check your registry settings between the working and non-working computers to confirm they are identical, as you see in the text below you can have things managed via those settings that don't appear in Excel for the average user.  Perhaps one of those is the culprit.  

Best of luck!  

-SA

See here :
Important These steps may increase your security risk. These steps may also make the computer or the network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you decide to implement this process, take any appropriate additional steps to help protect the system. We recommend that you use this process only if you really require this process.

By default, a user can decide whether to open the file when the warning message is displayed. However, the user-notification function can be set to any of the following levels by using a Group Policy setting or by using Registry Editor: •Display the warning message, and do not open the file.
•Display the warning message, and let the user decide whether to open the file (This is the default setting).
•Open the file, and do not display the warning message.
Use a Group Policy setting1.Download the 2007 Office system Administrative Template files (ADM, ADMX, ADML) and Office Customization Tool from the following Microsoft Download Center Web site:
http://www.microsoft.com/downloads/thankyou.aspx?familyId=92d8519a-e143-4aee-8f7a-e4bbaeba13e7

2.Extract the administrative template files to a folder. To do this, follow these steps: •Double-click the AdminTemplates.exe file.
•Accept the Microsoft Software License Terms, and then click Continue.
•Select a folder in which to save the extracted files, and then click OK.
•Click OK after the files have been extracted successfully.

3.Open the Group Policy Object Editor.

For more information about how to open the Group Policy Object Editor, visit the following Microsoft TechNet Web site:
http://technet2.microsoft.com/windowsserver/en/library/03ec122f-fc65-496e-ad0d-4fd22a96a4bb1033.mspx

4.Expand User Configuration.
5.Right-click Administrative Templates, and then click Add/Remove Templates.
6.In the Add/Remove Templates dialog box, click Add.
7.Browse to the folder that contains the Administrative Templates that you extracted in step 2c.
8.Click the Excel12.adm file, and then click Open. The Excel12.adm file is located in the following folder to which you extracted the Administrative Templates in step 2c:
\ADM\languageID
Note The English language administrative template files are located in the EN-US languageID folder.
9.Click Close to close the Add/Remove Templates dialog box.
10.Expand Administrative Templates, expand Microsoft Office Excel 2007, expand Excel Options, and then click Security.
11.On the Extended tab, double-click Force file extension to match file type under Setting.
12.On the Setting tab, click Enabled, click one of the following in the Force file extension to match file type drop-down list, and then click OK:¿Allow different
¿Allow different, but warn
¿Always match file type

13.Close the Group Policy Object Editor.



Alternatively
Use Registry Editor

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.1.Exit Excel 2007.
2.Start Registry Editor.¿In Windows Vista, click Start



, type regedit in the Start Search box, and then press ENTER.





If you are prompted for an administrator password or for a confirmation, type the password, or click Continue.
¿In Windows XP, click Start, click Run, type regedit, and then click OK.

3.Locate and then click the following registry subkey:
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security

4.On the Edit menu, point to New, and then click DWORD Value.
5.Type ExtensionHardening, and then press ENTER.
6.Right-click ExtensionHardening, and then click Modify.
7.In the Value data box, type the value data, and then click OK.

The following list contains the value data settings that are appropriate for the ExtensionHardening setting:¿0: Do not check the file name extension and the file type, and bypass the function of the warning message.
¿1: Check the file name extension and the file type. If they do not match, display the warning message.
¿2: Check the file name extension and the file type. If they do not match, do not open the file.
Note The default value data is 1. When the value data is set to 1, the behavior becomes the same as when no registry value is set. When the value data is set to 0, the file name extension and the file content are not checked in all situations. We do not recommend bypassing this function.
8.On the File menu, click Exit to exit Registry Editor.

Back to the top


STATUS


 This behavior is by design.
0
 

Author Comment

by:mattparrill
ID: 37069393
Hey SA haven't had a chance to try this yet but hopefully I should today I'll let you know the results Thanks!
0
 

Author Comment

by:mattparrill
ID: 37076652
Hey SA tried the idea you had yesterday with no success. If you have anything I'm willing to listen I really thought that might fix it this last time. Thanks for all the help
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37077141
Sorry,  that's all I've got, Have you tried a fresh install?  

Is there anything in the bios of the one computer that would be different then the other?  
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37077279
The only other thing I can think of is to check the files in the XLStart folder and see if you have any files in there that have vba that alters the way that files are opened.  

I use the personal.xlsb to control various things on my pc's.  It's possible that someone put something in that folder for you that prevents you from opening things properly.  

You'd want to check the vba on the sheets there, or just move them all to a different directory and see if opening the .xlsm files works properly (or macro enabled worksheets works after that).
0
 

Author Comment

by:mattparrill
ID: 37084856
Ya I just checked all of that and went ahead and deleted the startup items for excel so it would refresh still no luck their has to be a answer to this.
0
 

Author Comment

by:mattparrill
ID: 37100056
Still haven't found a fix for this if someone has an idea even its a crazy one please let me know!
0
 

Author Comment

by:mattparrill
ID: 37140286
This is still unresolved
0
 

Accepted Solution

by:
mattparrill earned 0 total points
ID: 37170553
Issue resolved re-installed office shared tools to correct the problem
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37188383
Good for you to have solved your issue.
gowflow
0
 
LVL 50
ID: 37419384
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

873 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