PivotTable source error

[pujo]

Hello all,
I have a workbook with the source data needed to create a pivot table.
The pivot table must be in a separate workbook.

My issue is that the source workbook name changes daily with date in file name. (example: Production Report - 20-Jun-23.xlsb)
The pivot table works fine as long as the Production Report file name doesn't change however, it changes daily (example: Production Report - 21-Jun-23.xlsb)

Is there a workaround for this?

 

[pjmorris]

I would suggest you investigate PowerQuery to acquire the data and Powerpivot to present it (you could skip the powerpivot, but it brings many benefits). Careful amendment of the Powerquery code in the PQ Editor will allow you to automatically generate the daily filename.

It might take a little to learn, but its definitely the way to go. Note, that questions specific to PowerQuery/ PowerPivot should be posted in the 'Power Tools' section in order to get the best response.

 

[pujo]

@pjmorris

Thanks for the quick info. I will look into PowerQuery/PowerPivot. I know nothing about them at the moment. lol

Appreciate your assistance!

Cheers!