VBA code to open an Excel file only if not already open

[Stepheny2k2]

Good morning guys,

I am trying to alter my vba code so when I update a filter on a pivot table in one workbook, a second workbook opens with the refreshed data. Everything is working except that once the the workbook as been opened once, I get an error when i run it again - Excel recognises that the file is already open and wants to know whether to overwrite.

I have written the code below which I was hoping would try to activate the output workbook if open but open it if it it wasn't already open but it doesn't work. It still asks me whether I want to overwrite.

Can anyone see my problem?

On Error Resume Next: Err.Clear: Dim wb As Workbook
    Set wb = Workbooks("Path to X.xls file"): wb.Activate
    If Err.Number > 0 Then Set wb = Workbooks.Open(Path to "CurrentMonth-TESTING.xls"): 
    If Not wb Is Nothing Then wb.Worksheets("Master").Activate Else MsgBox "File not found", vbInformation: Exit Sub

 

[JGreen01]

Hi monfro,

If you haven't seen this in your search for a resolution, check it out, http://www.cpearson.com/excel/WaitForFileClose.html (by Chip Pearson). It's different than what I use, but due to what you want to accomplish, I believe it will assist/aid you in how to modify your macro to achieve your specific goal.

What I use for working with multiple workbooks, going back and fourth consistently between them, whether the workbooks are open or closed, and without ever having one that is already open try to re-open itself as a "READ ONLY" version (that I'm unable to modify and save) when I click a command button to go to it, is as follows. However, not on Network, but could be.

' With not needing to be concerned if Workbook is open or closed, for I'm the only one accessing it.
' To be able to press a Vba Command Button on a sheet in one Workbook, open up a sheet
' in a different workbook, and/or just to move back and forth quickly between them making changes,
' when they're already open. All done without receiving Error Messages (already open, open as read only, etc.)
' by applying vba coding similar to what's shown below. I also keep all Workbooks in the same File Folder
' for which I use this code, such as a file folder for accounting, with four workbooks in that folder, where the
' information is somewhat connected - JGreen01.


Sub ShowYourMacroName()
    On Error Resume Next: Err.Clear: Dim wb As Workbook
    Set wb = Workbooks("YourWorkbookName.xlsm"): wb.Activate
    If Err.Number > 0 Then Set wb = Workbooks.Open(Filename:="C:\YourFolderName\YourWorkbookName.xlsm", ReadOnly:=False)
    If Not wb Is Nothing Then wb.Worksheets("YourWorksheetName").Activate
Sheets("YourWorksheetName").Visible = True


    With Worksheets("YourWorksheetName")
        .Activate
        .Range("A1").Select
    End With
End Sub

If I can be of assistance, let me know,

JGreen01