I have 2 workbooks (Workbook A and Workbook B). Workbook A contains data and Workbook B has a series of pivot tables and pivot charts that are linked to that data. I'm trying to use vba to create the connection to Workbook A, refresh the data, then delete the connection. I'm running into trouble creating the connection. After the .connections.addfromfile line, a dialog box pops up asking me to Select Table and it lists 3 available tables. The code pauses at this point and waits for me to select one of the tables. I've tried SendKeys, but since it pauses the code, it doesn't execute that lines. Any help would be greatly appreciated! The code is short and simple enough, it seems the solution should be pretty straight forward, I just can't find it.
Workbooks("Test Connection.xlsm").Connections.AddFromFile _ "C:\Users\user\Desktop\New folder (2)\Data.xlsx" ActiveWorkbook.RefreshAll ActiveWorkbook.Connections("Data").Delete