External data source not available error in excel

Question

I'm encountering the following problem: I have three types of workbooks: Personal Tracker used by all users, Global Tracker and the Pivot workbook, both in a shared location. Scenario: every time the user ends their work, they click a button, the data is transferred to the GL Tracker. Upon the change event in the GL Tracker, the Pivot Workbook is opened and the Pivot Table refreshed.
Then, once a week the Pivot Workbook should open automatically upon for e.g. Task Scheduler to perform some automated action without interference of any user. And here is where I encounter the problem: Once the Pivot wb is opened, I get an error message that

We couldn't get the data from 'Table1[#Data] in the workbook "Global Tracker path". Open this workbook in Excel and try again.

I guess it's connected to some data update in the Pivot Table and connections. How could I make this error disappear when Excel opens by itself, however still let the Pivot Workbook update every time when a user submits their data?
I have tried to open the GL Tracker right after the opening of the Pivot Workbook, but it didn't work. I have also searched for some code to disable connections, but none of it worked for me.

Thank you in advance.

Try putting a piece of code in the Workbook_Open event, able to check if "Global Tracker` is open. If not, it is easy to be open from the event. — FaneDuru

FaneDuru FaneDuru · Accepted Answer · 2020-03-01T17:25:33

Try making this piece of code the Pivot WB Workbook_Open event:

Private Sub Workbook_Open()
    Dim w As Workbook, boolFound As Boolean
    Const GLWorkbookFullName As String = "Global Workbook full name" ' put here the real fullname
    For Each w In Workbooks
        If w.FullName = GLWorkbookFullName Then
            boolFound = True: Exit For
        End If
    End If
    If Not boolFound Then Workbooks.Open GLWorkbookFullName
End Sub

External data source not available error in excel

1 Answers