For a couple of reasons I have had to take a workbook off of a local machine and place it on the network so others can access it. I cannot share the workbook because of the macro's and protection on it. At any one time only one person should have full control of workbook. The others only need read only access. I won't go into the whole deal but I was hoping there was a way on startup to detect if the workbook is in read only mode or not (Other than the basic notifications and indications that excel gives as many users either ignore or do not understand them). If so I would like a big message to popup stating this fact to the user and also if possible show them who currently has it open. Lastly, they would have a cancel workbook open option until they get the other user to exit.
I've got several workbooks that users will be using to keep track of individual projects. These workbooks will talk back to a master summary sheet that has the status of all projects on it. The possibility exists that someone else on the network will have the summary book open when the program tries to update it, which would cause an error when it opens as read only. So I want to do something like this.
I am running a call centre with 20 users at peak time. I have 1 user who will always have a master workbook open and saving throughout the day. The remainder of the users during calls will need to open this file as read-only to simply view the latest saved version.
This works however sometimes is takes a over a minute for the file to download from the server for the read only box to appear. I was wondering if there was a way for the other users to view it in a simpler and quicker way?
Also issues with the 1 user saving the file which others have it open as read only?
I have created an Add-in for use across the business, the add in calculates financial impact based on figures that are stored on the shared drive.
The addin currently opens the workbook, and sets the variables to figures contained in the workbook - and then closes the workbook.
To roll this out to the business - I need to get the add-in to open the workbook as read Only, so that it will allow multiple users to access at once. I have set to open as read only in the past - however this prompts me to save the workbook as something else.
Is there any way I can get this to just open as read only, do what it needs to - and then close; without having the option to save as something else?
I was wondering if there is a way to set a macro to run on an event only if the workbook is read-only. The macro I have set up right now reformats the toolbars to a custom user friendly one, but I want to be able to open the document with a password and not have the toolbars rearange on me.
I have a file that's only instruction is to open another workbook in Read Only; this is due to not having network privileges to make the entire folder Read Only.
I'm getting one user on the network where the Workbook.Open script doesn't fire up automatically and I can't see why. Then ocassionally i'm getting users where the file hasn't closed itself after the file has been opened.
Is there any obvious reason for both of these happening?
I have a shared Excel Workbook, and I am trying to prevent a non-permitted user from making any changes to it. I have managed to hide all of the worksheets bar one - The Warning Page - if the user is not one of my chosen users, but I am having a little trouble setting the worksheet to open as "read-only" if the user is not one that I have listed.
My macro code looks a bit like this:
If Application.UserName = "Vikki" Then Sheets("Warning!").Visible = False Sheets("Total").Visible = True Else Sheets("Warning!").Visible = True Sheets("Total").Visible = False ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly End If
But when I am using this, a non-user will get a msg box asking if they want to save the workbook before changing to read only. Can I prevent this msg box from appearing?
I have a workbook which has a macro to loop round refreshing its data every half an hour and each time the refresh is performed the file gets saved. This file is opened by one user in the morning, and left open with the macro running to carry out the refreshing throughout the day. This workbook is then also opened by a number of users as read only who interrogate (but do not update) the data in the spreadsheet as part of their work. Periodically, they close and re-open their copy of the workbook to ensure they have the most up-to- date version open.
To let them know when a new version is available (i.e. when the refresh has been performed and the file re-saved), I have some code which compares the last saved date/time of the file they are using with the date and time of the file they loaded the file from, and displays a message when they values are different. I have, however, been unable to develop a macro which properly 're-loads' the workbook. I have been able to get the file closed and the latest version opened (through a 'ThisWorkbook.Open' statement). However, when the new version susbsequently opens, none of the open events are triggered, nor is the Auto_Open macro executed. This is a major problem, as the spreadsheet relies on this processing being performed for the file to properly work. (I know that macros are enabled (I get the 'enable macros' prompt in the course of the updated version being opened, and can run macros once the file has been opened).)
I have a Workbook named "Master Archive" that contain data from 9 workbooks. This is all done with a macro upon opening each workbook and using a command button to send the data to it. Question is I want everyone to be able to view the Master Archive Workbook in a readonly fashion and the orginal to always remain closed. Reason being is I cant run the macro to archive the data if someone has it open, which then would cause debug issues with the code.
Are there settings were I can be the only one to open the workbook and be able to Edit.
Also Everyone has a shortcut to this Master Archive on there Desktop.
I have completely forgotten what I need to enable to get this to work
I have one spreadsheet indexed to another. When one user opens their s/s and changes a value and then closes it I need the other s/s to update once they open and refresh.
I have created a Hlookup to read a workbook that has several tabs. How do I write the formula to read every tab. So if my tabs are labelled like this... FY 2012, FY 2012-2, FY 2012-3 - each spreadsheet is formatted the same, same number of rows and columns but I need the formula to look on every sheet and then bring back the information.
I am trying to create a "macro" that will read values from one workbook into an array based on a key field. If the key isn't present the cells data is not to be stored. After the records are stored i want to test them agains a range in a differnt workbook. If the data compared matches a stored value, leave it alone. If there is no match, a cell needs to be added with a value stating a differnce.
Like i stated i am new at this and have no clue how to compare an data array to a field. Let alone read the values into the array.
I have one excel workbook which has 10 rows of data. I need to access another excel wb which has 10 rows of data and get the variance. The key here is wb 1 has the data as I manual gather it and it changes very often. WB 2 also changes to equal the number of rows in Wb 1.
A> I would like to call Wb 2 from Wb 1 and copy those 10 B> It should be able to handle change in the number of rows in WB 2 C> It should be able to call workbook 2 irrespective i name it differently.
I've recently discovered the usefulness of VB in excel and have managed to write some basic macros that enhance many of my workbooks. BUT, I've bumped into a problem I can't seem to solve. Please help!
I'm looking to import the values in a large range of cells ( 5 x 5000) in a closed workbook into a range that i specify in an open one, where both workbooks are housed in the same directory. I also want to include logic that allows me to move the 2 files to different directories (the names will never change, only the paths) and have the code still work--I believe i'd use relative path references?
I've found lots of snippets of code on the topic but can't seem to get any of them to work. For instance: http://spreadsheetpage.com/index.php...a_closed_file/
Problem is, I'm so new that I don't even know where to begin pasting the code (objects vs. modules, etc) in the VBA editor.
I have a pair of macros that together close and reopen a workbook when the user clicks on a button labelled 'Refresh'. The reason for the macros is that any one of multiple users may have the workbook open for editing while the others have it open read-only, and those who have it open read-only will want to refresh it periodically to make sure they're viewing the most up-to-date version.
Now, here's the hitch. If I have the workbook open read-only and refresh it while no-one else has it open for editing, it doesn't reopen read-only (it reopens for editing). But I don't want it to reopen for editing (I want it to reopen read-only). Is there anything I can add to the macros to make the workbook reopen read-only, or is there a different pair of macros I can use? Here's what I have at the moment:
Macro 1 Sub CloseMe() ' Application.OnTime Now, "OpenMe" ThisWorkbook.Close SaveChanges:=False ' End Sub
I understand that vba can determine if the current file is read-only using the following to return true / false. GetAttr(ThisWorkbook.Path) And vbReadOnly
which seems to work fine for determining if the file is read-only, as there is another instance of the file already open. However when opening a file straight from a zipped directory (i.e. not extracting the file first), Excel opens the file as read-only. The above code returns false in this instance, i guess because the workbook path has a .zip ending rather than the normal .xls etc endings. how to return true that the zip file is read-only?
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming - Converting to a different file format (didn't work, it won't let me) - Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
I've created an add-in. When I try to update the coding in the add-in, Excel reports that the add-in is read only and prompts me to choose another name for it. However, it is not currently selected in my Tools -> Add-ins... window.
Lately I've been learning how to pluck data right from a web page:
for instance, cageNum = IE.document.getElementById("txtCage").Value will read the info right out of the txtCage textbox at the page where my IE object is.
Now I'd like to read a table into excel. How is that done?
I've got an excel sheet from a guy at work who decided to lock the entire document down so I can't do anything with it. Virtually all the ribbon options are greyed out, there isn't anything locking it in the code, I've tried checking the save as "read-only" option and it's not there. How I can actually save this is editable? I've looked in windows explorer and it's not read-only in there either. I tried save-as and even tried making it an XLS instead of an XLSM and it still won't let me edit it
The xls file I have written is used by multiple users at the same time. None of them can actually change the file - it opens data files and imports the data it needs then writes back any changes to the data to those files.
So the "read-only" option that my users see is of no importance to them - can this be bypassed somehow?
