Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
file In Use
Hi all,
I am using VBA to open a workbook on a network drive with following code: Sub OpenMyfolder() Application.DisplayAlerts = False Workbooks.Open("P:\MyFolder\MyBook.xls") Application.DisplayAlerts = True End Sub Code works fine however, if workbook is already open on network drive, you get a prompt to say file is in use with options to Open as Read Only, Wait or Cancel. My question is, can I prevent this message showing if File is already open? & if it is, default to open the file as ReadOnly? I have used Application.DisplayAlerts = False but this did not work. Also,I am not too sure how I should write the reuqired code to open as readonly in such event. Hope clear - any help / guidance appreciated. -- JB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
file In Use
Take a look at this function. The example is using MS Word but it should do
the trick for you. http://word.mvps.org/FAQs/MacrosVBA/CheckIfFileOpen.htm Steve "johnboy" wrote in message ... Hi all, I am using VBA to open a workbook on a network drive with following code: Sub OpenMyfolder() Application.DisplayAlerts = False Workbooks.Open("P:\MyFolder\MyBook.xls") Application.DisplayAlerts = True End Sub Code works fine however, if workbook is already open on network drive, you get a prompt to say file is in use with options to Open as Read Only, Wait or Cancel. My question is, can I prevent this message showing if File is already open? & if it is, default to open the file as ReadOnly? I have used Application.DisplayAlerts = False but this did not work. Also,I am not too sure how I should write the reuqired code to open as readonly in such event. Hope clear - any help / guidance appreciated. -- JB |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
file In Use
Steve,
thanks for response - I have tried using functions posted at various sites to test if File is Open - but still get the message File In Use! -- JB "Steve Yandl" wrote: Take a look at this function. The example is using MS Word but it should do the trick for you. http://word.mvps.org/FAQs/MacrosVBA/CheckIfFileOpen.htm Steve "johnboy" wrote in message ... Hi all, I am using VBA to open a workbook on a network drive with following code: Sub OpenMyfolder() Application.DisplayAlerts = False Workbooks.Open("P:\MyFolder\MyBook.xls") Application.DisplayAlerts = True End Sub Code works fine however, if workbook is already open on network drive, you get a prompt to say file is in use with options to Open as Read Only, Wait or Cancel. My question is, can I prevent this message showing if File is already open? & if it is, default to open the file as ReadOnly? I have used Application.DisplayAlerts = False but this did not work. Also,I am not too sure how I should write the reuqired code to open as readonly in such event. Hope clear - any help / guidance appreciated. -- JB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
file In Use
Function FileLocked(strFileName As String) As Boolean
On Error Resume Next ' If the file is already opened by another process, ' and the specified type of access is not allowed, ' the Open operation fails and an error occurs. Open strFileName For Binary Access Read Lock Read As #1 Close #1 ' If an error occurs, the document is currently open. If Err.Number < 0 Then FileLocked = True Err.Clear End If End Function Sub OpenMyfolder() Dim sStr as String sStr = "P:\MyFolder\MyBook.xls" if FileLocked(sStr) then Workbooks.Open sStr, ReadOnly:=True Else Workbooks.Open sStr End if End Sub -- Regards, Tom Ogilvy "johnboy" wrote in message ... Steve, thanks for response - I have tried using functions posted at various sites to test if File is Open - but still get the message File In Use! -- JB "Steve Yandl" wrote: Take a look at this function. The example is using MS Word but it should do the trick for you. http://word.mvps.org/FAQs/MacrosVBA/CheckIfFileOpen.htm Steve "johnboy" wrote in message ... Hi all, I am using VBA to open a workbook on a network drive with following code: Sub OpenMyfolder() Application.DisplayAlerts = False Workbooks.Open("P:\MyFolder\MyBook.xls") Application.DisplayAlerts = True End Sub Code works fine however, if workbook is already open on network drive, you get a prompt to say file is in use with options to Open as Read Only, Wait or Cancel. My question is, can I prevent this message showing if File is already open? & if it is, default to open the file as ReadOnly? I have used Application.DisplayAlerts = False but this did not work. Also,I am not too sure how I should write the reuqired code to open as readonly in such event. Hope clear - any help / guidance appreciated. -- JB |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
file In Use
thanks Tom - very helpful.
-- JB "Tom Ogilvy" wrote: Function FileLocked(strFileName As String) As Boolean On Error Resume Next ' If the file is already opened by another process, ' and the specified type of access is not allowed, ' the Open operation fails and an error occurs. Open strFileName For Binary Access Read Lock Read As #1 Close #1 ' If an error occurs, the document is currently open. If Err.Number < 0 Then FileLocked = True Err.Clear End If End Function Sub OpenMyfolder() Dim sStr as String sStr = "P:\MyFolder\MyBook.xls" if FileLocked(sStr) then Workbooks.Open sStr, ReadOnly:=True Else Workbooks.Open sStr End if End Sub -- Regards, Tom Ogilvy "johnboy" wrote in message ... Steve, thanks for response - I have tried using functions posted at various sites to test if File is Open - but still get the message File In Use! -- JB "Steve Yandl" wrote: Take a look at this function. The example is using MS Word but it should do the trick for you. http://word.mvps.org/FAQs/MacrosVBA/CheckIfFileOpen.htm Steve "johnboy" wrote in message ... Hi all, I am using VBA to open a workbook on a network drive with following code: Sub OpenMyfolder() Application.DisplayAlerts = False Workbooks.Open("P:\MyFolder\MyBook.xls") Application.DisplayAlerts = True End Sub Code works fine however, if workbook is already open on network drive, you get a prompt to say file is in use with options to Open as Read Only, Wait or Cancel. My question is, can I prevent this message showing if File is already open? & if it is, default to open the file as ReadOnly? I have used Application.DisplayAlerts = False but this did not work. Also,I am not too sure how I should write the reuqired code to open as readonly in such event. Hope clear - any help / guidance appreciated. -- JB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
I saved file A over file B. Can I get file B back? | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
I SAVED A FILE OVER ANOTHER A FILE IN EXCEL. THE OLD FILE WAS AN . | Excel Discussion (Misc queries) | |||
i received a file that reads powerpoint document file file exten. | Excel Discussion (Misc queries) |