Create shortcut to open file Read Only

[Kentetsu]

Hello,

I've been searching all morning, but can't quite find what I need.

I've got a bit of code that creates a shortcut on the user's desktop. I need to modify it so that it creates a shortcut that opens the file as Read Only.

As you can see, I've tried a few variations (commented out) that all failed to work.

Option Explicit


Public Sub CreateShortCut()
Dim oWsh As Object
Dim oShortcut As Object
Dim sPathDeskTop As String
Dim r As Long

Set oWsh = CreateObject("WScript.Shell")
sPathDeskTop = oWsh.SpecialFolders("Desktop")

Set oShortcut = oWsh.CreateShortCut(sPathDeskTop & "\" & _
"Rack Tag & Track App" & ".lnk")

'ActiveWorkbook.Name & ".lnk")
With oShortcut

.TargetPath = "J:\ZZ  PII Ovens\Batch Ovens PC\Oven Logs Database\Rack Issue Reporting.xlsm"

'.TargetPath = "J:\ZZ  PII Ovens\Batch Ovens PC\Oven Logs Database\Rack Issue Reporting.xlsm" / r

'.TargetPath = excel.exe /r "J:\ZZ  PII Ovens\Batch Ovens PC\Oven Logs Database\Rack Issue Reporting.xlsm"

'.TargetPath = "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" / r "J:\ZZ  PII Ovens\Batch Ovens PC\Oven Logs Database\Rack Issue Reporting.xlsm"
'
'.TargetPath = "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" - r "J:\ZZ  PII Ovens\Batch Ovens PC\Oven Logs Database\Rack Issue Reporting.xlsm"

.Save
End With
Set oWsh = Nothing

MsgBox "Shortcut Has Been Created Successfully", vbOKOnly

Application.DisplayAlerts = False
ThisWorkbook.Close , False
Application.DisplayAlerts = True
End Sub

I'm not too sure on my Dim statement for r either (as long?).

Thank you...

 

[Jack in the UK]

I have never created a shortcut like this, but to open a file read only the code You require is

Workbooks.Open Filename:=FILE_TO_OPEN, ReadOnly:=True

 

[Kentetsu]

Yeah, that's still giving me a Syntax error.

Thanks for the attempt.

 

[Kentetsu]

Still working on this if anyone has any ideas. Thanks...

 

[Kentetsu]

Wow, there's so much information out there on how to create a shortcut to open a file as read only. But I can't find anything about creating that shortcut programatically.

 

[Kentetsu]

One more bump before I give up and try to find another approach. Any thoughts on this are greatly appreciated...

 

[Kentetsu]

Okay, Excel seems to be happy with the /r switch, but I can't figure what to declare it as. Everything I've tried comes up as Type mismatch. Any ideas?

Option Explicit


Public Sub CreateShortCut()
    Dim WshShell As Object
    Dim strDesktop As String
    Dim oMyShortCut As Object
    Dim Msg As String
    Dim r As String
        
    Set WshShell = CreateObject("Wscript.shell")
    If (WshShell Is Nothing) Then Exit Sub
    strDesktop = WshShell.SpecialFolders("Desktop")
    Set oMyShortCut = WshShell.CreateShortCut(strDesktop + "\Rack Repair Request.lnk")
    oMyShortCut.WindowStyle = 3  '3- Maximized 7=Minimized  4=Normal
    
    oMyShortCut.IconLocation = "J:\ZZ  PII Ovens\Batch Ovens PC\Oven Logs Database\PG\SaraLee.ico"
    oMyShortCut.TargetPath = "J:\ZZ  PII Ovens\Batch Ovens PC\Oven Logs Database\Rack Issue Reporting.xlsm" / r
    oMyShortCut.Save
    
Msg = "A Shortcut to the Rack Tag Application has been Created." & vbCrLf & vbCrLf
Msg = Msg & "You will find it on your Desktop."
MsgBox Msg, vbOKOnly

Application.DisplayAlerts = False
ThisWorkbook.Close , True
Application.DisplayAlerts = True

End Sub

 

[Kentetsu]

Solved! Well, sort of.

I figured out a work-around for this which gives the same results. Rather than have the shortcut open the Application, it instead opens an "intermediary" workbook.

The sole function of this intermediary is to open the App as Read Only, then immediately closes itself. It adds about 1 second to the start-up time of the App, maybe not even that much.

Getting Excel to open a file as Read Only is a piece of cake. Getting VBA to create a shortcut to open a file as Read Only, not so much. lol.

Here's the bit of code I used:

Option Explicit 

Private Sub Workbook_Open() 

Dim wb As Workbook 

Set wb = Workbooks.Open(Filename:="File Path\Application.xlsm", ReadOnly:=True) 
Me.Close , False 

End Sub

Maybe someone else can make use of this solution in the future...