MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel 97-03 file connecting to Excel 2007 file as data source

  Asked By: Diane    Date: Mar 15    Category: MS Office    Views: 1780

I've got an interesting problem.

I have an Access report which I need to run regularly. The data which
the Access report is based on is contained in an Excel 2007
spreadsheet which is linked in.

On most of the computers I want to run this report I have Access 2007,
however some of the computers only have earlier versions of Access.
Note that all of the computers have Excel 2007 installed.

The spreadsheet contains numerous pivot tables which don't convert
back to earlier versions of Excel very well, but that doesn't really
matter because the Access report only reads the basic numerical data
in the spreadsheet.

The problem of course is that Access XP and 2003 can't link to Excel
2007 spreadsheets, so therefore my report won't work on the computers
which only have Access XP or 2003.

The easy solution would seem to be just to maintain the data in a
97-03 format Excel spreadsheet, but there are a couple of reasons for
not wanting to do that, not least of which the very large size of the
spreadsheet and how slowly it runs in 97-03 format.

I was wondering whether it might be possible to mirror the worksheets
containing the data in the Excel 07 spreadsheet into an intermediate
Excel 97-03 spreadsheet which is in turn read by Access XP.

i.e. can I connect an Excel 2007 spreadsheet to an Excel 97-03
spreadsheet, reading Excel 07 tables into the 97-03 spreadsheet?

Or would there be a quick and simple way to create a 97-03 format
spreadsheet on the fly when the report is loaded up in Access XP, i.e.
add another button to the Access form marked "refresh Excel 97-03

Any ideas?



No Answers Found. Be the First, To Post Answer.