Solved

Access 2007 can no longer link to Excel 2007 data due to Chart Sheets

Posted on 2012-04-10
8
458 Views
Last Modified: 2012-04-27
How do we link Excel 2007 data into an Access 2007 accdb when there are "chart sheets" in the Excel workbook?

I'm hoping there's a good explanation or workaround for this. We have recently upgraded from Office 2003 to Office 2007. In 2003, we linked from Access to Excel tables. What we have found now in 2007 is that, if there is a "chart sheet" ( a chart on its own tab or sheet rather than a chart as an object in a sheet) in the Excel 2007 workbook, we can no longer link to the data in that workbook from Access 2007. We get the error, "External table is not in the expected format."

If we then go into that same Excel workbook, delete the chart sheet, and save the workbook, Access can link to the workbook just fine. If we put charts in the workbook as objects on worksheets, Access links just fine. It's only when there is a chart as its own sheet that Access can't link.

We've started with brand new Access accdb databases and with brand new xlsx workbooks using the very simplest of data in Excel and we can duplicate this error every time. I've spent the last 30 minutes looking for solutions here in experts-exchange but couldn't find anything so sorry if this question has already been addressed and I just couldn't find it.

We don't want to lose the functionality of having charts as their own sheets, but if we do that, we lose the functionality of Access being able to link to our workbooks.

Any ideas?
0
Comment
Question by:joelhoffman
  • 5
  • 3
8 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Linking Excel (e.g., Sheet1) with embedded sheets and with Chart Sheets (charts on their own sheet) works fine with a simple test I just did.

I was able to link Sheet1 and also the range on Sheet1 called "mySource".

Here's my sample XLSX file (attached).  Create a link from it on Sheet1.  It should link just fine.  It also imports to a new Access table just fine.

Try it with this file and advise exactly how are you trying to do the linking.

Dave
excelLinkAccess-r1.xlsx
0
 

Author Comment

by:joelhoffman
Comment Utility
I saved your file directly to my C Drive; created a brand new Access 2007 database, Clicked on:

External Data \ Excel

I selected your file and the radial button "Link to the data source by creating a linked table."

At this point: make sure your file is NOT open.

Clicked OK.

Error message: "The wizard is unable to access information in the file. Please check that the file exists and is in the correct format."

I open your file, delete the chart tab, save the file, CLOSE it.

I go through the EXACT same steps above to link to the spreadsheet from Access, accepting all the defaults ... Everything links up perfectly.

Since I've posted this, one of my company's IT folks have contacted Microsoft. Microsoft is apparently aware of the issue and is sending us a patch. I'll know more when we get the patch and try it out, at which time I'll post here.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
It may be you have one patch less than mine.  I do the same thing (Office 2007) and it works just fine.

Check to see if your Office is 100% up to date with the patches that are already there - I'm running Office Professional Plus with Access version:  12.0.6553.5000 SP2 MSO 12.0.6562.5003 as listed in the Access Options/resources dialog.

Dave
0
 

Author Comment

by:joelhoffman
Comment Utility
Interesting! My version starts with 12.0.6535 instead of 12.0.6553. Everything else is exactly the same as yours. Could you please check that you didn't just transpose the '5' and '3'? Thanks!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I just got another update, so I'm now at:

12.0.6654.5003 SP2 MSO (12.0.6562.5003)

If its not this, then please advise about the patch that MSFT provides for the knowledge base.

Cheers,

dave
0
 

Author Comment

by:joelhoffman
Comment Utility
I've been on vacation the past week. I'll be looking at versions and patches shortly.
0
 

Author Comment

by:joelhoffman
Comment Utility
We still have not received the Microsoft patch they promised us. Since yours is working fine and you're several updates ahead of me, I'm thinking we're just behind the curve at our company getting updated. I'll let you know. Hopefully I can keep this thread open until we get that patch on my end.
0
 

Author Comment

by:joelhoffman
Comment Utility
I was wrong: we had received the patch but our corporate IT is dragging their feet on pushing it out across the company. I'm going to assume the patch will fix the problem since you're not having the same problem and close this question. Thanks for the replies!
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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

12 Experts available now in Live!

Get 1:1 Help Now