Solved

In excel create a connection to another excel workbook

Posted on 2013-05-22
3
411 Views
Last Modified: 2013-05-23
I have a spreadsheet that gets some data from another excel workbook. It works fine, except if there are new, additional columns in the source workbook, they are ignored, don't show up in spreadsheet using the connection. It seems like the connection is somehow remembering all the column names and will only import those, even though the sql is like select * from ....

If there are fewer columns in the source workbook, then the missing columns (headers) will show up like Column1, Column2, etc, but, of course, no data in these columns.

Any ideas on how to make the table expand / shrink with the source?

thanks
Alan

excel 2010, 32 bit, on a windows 7 64 bit box.
connection looks like (points to a sharepoint folder):
DSN=Excel Files;DBQ=\\network\DavWWWRoot\SiteDirectory\path\VWProductSpendData.xlsx;DefaultDir=\\network\DavWWWRoot\SiteDirectory\path\;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
Command type: SQL
Command text like:
SELECT *
FROM `\\network\DavWWWRoot\SiteDirectory\path\VWProductSpendData.xlsx`.qryVWProductSpend qryVWProductSpend

connection was create from within excel using "From Other Sources", selecting Excel Files*
0
Comment
Question by:avoorheis
  • 2
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 39190150
Right-click the table, choose "External Data Properties", and uncheck the "Preserve column sort/filter/layout" option.

HTH
Rory
0
 

Author Comment

by:avoorheis
ID: 39190943
hmmm, I don't remember exactly how I created this, but, it's not a table (which is a bit odd for me, cause I think I normally end up with a table when using a data connection).
Anyhow, I thought I had already unchecked that, but, now that I'm looking at it again, that option is grayed out.
Let me go through the creation process again and I'll report back.
0
 

Author Comment

by:avoorheis
ID: 39191305
I guess that's the issue. I actually did the import from a text file instead of a spreadsheet and has less control of the columns.

thanks
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now