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

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.