Macro Code - Open As Read Only

Jan 21, 2009

below code


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 ?

View 9 Replies


ADVERTISEMENT

Code To Open A Read Only .xl File

Jan 11, 2007

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.

View 5 Replies View Related

Macro Coding: Add A Code To The Sort And Paste Macro That Will Open The Second Spread Sheet

Jul 21, 2007

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

Windows("estimate sheet one.xls").Activate

View 2 Replies View Related

Open Workbook As Read-only?

Jan 12, 2013

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?

View 11 Replies View Related

Open File Only If Not Read-Only

Jan 16, 2010

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.

2. Check When the file is Closed And Open it

View 3 Replies View Related

Workbook Open In Read Only

Jun 16, 2014

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?

Code:
Application.ScreenUpdating = False

Workbooks.Open FileName:=ThisWorkbook.Path & "iSS.xls", ReadOnly:=True

Application.ScreenUpdating = True

ThisWorkbook.Close False

View 1 Replies View Related

Open Read Only File

Oct 7, 2008

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.

View 9 Replies View Related

VBS To Open Excel File As Read Only?

Jan 31, 2014

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.

View 5 Replies View Related

VBA - Open File NOT In Read Only Mode

Nov 27, 2011

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.

The file open command is the following:

Workbooks.Open Filename:=sourcePath & sourceFileName

I searched the entire project and appended ", readonly:=false" to every open command. So, the command became

Workbooks.Open Filename:=sourcePath & sourceFileName, readonly:=false

Still, Excel opened the file in read only mode as seen in the title bar (filename [read-only]).

If I double-clicked on the file and opened it, it's NOT in read only mode. I checked the property of the file. It is NOT read only.

View 2 Replies View Related

VBA Code To Run Macro In All Open Workbooks

Mar 12, 2014

I have a macro to get copy of sheet named "Doc Info" from workbook File 1 to active workbook.

I could do it for one file on any active workbook.

But what I would require is, upon executing this macro , i want this macro to get executed in all open workbooks( could be any numbers ).

I want to move copy of sheet from File 1 to all open workbooks ( which i am doing it manualy for every file )

All these open workbooks could be from any folder , wont be in same folder.

So logic is to execute my macro apply in all open workbooks in my computer.

Below is the code and i have attached file for test

HTML Code: 

Sub Copysheet()
Dim wSht As Worksheet
Dim wBk As Workbook
Dim wBk1 As Workbook

Set wBk = ActiveWorkbook 'Workbooks("File 2.xls")
Set wBk1 = Workbooks("File 1.xlsm")
Set wSht = wBk1.Sheets("Doc Info")

wSht.Copy before:=wBk.Sheets(1)

End Sub

View 2 Replies View Related

Open A Pdf File In Macro Code

Aug 11, 2004

how to open a pdf file (located: "C:myfile.pdf") in macro code?

View 4 Replies View Related

Open Web Page Via Macro Code

Feb 24, 2008

I have been looking for some VBA code that simple, when it runs, opens a browser and a specific, hard coded website. Is this possible in VBA?

View 2 Replies View Related

Open Web Page Macro Code

Mar 5, 2008

I am currently using the following code in order to open a web page by clicking a button:

Private Sub Timetable18_Click()

ActiveWorkbook.FollowHyperlink Address:="http://journeyplanner.tfl.gov.uk/user/AHF/JP15__00006e8e.pdf"
NewWindow = True

End Sub

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.

View 4 Replies View Related

Open/Add New Workbook Macro Code

May 12, 2008

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.

View 2 Replies View Related

Open Workbook Within Zip File & Not Have Read-Only Status

Aug 12, 2009

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?

View 2 Replies View Related

Macro Code To Work Between 2 Open Workbooks

Jun 4, 2008

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

current_wb.Activate
current_wb.Worksheets(1).Select
wbkFrom.Activate

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.

View 4 Replies View Related

Open Hyperlink In Cell With Macro Code

Aug 7, 2008

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.

View 2 Replies View Related

Password Protected Excel File - Can Only Open As Read Only

May 31, 2013

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.

View 3 Replies View Related

Remove/Disable Open File As Read Only Option

Apr 9, 2008

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.

View 8 Replies View Related

2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others

Jan 27, 2009

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..

View 2 Replies View Related

Read The 1st Character In The Hex Code

Dec 8, 2008

I have a set of Hex Code a total of 4 Sets :

A4EC 9ACF 4A02 044E

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

View 4 Replies View Related

Code To Read A Formula As String

May 19, 2009

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?

View 5 Replies View Related

Code Opens Files As Read Only

Apr 19, 2007

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"...............

View 3 Replies View Related

Only Execute Code On A Write-access Basis, Not Read-only

Jul 6, 2009

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.

View 3 Replies View Related

Files Saved To Program Files Folder, Open As Read Only

Feb 8, 2009

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)?

View 5 Replies View Related

Abort Workbook Open If Workbook Is Read Only.

Dec 12, 2009

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.

View 2 Replies View Related

File May Be Read-Only, Or You May Be Trying To Access A Read-Only Location

Jan 4, 2007

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?

View 2 Replies View Related

Run Macro Only If Workbook Is Read-Only

Jun 11, 2009

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.

View 2 Replies View Related

Skip To Next Macro, If Read Only Is Available

May 30, 2007

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.

View 9 Replies View Related

Macro -open The Look In List” And Stop So I Can Pick A File To Open

Oct 18, 2008

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

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved