Linking to Excel File (1 Viewer)

[trab]

I've been downloading an Excel data file from a website for some time now, and with a link to it from Access, I was able to import the data to a cumulative table, but a month or so ago, the export facility on the website was updated.

When I download the data now, the link in Access no longer works. I get the message "Sheet1$ is not a valid name. Make sure that it does not include etc "

If I try to open the file in Excel, I get a warning message "The file you are trying to ope, StatusReportExcel.xls, is in a different format from the one specified by the file extension etc." If I click on <Yes>, the file opens.

Apparently, as far as Excel's concerned, this may be something to do with ExtensionHardening(?), but the real issue is what must I do to be able to make the link work properly again.

I ought to add when I try to save the Excel file, the default type is Web Page (*.htm; *.html).

 

[CJ_London]

perhaps it has been updated from .xls to .xlsx but has the wrong file extension - try changing to .xlsx and see if you still get the warning message when opening in excel

 

[trab]

The problem being, though is that I'm effectively using a Access 2003 database, and it doesn't like 2007 files(?)

 

[CJ_London]

very likely to be the case - you'll need to upgrade to 2007 or later. So far as I am aware there is not a fix for 2003 to open .xlsx

 

[trab]

Many thanks for your quick responses (tell me where the thanks button is and I'll hit it.)

I do have Access 2007 on my PC - I do have problems running two versions of Access. This is a necessity as I run some software which generates, can you believe it, Access 2 databases, which Access 2007 does not recognise. We've been waiting for an upgrade, which should come later this year.

On that matter, I've noticed problems with my VBA code, when I've upgraded other databases. What is the safest way to do this?

 

[CJ_London]

What is the safest way to do this?

just keep a copy of the original db in a safe place and work off a copy.

Don't think there is a 'safe' way of upgrading, just use the upgrade facility.

However my preferred alternative is to create a new db in 2007 then copy and paste and/or import all the objects one at a time. Tables should be linked anyway (if not, this is the time to do it), so you can just remake the links, then copy queries, modules, forms and reports in that order. As new code is imported, compile it to check for missing references and any other issues. Benefit of this is you can see if any particular object has a problem and resolve it before moving on.

Code that plays around with shortcut menus and toolbar will need to be changed to work with the ribbon instead.

2007 and later is less forgiving on field and table names so you may get issues there if you have used reserved words, non alpha numeric characters etc.

One other tip for code (modules and forms) is to copy it out of the old db into notepad, then copy from notepad to the new db. This effectively decompiles it and removes all the hidden rubbish created while developing. That hidden rubbish can sometime cause problems.

You can also look at the undocumented saveastext and loadastext functions as a way of moving objects - tho' not sure this would work in an upgrade situation - see this link

http://stackoverflow.com/questions/...tion-to-text-objects-and-import-into-a-new-md