0

I use ActiveWorkbook.Lockserverfile procedure in excel 2016

When I run my macro and the file with this procedure(ActiveWorkbook.lockServer) opened in another computer I get a an alert :

enter image description here

I want that "enter" button will be pressed automaticaly so that file will opened as read only.

I open for other suggestion like turn off the alerts (didn't work)

Community
  • 1
  • 1
Igor Gol
  • 9
  • 2
  • 2
    use `ReadOnly` argument in [`Workbook.Open`](https://msdn.microsoft.com/en-us/library/office/ff194819.aspx) method. Always set to True and that pop-up will never happen. – Scott Holtzman Jun 15 '17 at 15:01
  • 1
    `SendKeys` could in principle work but such solutions are fragile. It is better to follow the advice of @ScottHoltzman and avoid the problem entirely. – John Coleman Jun 15 '17 at 15:09
  • Just as an idea could application.displayalerts=false also work? – 99moorem Jun 15 '17 at 15:13
  • @99moorem The last line of the question suggests that OP tried that but that it didn't work. – John Coleman Jun 15 '17 at 15:15
  • @JohnColeman Ahh did not see that fair point then. I would go with scottholtzman's suggestion then – 99moorem Jun 15 '17 at 15:15
  • @Scott Holtzman - I need to open this file to edit always, if it read only cause someone use it is fine but as a default I need open for edit. other options? – Igor Gol Jun 15 '17 at 15:36
  • You have contradictory statements @IgorGol - If you always need to edit it then I suggest placing a function to check if the file is already open before trying to open. And if it is open, alert user so they can wait for it to be closed (or ask user to close it) before trying again. – Scott Holtzman Jun 15 '17 at 16:00
  • @SCott holtzman - can you write in code your suggestion ? and I will try it – Igor Gol Jun 15 '17 at 19:42
  • @IgorGol - This [answer](https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open#answer-9373914) will help. – Scott Holtzman Jun 15 '17 at 19:47
  • @Scott Holtzman - Hi, I tried some of the solutions in your link and it didn't work. Do you have an idea how to press in vba the "ok" button when this alert pops up? it will be a better solution – Igor Gol Jun 18 '17 at 07:23
  • @99moorem - tried it - application.displayalerts=false - not working – Igor Gol Jun 18 '17 at 08:45
  • The better solution than that is using the answer from the link I provided to find out if its open already. If so, open with ReadOnly property set to True. Otherwise, open normal. For clarity, you could try the `FindWindow` API, but that is much more challenging to implement. – Scott Holtzman Jun 18 '17 at 12:05
  • @Scott Holtzman - I Tried these solutions but in excel 2016 it is not working becasue microsoft changed the logic. All the files opened as read only as default so the code output is that all files are read only :((((( – Igor Gol Jun 18 '17 at 13:20
  • Hi, First of all I wanted to edit the files and not read only. if file is locked on another station i want to open it to read only. The problem with openning to editing I solved with ActiveWorkbook.Lockserverfile The second problem with pop up I solved with Setkeys - > I sent "Enter" when pop up shows Thanks! – Igor Gol Jun 19 '17 at 10:26

1 Answers1

-1
Sub OpenReadOnly()
    Dim NewWb As Workbook

    ' change to your file path
    WbFullName = "C:\Users\Omerp\Desktop\test1.xlsx"

    Set NewWb = Workbooks.Open(WbFullName, ReadOnly:=True)
End Sub
lebelinoz
  • 4,890
  • 10
  • 33
  • 56
Omer Pesach
  • 9
  • 2