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

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?
joelhoffmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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
joelhoffmanAuthor Commented:
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.
dlmilleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

joelhoffmanAuthor Commented:
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!
dlmilleCommented:
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
joelhoffmanAuthor Commented:
I've been on vacation the past week. I'll be looking at versions and patches shortly.
joelhoffmanAuthor Commented:
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.
joelhoffmanAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.