Open Linked workbooks with VBA

[Rupert Bennett]

I have nine workbooks residing in the same folder on the network. They all have cells that linked to one or more workbooks within the folder and the Master that has links to all. I am looking for a routine that would open all workbooks that are linked to the workbook being opened. We could even put code in each workbook, to say which ones should be opened as a result. The problem for me is to write the code to say if the file is already opened, go ahead and open it as a read only file. On closing, any file that was opened as read only should be closed without updating, but update and save all the others. TIA for any help you can give.
Rupert

 

[viquarshaikh]

Why don't you make a start and then give us a yell when you're stuck?

 

[Rupert Bennett]

I am sorry to be just getting back to you on this. I was busy doing month-end inventory; a real PITA
Here is a sample of the code I am using. I list each file to be opened by
each workbook. If the workbook is in use by another user, it will be opened as a read only.
In the BeforeClose event, I activate and close each workbook that was opened and save changes depending on whether it was opened as read only.

I think my code can be made more efficient and also get rid of the annoying notice that you get telling you the file is available for edit, when the read only file is closed by the other person who had it open.


Private Sub Workbook_Open()
Workbooks.Open Filename:="P:\Forecasts\Qrtly Summary.xls", UpdateLinks :=3
Workbooks.Open Filename:="P:\Forecasts\Actual and Frcst 2006.xls", UpdateLinks:=3
'this is repeated for each workbook that I want to be opened by this workbook
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbooks("Qrtly Summary.xls").Activate
If Workbooks("Qrtly Summary.xls").ReadOnly Then
Workbooks("Qrtly Summary.xls").Close savechanges:=False
Else: Workbooks("Qrtly Summary.xls").Close savechanges:=True
End If
Workbooks("Actual and Frcst 2006.xls").Activate
If Workbooks("Actual and Frcst 2006.xls").ReadOnly Then
Workbooks("Actual and Frcst 2006.xls").Close savechanges:=False
Else: Workbooks("Actual and Frcst 2006.xls").Close savechanges:=True
End If
' this is also repeated for all files that were opened by this workbook.
End Sub

 

[viquarshaikh]

Try using the IsFileOpen ApI to check whether a file is already open or not.

Check out this link
http://www.xcelfiles.com/IsFileOpenAPI.htm

Then use that function like this

   If IsFileAlreadyOpen("P:\Forecasts\Actual and Frcst 2006.xls") Then
        Workbooks.Open Filename:="P:\Forecasts\Qrtly Summary.xls", UpdateLinks :=3 , ReadOnly:=True

    Else
        Workbooks.Open Filename:="P:\Forecasts\Qrtly Summary.xls", UpdateLinks :=3 , ReadOnly:=False
    End If

 

[Rupert Bennett]

Thanks for your suggestion. I will amend my code accordingly. I have deadlines to meet in preparing my Financial Statements and other reports for April, so I probably won't be able to test the code for a few days. My only question at this point is, will this get rid of the annoying pop up on my screen after the previously opened file is closed by the other user? This message comes even hours after I have closed all the files, letting me know that the file is now available for edit. Thanks for your help.