Is there a way to link Access 2003 app to an xlsx sheet?

bfuchs
bfuchs used Ask the Experts™
on
Hi Experts,

I'm using Access 2003 and looking for a way to link a spreadsheet created I guess with later version of Excel.

The problem is that I dont get an option to see all file types (*.*) under the Link wizard, so how can this be accomplished?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
No can do.  Nothing in Access 2003 would have any idea of what the "future" format of a spreadsheet would look like.

I seem to recall that MS created a converter or viewer for the .xlsx (or maybe it was just .docx) file type.  If that is available, you might be able to use OLE automation to open Excel and get it to convert the spreadsheet for you but I haven't a clue if that would actually be possible.  Companies like Microsoft think long and hard before changing file formats for this very reason.  Once you change the format, no old version of the app can use the new file.
Hi,
MS created a converter or viewer for the .xlsx (or maybe it was just .docx) file type.  If that is available, you might be able..
Yes, that is avail and I'm using it to view contents of file, so perhaps there is hope..
Once you change the format, no old version of the app can use the new file.
FYI- Its not the file ext that's causing the problem it's just the office version, I tried doing it from a pc with Access 2010 and still couldn't, see attached.

Thanks,
Ben
Untitled.png
John TsioumprisSoftware & Systems Engineer

Commented:
You using Access 2010 but your application is still Access 2003...If you transfer it to 2010 then you can link the Excel to Access..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<The problem is that I dont get an option to see all file types (*.*) under the Link wizard, so how can this be accomplished?>>

  As Pat said, it cannot.   The ISAM driver in 2003 that allows for linking an Excel spreadsheet as a "table" only understands the .xls format.

 This is separate from the compatibility pack that allows you to use Excel 2003 to open an .xlsx file type.

 Pat was also correct in saying that automation might be an approach; it will work.   There, your using Excel itself to read the spreadsheet and with the compatibility pack, that will work.

 Other option is to get the creator of the spreadsheet to either save in .xls, or .csv

 No points please.

Jim.
Distinguished Expert 2017

Commented:
Thanks Jim for confirming that OLE automation could work.

@bfuchs,
If you can convert the app to the .accdb format, that will solve the problem.  Otherwise, use my automation suggestion to have Excel open the .xlsx and save it in the .xls format.  Then your A2003 app will be able to link to it.
Thanks to all participants!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial