Open/Close workbook and Refresh Connection

Question

I have two workbooks on a shared network drive:

Workbook A (Table)
Workbook B (Pivot Table -Connected to source Workbook A)

I'm trying to, when Workbook B is opened, run macro and do this:

Unprotect a certain worksheet on Workbook B
If workbook A is Open, refresh data connections on workbook B
If workbook A is closed, open workbook A and refresh data connections on workbook B, then close workbook A.
Protect a certain worksheet on Workbook B

The code below works as intended in most scenarios when testing so far, but if someone else tries to open workbook B on their computer when someone else has Workbook A opened on another computer, it opens workbook A as a read-only file and keeps it open on their computer. I need it to close on their computer, and keep the initial one open that's on the other computer.

Public Sub RefreshPvt()
ThisWorkbook.Worksheets("Sheet1").Unprotect
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wkb As Workbook

If IsFileOpen("S:\\Testing\Job Closeout Status Test.xlsx") Then
    ThisWorkbook.RefreshAll
Else
Set wkb = Workbooks.Open(filename:="S:\\Testing\Job Closeout Status Test.xlsx")
ThisWorkbook.RefreshAll
wkb.Close SaveChanges:=False
End If

ThisWorkbook.Worksheets("Sheet1").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Wait, why do you want to open workbook A in a new instance? There is absolutely no reason to do that, and it can cause some issues. So why do it? — vacip
I don't think you need to have the workbook A open. Still, opening it in another Excel instance is like opening it on another computer. Makes no sense and has no effect. If you want to open it, just open it in the same Excel instance where workbook B is. — vacip
Okay. I don't need it to open it in a new instance just have it opened when I refresh the connection. — DigitalSea

Jpad Solutions Jpad Solutions · Accepted Answer · 2016-01-09T16:53:00

if you run ThisWorkbook.RefreshAll on workboook B in the immediate window does it work?

You could also make XLApp.Visible = true to see whether or not it's opening

I think you should be using something more like:

Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables

    pt.RefreshTable

Next pt

To do the whole workbook you could use:

Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

End Sub

Open/Close workbook and Refresh Connection

2 Answers