Excel data source reference is not valid

I found the reason the pivot tables didn't work was because of the file name. The reports are generated by a report automation system and the name contains square brackets [] that Excel doesn't like.

I removed the square brackets totally by chance and found the pivots are working.

I also tried to save the file in the Save As option with square brackets but like I said, Excel moans about it and doesn't allow you.

I had this same error but didn't have brackets [] in my file name. I found that the Excel file from my web-based reporting tool was not actually in XLSX format. When I did a "Save As" in Excel the file type was "Web page". I changed that to "Excel workbook (*.xlsx)" and saved with the same name and the Pivot Table worked.