Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-04-10
8
Medium Priority
?
518 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

916 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