When opening an Excel workbook that contains square brackets in the name (e.g., "foo[1]"), the user will receive the following error message when attempting to create PivotTables using data from within the workbook:
Data source reference is not valid
The cause is due to square brackets being invalid characters in an Excel workbook file name.
This behavior is expected when these conditions occur. In order to work around this, the user would be required to remove all invalid Excel characters from the file name.