I have tried different ways to open/modify/save an Excel file which is already opened.
With the Workbook.Open() method, it opens it. But after modification, when I want to save and close it, of course it ask to SAVE AS the document.. I don't want to create a new Excel file.
I would like to avoid to open an Excel document which is already opened.. For example get a value of a Boolean variable called "IsOpenedFile" , which is true when it is already opened and false in the other case.
I have tried the code bellow.
Unfortunatly, when I previously open the excel file and run the code, the error handling doesn't seem to work, because I have still the window appearing with error number 70 saying that the access is refused (the error occurs at the Open statement... line n°5)
I have no idea how to solve the problem, can someone help?
Code:
Function IsOpenedFile(MyWorkbook As String)
MyWorkbook = "C:\Users\Username\Documents\File_name.xls"
Dim FileNumber As Long
On Error Resume Next
FileNumber = FreeFile()
Open MyWorkbook For Input Lock Read As #FileNumber
Close FileNumber
ErrorNumber = Err
On Error GoTo 0
Select Case ErrorNumber
Case 0: IsOpenedFile = False
Case 70: IsOpenedFile = True
Case Else: Error ErrorNumber
End Select
End Function
With the Workbook.Open() method, it opens it. But after modification, when I want to save and close it, of course it ask to SAVE AS the document.. I don't want to create a new Excel file.
I would like to avoid to open an Excel document which is already opened.. For example get a value of a Boolean variable called "IsOpenedFile" , which is true when it is already opened and false in the other case.
I have tried the code bellow.
Unfortunatly, when I previously open the excel file and run the code, the error handling doesn't seem to work, because I have still the window appearing with error number 70 saying that the access is refused (the error occurs at the Open statement... line n°5)
I have no idea how to solve the problem, can someone help?
Code:
Function IsOpenedFile(MyWorkbook As String)
MyWorkbook = "C:\Users\Username\Documents\File_name.xls"
Dim FileNumber As Long
On Error Resume Next
FileNumber = FreeFile()
Open MyWorkbook For Input Lock Read As #FileNumber
Close FileNumber
ErrorNumber = Err
On Error GoTo 0
Select Case ErrorNumber
Case 0: IsOpenedFile = False
Case 70: IsOpenedFile = True
Case Else: Error ErrorNumber
End Select
End Function