Forum Discussion
Refresh Pivot Table while keeping the external source data open
Hello!
I have a pivot table created from an external excel workbook. I need to display in a Microsoft Teams meeting a computer monitor showing the pivot table while in another monitor I have the external excel workbook open. I need to make changes to the external excel workbook thru ought the meeting, refresh the pivot table being presented to show the changes; without closing the external source data file. Currently I get an error message that reads: [DataSource.Error] The process cannot access the file *** because it is being used by another process.
I have office 365
Please help!
5 Replies
Hi Amanda1901
I assume that you have created the pivot table from a Power Query, which reads that data from the external file, right?
In this case, I guess there is no way to avoid that error message, because Power Query will always complain if an external source file is open.
What you could do instead - if this is possible at all in your case - is to create the Pivot table directly in the external file (just for demonstration purposes) and then open a new window within this external file (Menu "View | New Window"). This way you can show the Pivot table in one window in your Teams session, while changing the source table in the other window.
I do not know if this is feasible in your case, but this is the only option I can think of.
Hi,
is there a way to refresh a power query + Power pivot file that has the data in the same file ( one sheet as DB uploaded to Power Query and then to Power pivot)?
i get an error when i tries to refresh from Teams ( or from excel on line for that matter) , no problem refreshing when opening in excel desktop)
This is the Technical details of the error i am getting:
"Connection: Query - DB
Error: COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=DB;extended properties="UEsDBBQAAgAIAKxMYVSGY5w/pwAAAPgAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAIWPvQ6CMBhFX4V0p38qUfNRBicTSExMjCspFRqhGFos7+bgI/kKkijq5nhPznDu43aHZGjq4Ko6q1sTI4YpCpSRbaFNGaPencIlSgTscnnOSxWMsrHrwRYxqpy7rAnx3mM/w21XEk4pI8cs3ctKNTn6yPq/HGpjXW6kQgIOrxjBccTwgq04nkcMyIQh0+ar8LEYUyA/EDZ97fpOiUqF2xTINIG8X4gnUEsDBBQAAgAIAKxMYVQPyumrpAAAAOkAAAATABwAW0NvbnRlbnRfVHlwZXNdLnhtbCCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAABtjksOwjAMRK8SeZ+6sEAINWUB3IALRMH9iOajxkXhbCw4ElcgbXeIpWfmeebzelfHZAfxoDH23inYFCUIcsbfetcqmLiRezjW1fUZKIocdVFBxxwOiNF0ZHUsfCCXncaPVnM+xxaDNnfdEm7LcofGOybHkucfUFdnavQ0sLikLK+1GQdxWnNzlQKmxLjI+JewP3kdwtAbzdnEJG2UdiFxGV5/AVBLAwQUAAIACADIA2FUfSVrMXkEAAC3FQAAEwAcAEZvcm11bGFzL1NlY3Rpb24xLm0gohgAKKAUAAAAAAAAAAAAAAAAAAAAAAAAAAAA5VhbayM3FH4P5D+I8YsDU0Og9KVNwet4L+0m3tretmDMIM/ItuqxFCRNmmD8H3ahu0soLG0pJdk/NH+nR3O/yImZti+NQ5Lx+c5dR0dzJImrKGdoFP8//vLw4PBALrEgHnrB5gJLJQJXBYKgE+QTdXiA4DPigXA1pX/lEr/TC4QgTP3AxWrG+ap9tJmc4zU5scoarOl20uNMAev08ICyoq6i3TPKKG5qMhPe19rgVf/HJoa03L42ng+dU3LZxEosua+d1yNnhH0iHcw8Zyyw1yiFdS372j99YrQ3xjOfdHp8PaOMtDd1/Xal1uy8BuxogewkhcA4ds45cwbPRmdOF2r2kqrr7ZEdW2xZT6mviHZlyH+WVmZ7RHwocE1rx17ZiGB3idqTV4L/BJCcoq++Rizw/aNcWW+J2QJ0ja8vSK4LPGZyzsW6x/1gzTQo21XL9mZjDQRdUIZ91GeQGqyIZSMF3MiD562NNlb/6gJsgwwBDs/IIVUHPQeXJUoxFqxnREToGQaP0UhBpmQKK3KlIjAmoyHBkrMaCquxhuWsi71An+nVgPiIV5cCEApBBhBrHe0C2nVdHjClM1XDh4APibwABdSEj7FcoVMiXTPSsnSdqC8+7+ic5/RnggcXNZF0YWsAJA0vdOwPeNOVki4YUhxlEnUeKFccrYBQ5iXOimAHT6F2z4jQ1fZdQAQlheI9JxKy/Q2nrF2pSbsQ5tZupV/QSypVBaxgWtu3lHmdl2SuBgFUbu4HVCXsTLBREsncieHoOd4B7ZrrNqqYi9JOWapRMzwJqO9RtkAzn7uroshoySFTuhdZ2+aSeTxDwoDiodjbQmJjICG3dwWut7Jpo+bEfkosrmbX05p6gVR8nZsEapa0ql+RStcPoGsHDOveRqDdIR+apgajhkXnKG9ZcMCOEXRDpLshSruhhSAINMm3xvRE97WY2kq3UpGY7buEqJaE3ecLIr4kcbM0N95jY+et5CRrwTsNTUFL3JLzQ6dmqXj+hG/D38Ib+PnkhJ/CO/iFxyZHoFFR8RRsckYkJ8/m3t5UKvUyWKt7Y8MrboIiy7aUxJLbpRR+hFDfh3+E78LbRpkryD+WhP0Fwd5AwbxrWGwlDY8jaX/qDQXhfnDCXyD+u/BN+NbRz2+iLNyFN40y+bDaR5fej+Gv8OVWl5YDf34PP4S3zj9NrlHpo0ht5SVrn3nnwcTZ++wHu9Jp7FKrts1nn56XioHVR7YhZqsm1aDl/r0Ffyr42jTgjLmBGJkuL11ivRsoPiRrfkmStyx5DB7EWBah/qjMMW35e+wHpPZujSanZI5h0olV6bBBKgn8KNaV5lV/0ndKbT59p1R2VgwRYTDXujV5E7uvwSQUQVwuvOSLD8W1Lb73GEIr3dUYxuJGNzYGPf/dvsbsescYV0JaRmpxUEyBB2a7lO2e0S5l2TGrZhrMo24KG+fkFDRN2JlgZSxP6feMkTWW6nVD5vOuYTVl2HkdkTHUbyMKma9dRaSY8SZCg3v2XJgHGpXy//dA+htQSwECLQAUAAIACACsTGFUhmOcP6cAAAD4AAAAEgAAAAAAAAAAAAAAAAAAAAAAQ29uZmlnL1BhY2thZ2UueG1sUEsBAi0AFAACAAgArExhVA/K6aukAAAA6QAAABMAAAAAAAAAAAAAAAAA8wAAAFtDb250ZW50X1R5cGVzXS54bWxQSwECLQAUAAIACADIA2FUfSVrMXkEAAC3FQAAEwAAAAAAAAAAAAAAAADkAQAARm9ybXVsYXMvU2VjdGlvbjEubVBLBQYAAAAAAwADAMIAAACqBgAAAAA=".
A connection could not be made to the data source with the DataSourceID of 'db5ba929-027e-4107-8a85-43b3c0bb8676', Name of '<pii>Query - DB</pii>'.
An error occurred while processing table '<pii>DB</pii>'.
COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=Rank.
A connection could not be made to the data source with the DataSourceID of 'b21404d5-dcd0-4289-8d0b-e3fd088e3a32', Name of '<pii>Query - Rank</pii>'.
An error occurred while processing table '<pii>Rank</pii>'.
The current operation was cancelled because another operation in the transaction failed.
Connection: Query - Rank
Error: COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=DB;extended properties="UEsDBBQAAgAIAKxMYVSGY5w/pwAAAPgAAAASABwAQ29uZmlnL1BhY2thZ2UueG1sIKIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAIWPvQ6CMBhFX4V0p38qUfNRBicTSExMjCspFRqhGFos7+bgI/kKkijq5nhPznDu43aHZGjq4Ko6q1sTI4YpCpSRbaFNGaPencIlSgTscnnOSxWMsrHrwRYxqpy7rAnx3mM/w21XEk4pI8cs3ctKNTn6yPq/HGpjXW6kQgIOrxjBccTwgq04nkcMyIQh0+ar8LEYUyA/EDZ97fpOiUqF2xTINIG8X4gnUEsDBBQAAgAIAKxMYVQPyumrpAAAAOkAAAATABwAW0NvbnRlbnRfVHlwZXNdLnhtbCCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAABtjksOwjAMRK8SeZ+6sEAINWUB3IALRMH9iOajxkXhbCw4ElcgbXeIpWfmeebzelfHZAfxoDH23inYFCUIcsbfetcqmLiRezjW1fUZKIocdVFBxxwOiNF0ZHUsfCCXncaPVnM+xxaDNnfdEm7LcofGOybHkucfUFdnavQ0sLikLK+1GQdxWnNzlQKmxLjI+JewP3kdwtAbzdnEJG2UdiFxGV5/AVBLAwQUAAIACADIA2FUfSVrMXkEAAC3FQAAEwAcAEZvcm11bGFzL1NlY3Rpb24xLm0gohgAKKAUAAAAAAAAAAAAAAAAAAAAAAAAAAAA5VhbayM3FH4P5D+I8YsDU0Og9KVNwet4L+0m3tretmDMIM/ItuqxFCRNmmD8H3ahu0soLG0pJdk/NH+nR3O/yImZti+NQ5Lx+c5dR0dzJImrKGdoFP8//vLw4PBALrEgHnrB5gJLJQJXBYKgE+QTdXiA4DPigXA1pX/lEr/TC4QgTP3AxWrG+ap9tJmc4zU5scoarOl20uNMAev08ICyoq6i3TPKKG5qMhPe19rgVf/HJoa03L42ng+dU3LZxEosua+d1yNnhH0iHcw8Zyyw1yiFdS372j99YrQ3xjOfdHp8PaOMtDd1/Xal1uy8BuxogewkhcA4ds45cwbPRmdOF2r2kqrr7ZEdW2xZT6mviHZlyH+WVmZ7RHwocE1rx17ZiGB3idqTV4L/BJCcoq++Rizw/aNcWW+J2QJ0ja8vSK4LPGZyzsW6x/1gzTQo21XL9mZjDQRdUIZ91GeQGqyIZSMF3MiD562NNlb/6gJsgwwBDs/IIVUHPQeXJUoxFqxnREToGQaP0UhBpmQKK3KlIjAmoyHBkrMaCquxhuWsi71An+nVgPiIV5cCEApBBhBrHe0C2nVdHjClM1XDh4APibwABdSEj7FcoVMiXTPSsnSdqC8+7+ic5/RnggcXNZF0YWsAJA0vdOwPeNOVki4YUhxlEnUeKFccrYBQ5iXOimAHT6F2z4jQ1fZdQAQlheI9JxKy/Q2nrF2pSbsQ5tZupV/QSypVBaxgWtu3lHmdl2SuBgFUbu4HVCXsTLBREsncieHoOd4B7ZrrNqqYi9JOWapRMzwJqO9RtkAzn7uroshoySFTuhdZ2+aSeTxDwoDiodjbQmJjICG3dwWut7Jpo+bEfkosrmbX05p6gVR8nZsEapa0ql+RStcPoGsHDOveRqDdIR+apgajhkXnKG9ZcMCOEXRDpLshSruhhSAINMm3xvRE97WY2kq3UpGY7buEqJaE3ecLIr4kcbM0N95jY+et5CRrwTsNTUFL3JLzQ6dmqXj+hG/D38Ib+PnkhJ/CO/iFxyZHoFFR8RRsckYkJ8/m3t5UKvUyWKt7Y8MrboIiy7aUxJLbpRR+hFDfh3+E78LbRpkryD+WhP0Fwd5AwbxrWGwlDY8jaX/qDQXhfnDCXyD+u/BN+NbRz2+iLNyFN40y+bDaR5fej+Gv8OVWl5YDf34PP4S3zj9NrlHpo0ht5SVrn3nnwcTZ++wHu9Jp7FKrts1nn56XioHVR7YhZqsm1aDl/r0Ffyr42jTgjLmBGJkuL11ivRsoPiRrfkmStyx5DB7EWBah/qjMMW35e+wHpPZujSanZI5h0olV6bBBKgn8KNaV5lV/0ndKbT59p1R2VgwRYTDXujV5E7uvwSQUQVwuvOSLD8W1Lb73GEIr3dUYxuJGNzYGPf/dvsbsescYV0JaRmpxUEyBB2a7lO2e0S5l2TGrZhrMo24KG+fkFDRN2JlgZSxP6feMkTWW6nVD5vOuYTVl2HkdkTHUbyMKma9dRaSY8SZCg3v2XJgHGpXy//dA+htQSwECLQAUAAIACACsTGFUhmOcP6cAAAD4AAAAEgAAAAAAAAAAAAAAAAAAAAAAQ29uZmlnL1BhY2thZ2UueG1sUEsBAi0AFAACAAgArExhVA/K6aukAAAA6QAAABMAAAAAAAAAAAAAAAAA8wAAAFtDb250ZW50X1R5cGVzXS54bWxQSwECLQAUAAIACADIA2FUfSVrMXkEAAC3FQAAEwAAAAAAAAAAAAAAAADkAQAARm9ybXVsYXMvU2VjdGlvbjEubVBLBQYAAAAAAwADAMIAAACqBgAAAAA=".
A connection could not be made to the data source with the DataSourceID of 'db5ba929-027e-4107-8a85-43b3c0bb8676', Name of '<pii>Query - DB</pii>'.
An error occurred while processing table '<pii>DB</pii>'.
COM error: Microsoft.Sql.Analysis.ASOnPremisesDataAccess, Failed discovery for connection string provider=Microsoft.Mashup.OleDb.1;data source=$Workbook$;location=Rank.
A connection could not be made to the data source with the DataSourceID of 'b21404d5-dcd0-4289-8d0b-e3fd088e3a32', Name of '<pii>Query - Rank</pii>'.
An error occurred while processing table '<pii>Rank</pii>'.
The current operation was cancelled because another operation in the transaction failed.https://euc-excel.officeapps.live.com/x/_layouts/xlviewerinternal.aspx?unified=1&ui=en-us&rs=he-IL&wopisrc=https%3A%2F%2Famstechltd.sharepoint.com%2Fsites%2FOGSM%2F_vti_bin%2Fwopi.ashx%2Ffiles%2F37d2955751a0402ca23f7ea8653045ed&wdenableroaming=1&mscc=1&hid=db2b9ad4-4d89-f13a-bdd6-94a84e1e12b9-1545&uiembed=1&uih=teams&uihit=files&hhdr=1&dchat=1&sc=%7B%22pmo%22%3A%22https%3A%2F%2Fteams.microsoft.com%22%2C%22pmshare%22%3Atrue%2C%22surl%22%3A%22%22%2C%22curl%22%3A%22%22%2C%22vurl%22%3A%22%22%2C%22eurl%22%3A%22https%3A%2F%2Fteams.microsoft.com%2Ffiles%2Fapps%2Fcom.microsoft.teams.files%2Ffiles%2F454822843%2Fopen%3Fagent%3Dpostmessage%26objectUrl%3Dhttps%253A%252F%252Famstechltd.sharepoint.com%252Fsites%252FOGSM%252FShared%2520Documents%252FGeneral%252FOGSM%25202022%252FOGSM%2520Template.xlsx%26fileId%3D37d29557-51a0-402c-a23f-7ea8653045ed%26fileType%3Dxlsx%26ctx%3Dfiles%26scenarioId%3D1545%26locale%3Den-us%26theme%3Ddefault%26version%3D21120606800%26setting%3Dring.id%3Ageneral%26setting%3DcreatedTime%3A1646123350978%22%7D&wdorigin=TEAMS-ELECTRON.teams.files&wdhostclicktime=1646123350917&jsapi=1&jsapiver=v1&newsession=1&corrid=3613a999-98e3-462c-b0f3-7f86ad726ea8&usid=3613a999-98e3-462c-b0f3-7f86ad726ea8&sftc=1&core=1&hvt=1&sams=1&accloop=1&sdr=6&scnd=1&sat=1&xlel=1&hodflp=1&wdredirectionreason=Force_SingleStepBoot&rct=Medium&ctp=LeastProtected#
https://euc-excel.officeapps.live.com/x/_layouts/xlviewerinternal.aspx?unified=1&ui=en-us&rs=he-IL&wopisrc=https%3A%2F%2Famstechltd.sharepoint.com%2Fsites%2FOGSM%2F_vti_bin%2Fwopi.ashx%2Ffiles%2F37d2955751a0402ca23f7ea8653045ed&wdenableroaming=1&mscc=1&hid=db2b9ad4-4d89-f13a-bdd6-94a84e1e12b9-1545&uiembed=1&uih=teams&uihit=files&hhdr=1&dchat=1&sc=%7B%22pmo%22%3A%22https%3A%2F%2Fteams.microsoft.com%22%2C%22pmshare%22%3Atrue%2C%22surl%22%3A%22%22%2C%22curl%22%3A%22%22%2C%22vurl%22%3A%22%22%2C%22eurl%22%3A%22https%3A%2F%2Fteams.microsoft.com%2Ffiles%2Fapps%2Fcom.microsoft.teams.files%2Ffiles%2F454822843%2Fopen%3Fagent%3Dpostmessage%26objectUrl%3Dhttps%253A%252F%252Famstechltd.sharepoint.com%252Fsites%252FOGSM%252FShared%2520Documents%252FGeneral%252FOGSM%25202022%252FOGSM%2520Template.xlsx%26fileId%3D37d29557-51a0-402c-a23f-7ea8653045ed%26fileType%3Dxlsx%26ctx%3Dfiles%26scenarioId%3D1545%26locale%3Den-us%26theme%3Ddefault%26version%3D21120606800%26setting%3Dring.id%3Ageneral%26setting%3DcreatedTime%3A1646123350978%22%7D&wdorigin=TEAMS-ELECTRON.teams.files&wdhostclicktime=1646123350917&jsapi=1&jsapiver=v1&newsession=1&corrid=3613a999-98e3-462c-b0f3-7f86ad726ea8&usid=3613a999-98e3-462c-b0f3-7f86ad726ea8&sftc=1&core=1&hvt=1&sams=1&accloop=1&sdr=6&scnd=1&sat=1&xlel=1&hodflp=1&wdredirectionreason=Force_SingleStepBoot&rct=Medium&ctp=LeastProtected#
Hi Eitanlif
currently it is not possible to refresh Power Query queries in Excel for the Web (this is what you usually use when you start Excel in Teams). You can just see and work with the query results, but you cannot refresh or modify any queries.
For this you need to open your file in the desktop version of Excel.
I cannot thank you enough for your suggestion. I am not using power query; however, your suggestion was on point. I am able to have the pivot table in one instance/window and the data source in another one. More importantly when I make a change in the data source all I have to do is click refresh all in the pivot table and it automatically updates the pivot table without requiring to close the data source.
Thank you again!!!
