External Data in Pivot Tables

  • Thread starter Thread starter SPenney
  • Start date Start date
S

SPenney

I have a series of files in Excel that I would like to summarize with
Pivot Table. The files are too big -- too many lines -- to combine an
run the table. Is there a way to link multiple external files into on
workbook and run a table? I've explored the external data option, bu
I can only get one file with it. Thanks

Staci
 
You could use MS Query to import the data (Data>Import External Data>
New Database Query) and use an SQL UNION statement to combine the data
into one table.
 
How do you use an SQL UNION statement? I've never done a linked pivot
table before.

Stacie
 
Use MS Query to import the data from one of the files, then modify the
query:

Open the file in which you want the combined data (or create a new file)
Choose Data>Import External Data>New Database Query
Choose Excel files (or your file type), click OK
Select the first file to be combines, click OK
Select the table and columns for the query, click Next
(optional) Select a field to filter, click Next
(optional) Select field(s) to sort, click Next
Choose View data or edit query in MS Query, click Finish
In MS Query, choose View SQL
Assuming your files all have the same structure,
copy the existing SQL statement
Click at the end of the SQL statement, and press Enter
Type UNION and press the spacebar
Press Ctrl+V to paste in the copied SQL text.
Change the file name in the pasted text
Click OK, click OK to confirm
You should see the combined data
Choose File>Return Data to Microsoft Excel
Click OK to place the data on the worksheet, or create a PivotTable
 
Debra,

I tried the SQL union statement, but I am getting two error messages.
The first is "SQL cannot be represented graphically. Continue anyway?"
I clicked yes. Then the following message popped up "The Microsoft Jet
database could not find the object "11-2003--UPS Billing$" Make sure
the object exists and that you spelled its name and the path
correctly."

The name isn't quite right. The $ shouldn't be there, but it is on the
other.

Any suggestions? Thanks for all the help. If I can get this to work,
I'll really impress the gentleman I'm doing this for.

Stacie
 
Back
Top