Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-04-10
8
Medium Priority
?
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 42

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 42

Accepted Solution

by:
dlmille earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 42

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

721 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