Solved

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

Posted on 2012-04-10
8
473 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
ID: 37830602
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
ID: 37835366
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
ID: 37835391
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
ID: 37853473
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 41

Expert Comment

by:dlmille
ID: 37858593
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
ID: 37881414
I've been on vacation the past week. I'll be looking at versions and patches shortly.
0
 

Author Comment

by:joelhoffman
ID: 37893231
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
ID: 37902007
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

13 Experts available now in Live!

Get 1:1 Help Now