I receive an Excel workbook every month with sales data from a customer. After four months of reports, the customer changed the formatting of the workbook. My report includes the last 12 months of data so I need to get the information from both workbook formats.
I setup folders for each format, put the workbooks in the appropriate folder, and created a query for each set of files. The final result is two data tables that have indentical columns including the data type and column names down to the same capitalization. Everything appears to be perfect. However, when I run Append Queries as New no errors are generated, but a new column is created for City, ST, and Zip and added to the query. The new columns have a 2 after the column name. The original columns are still in the output. Nothing is removed.
When I go to each query, copy the table, and paste it into a new Excel workbook, everything looks like the end result that I want. I spent hours trying to resolve this issue including rebuilding my queries completely multiple times. Any suggestions are GREATLY appreciated.
