External Data Source Not Available Error in Excel
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...
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.
1 Answer
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
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
