Open an excel file read only but open with different file name

Question

Scenario

I have a file that I am opening as ReadOnly using the following code.

Set wbRead = Workbooks.Open(FilePath, ReadOnly:=True)

Here the FilePath is a variable that tells the file location of that file

Problem

The issue I am facing is, if the user run macro second time without closing this already opened readonly file, it is giving runtime error due to having similar file name opened

What I need

Is there any way whereby excel can open a file as readonly, but the opened file shows some random name? Eg: Actual file name is A. But when excel open it as readonly, it open as A123? 123 is like a random number.

No. The only way this could be done would be to actually rename the file before you open it, which you won't be able to do because Excel has it open. But you're asking the wrong question anyway. Your question should be How can I stop my users from running my macro twice while the file is still open? — Ken White
– Ken White, Commented Oct 15, 2018 at 2:49
Would you really want them to be able to open the same file twice? — Tim Williams
– Tim Williams, Commented Oct 15, 2018 at 3:01
@KenWhite Users need to run the macro as many times as they want. Currently they have to save the read only file to somewhere and close it before they run second time which they don't prefer. So the question is correct. Just that if there is a way to do it. — Anu
– Anu, Commented Oct 15, 2018 at 3:27
@TimWilliams Yes. Not only twice. As many times as they want — Anu
– Anu, Commented Oct 15, 2018 at 3:27
Copy the file to the temp folder under a random name, and open the copy. — Tim Williams
– Tim Williams, Commented Oct 15, 2018 at 4:29

Jochen · Accepted Answer · 2018-10-15 09:04:10Z

2

Abother Solution would be to always (open or not) use Workbooks.Add to create a new copy of your file. Excel will automatically prompt you to save under a new name when you close:

Set wbRead = Workbooks.Add(FilePath)

answered Oct 15, 2018 at 9:04

Jochen

1,2541 gold badge7 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2018-10-15 05:28:15Z

1

If the file is already open, make a copy in the temp folder under a different name, and open it from there.

Sub OpenFile()

    Const fPath As String = "C:\users\tim\desktop\tmp.xlsm"

    Dim fso, wb As Workbook, fName, p
    Set fso = CreateObject("scripting.filesystemobject")

    p = fPath
    fName = fso.getfilename(p)

    On Error Resume Next
    Set wb = Workbooks(fName)
    On Error GoTo 0

    If Not wb Is Nothing Then
        p = fso.GetSpecialFolder(2) & "\" & Round(Rnd() * 1000, 0) & "_" & fName
        fso.copyfile fPath, p
    End If
    Workbooks.Open p


End Sub

answered Oct 15, 2018 at 5:28

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

2 Comments

Anu Over a year ago

I tried this code, but it is not working. It works only when I change If Not wb Is Nothing to If wb is Nothing. By the way if use this method, will some objects be saved in memory? Because my application is a memory hungry type and I need to make sure memory is not using un-necessarily.

Tim Williams Over a year ago

Files will only be in memory as long as they’re open

Collectives™ on Stack Overflow

Open an excel file read only but open with different file name

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related