CODEWITHSUNDEEP
excelvba
Anu
Anu

Reputation: 1129

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

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.

Upvotes: 0

Views: 882

Answers (2)

Jochen
Jochen

Reputation: 1254

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)

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166316

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

Upvotes: 1

Related Questions