Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,907
- Office Version
- 365
- Platform
- Windows
Hi All
I'm trying to force a particular file to open in its own dedicated instance of the Excel application. The macro below checks how many other files are open (by counting visible windows). If no other files are open then no problem. However, if there are other files open then it needs to re-open in a new instance and then close ThisWorkbook. This bit is ok but (naturally) the file opens in read only.
Does anybody have an idea how to make the file open in write mode. I tried adding a SaveAs statement to save over itself but it won't allow it. The auto_open macro isn't triggered when the file is reopened with this macro.
I'm trying to force a particular file to open in its own dedicated instance of the Excel application. The macro below checks how many other files are open (by counting visible windows). If no other files are open then no problem. However, if there are other files open then it needs to re-open in a new instance and then close ThisWorkbook. This bit is ok but (naturally) the file opens in read only.
Code:
Option Explicit
Sub auto_open()
Dim win As Window
Dim lngCount As Long
For Each win In Application.Windows
lngCount = lngCount - win.Visible
Next win
If lngCount = 1 Then GoTo Finish
With CreateObject("Excel.Application")
.Visible = True
'.DisplayAlerts = False
Call .Workbooks.Open(ThisWorkbook.FullName)
End With
ThisWorkbook.Close False 'this should still trigger the before_close routine
Exit Sub 'don't actually need this
Finish:
'//call User Interface routine here
ThisWorkbook.SaveAs ThisWorkbook.FullName 'won't trigger if file is opened through this routine
End Sub
Does anybody have an idea how to make the file open in write mode. I tried adding a SaveAs statement to save over itself but it won't allow it. The auto_open macro isn't triggered when the file is reopened with this macro.