Sub Collect_OS_Data()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim fso As Object, fld As Object, fil As Object, fldPath As String, wbSrc As Workbook, wbCur As Workbook
fldPath = "Q:PublicPAYMENTS Q&RREPORTSSuspense Activity BUSINESSOngoing - Suspense BUSINESS - Activity Reports"
Set wbCur = ActiveWorkbook
ActiveSheet.Name = "Raw Data"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fldPath)
On Error GoTo ErrHandler
For Each fil In fld.Files
This opens all spreadsheets within a folder and takes the data i require out of each one and pastes to a new workbook, however the files are password protected. Can you add into the code to open each spreadsheet as a readonly copy and paste data and then close without a save prompt or denying the save prompt ?
I have a read only excel file which I want to interrogate in another report.
I'm writing some code to open the file but I'm not sure how to get past the password/read-only prompt when opening the file.
My code is:
Workbooks.Open Filename:= "C:Sales2006.xls"
I assume it's possible to write some code to get past the prompt. I have checked the help files which do seem to indicate it's possible to specify a read only property but it doesn't say how.
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
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?
1. Check if file is already open a) If Not then Open the File b) If It is Open then Show Message That File is Open And Will Load Once the user close it.
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 workbook set up with hidden worksheets available only with a password. What I would like to do is set this up so that those employees who have the password will have full rights to make changes, while those who don't have the password will have "read only" rights. It should always open as a "read only" file, and then change to full rights when you enter the correct password.
I've also set this up so that it cannot be printed; can I also set it up so that it cannot be saved anywhere on our local drive?
I have the macro set up to limit the access via a password, but don't know where or how to set up these other changes.
I have a excel file that opens and will automatically run a bunch of tasks if a certain user opens it. This user is only used to automatically run this excel file, other users need to open the file occasionally to edit emails address, add clients etc stuff like that, that the automation part of it works off.
Currently I have this user running the file every hour using Win7 Task Scheduler. This is working well, except for the time when another user is editing the file on the hour and of course the Automated user gets the "this file is open blah blah blah, open as read only, cancel etc" popup, this stalls everything, and if I dont notice it, it could sit like this for days.
The file runs in Read Only fine, so... Basically my question is, how to open this file as "Read Only" using Task Scheduler? As if it just ran as Read Only all the time then it wouldnt matter what other users were editing at any given time.
One of my ideas was to have Task Scheduler run a VBS script instead of running the Excel file directly, and having the VBS script simply load the Excel as Read Only.
I have a VBA script that has been running fine for months on one computer. Today, I ran it on another computer and it opened files in read only mode which disrupted everything.
However, when this is executed it returns the following error:
Run-time error '-2146697210 (800c0006)': Method 'FollowHyperlink' of object '_Workbook' failed
Clearly, I'm doing something wrong. I wondered if it had anything to do with the fact that the webpage I'm trying to open is a pdf and not html or similar, but this doesn't appear to be the issue.
I am trying to write code to open a new (blank) workbook while in an existing workbook (I am then going to pass data between the two which is easy). When I try to record the code to get the syntax for opening a new workbook it will not record any code. I also need to name the new workbook based on text in a cell in the existing workbook. I just saw before posting this that the code;
workbooks.add
will open a new workbook now I just need to name the new workbook based on text in my existing workbook.
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 am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here
Set current_wb = ThisWorkbook Dat_Fil = Application. GetOpenFilename Workbooks.Open Dat_Fil Set wbkFrom = Application.ActiveWorkbook
NOTE-Workbook object variables are declared outside any macro, and as "public." Code here
Public current_wb As Workbook Public wbkFrom As Workbook
In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error: "object variable or With block variable not set" for any of the following statements in the second macro
I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.
how to open a Hyperlink directly. I have writen some code which allows me to copy over certain cells from a large list of hyperlinked files. However I am struggling to figure out how to open the hyperlink without actually writing the hyperlink address into the code. The hyperlinks are the file names and not the file paths. In essence I'm aiming to just get the macro to "click" the hyperlink to open it. Once the file is open the rest of my code will work, it is just this one sticking point.
I've got a spreadsheet that has password protection before you can "modify" it. All of a sudden today, when I open the spreadsheet and enter the correct password, it still opens but only as Read Only.
Is there a way to remove the option to open a file as read only when the file is already open by another user? Ideally when a user tries to open a file that is already in use they would get a message to that effect and the only option at that point would be for them to select "notify" when file is available.
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
1. What i need to do is Read the 1st Character in the Hex Code which say A now.
2. Now I need to find if there is another A in this Hex Code. If yes - e.g A4EC 9A - I need to find the difference between A-A. How many characters are in between the 2 A-A. So for this I have A 4EC9 A so the difference is 4. 2.1 - Another Senario of "A" - A 4EC9ACF4 A the difference is 8 characters
3. Next we will move on to the next Letter which is 4 and do our search.
The key is i only want to record those Hex code which have a difference of 4,8,10,12 differences.
So for that above example I should be able to record the following :
A 4EC9 A == 4 letter difference 4 A020 4 == 4 letter difference A 4EC9ACF4 A == 8 letter difference 4 EC9ACF4A020 4 == 10 letter difference E C9ACF4A02044 E == 12 letter difference
I am using the following code to read a formula as a string. But sometimes the use r may forget to enter formula in the particular cell. In this case I want to check if the first character of the string is a Equal-to Sign (=). In case it is then macro proceeds further otherwise a message prompts user to enter a formula first. how to check if the first character of the string is a '=' sign?
why the below code opens files as read only? What this code does is open all worksheets, in four different folders, whose filenames include the desired date. For example, if i want all worksheets for the 19th, then K117819, k127819, k217819 and K226119 all open. Except they open as read-only which requires saving to a different location and then renaming - which sort of defeats the time saving reason for running the code in the first place.
Public Sub Auto_Open() Dim sCurFile As String Dim sPath As String Dim mpath As String MsgBox "This tool will print all four production lines line report total sheet for any month and day in 2007" mpath = InputBox("Enter the month i.e. 01 for January", "print") fpath = InputBox("Enter day of the month to print (use leading 0's)?", "print") Application. ScreenUpdating = False Application.DisplayAlerts = False Application.AskToUpdateLinks = False 'Get the path sPath = "t:2007" & mpath & "k11"...............
I have recently used a before_close event on this workbook to save a backup of the open file to another location on my system. This works fine but I was wondering if there was some more code I could add to only execute this event on a write access basis.
The file I use can be viewed by anyone on the network as read-only and only certain users with a password can edit/update with a write access password.
The backup event is use executes every time the document is closed be it read-only or write-access.
Ideally I would like to add some code to only execute this backup if the file is opened on a write-access basis.
I have an Excel application (Excel 2003) which is stored in the default Program Files folder by the Installer; for example: c:program fileszxchello.xls. The problem I am running into is this file opens as Read Only in Vista and this is interfering with the running of the application. There is no problem opening the file normally in Windows XP.
I have been able to narrow down the cause of this to the User Account Control system in Vista - if I turn OFF User Account Control, the Excel file opens normally and my application functions normally. Is there another option to open the Excel file without turning OFF User Account Control because some users may find it unacceptable to turn OFF this security feature. Ofcourse, one option is to install the application in another location, outside the Program Folder, and the file would open normally, but the Packaging Wizard that I am using to package the application does not allow me to install the application in any other location and thus, the application installs in the Program Folder and I am running into this problem of the Exel file opening as Read Only. Is there a way out of this situation where I can open the file normally (not as Read Only)?
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'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 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 been using various bits of information from all sorts of threads and made a somewhat nifty little Macro program for the company i work for.
The following threads may have my answer in them, but I can't see it or figure out how it could be restructured to my needs.
Disable Read-only Message Box
looks as if the file is set as readonly then to close it without saving
[url] [url]
contains a msgbox I would like to add in, specifying that the file is read only therefore to abort current macro for particular file
concerns very much the problem I am having.
Basically what it boils down to is,
My Program Manager runs a daily report, then uses my program to update up to 7 different workbooks. Each having their own Sub program.
Some of the Supervisors or leads gets in to early and thus makes the file as a Read-Only, which of course stops my program and can not continue.
What I would like to learn is.. (from the countless threads I have looked through, I can't seem to find the answer)
Bring up a msgbox, informing the PM that a specified file is already open and therefore this particular process has been aborted.
Then it will move on to the next Sub, and so forth...
I want to thank everyone for their help directly or indirectly, (been reading threads for about 9 months now) and any future help as well.
And if it's not to difficult, could it be close to layman terms? I try to understand alot of these codes and I get lost with some of the wording. I am just pratically piecing pieces together with my "eyes closed" and somehow come out with a working VB codes.
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box
