Import Issue

[Xarkath]

Hello,

We get weekly Excel files from clients that I wont to load to Access for tracking and reporting. The reports are consistently formatted and everything looks good. I set it up so that the incoming files copied and renamed to replace the copy that is set in the path the import macro is using. (new file but same name). On the first import I save the steps then created a macro for the end users so that they do not have to do the manual importing. That macro keeps popping errors and I was able to track down the cause but I don't understand it nor do I know how or even if there is a way to resolve it. If the new file is "saved" over the copy being imported the save import can not longer find the document even though it is in the same folder and has the same name. If the data is simply copied from the new file and pasted into the target file then the macro works fine.

Why would saving over the existing file make the document unrecognizable and is there a way to fix it?



Thanks

[rpeare]

If the files are always going to the same location with the same file name why not try to renew the links, typically what I've done with this sort of things is not link the excel files at all, but import the contents via code using the docmd.transferspreadsheet commands. Are you using a linked table structure?

[ssanfu]

Why would saving over the existing file make the document unrecognizable and is there a way to fix it?

Could be a number of things. Maybe the new file name has had a space added...... would have to see the code.

[Xarkath]

Hello,

These are not linked tables. I did not want to go that direction. I have never use the "transferspreadsheet" commands; I will look into them. As for the name it should not be changing, typically I browse to the folder then click on the document which then assigns the name. I do it this way to avoid any typing errors.

As for the code, not much to it, just a button that triggers a macro to run the saved import. All wizard created.

It is almost as if the create date is part path so by doing a "save as" it gets a new create date and breaks the link stored in the saved import. Makes no sense to me but that is how it acts. And I have checked the saved import to be sure that there are not any dates in the path. If there is, they are hidden.

Thanks for the suggestion, I'll look into it.

[alansidman]

Convert the macro to VBA (using the Access Wizard) and then post the VBA code to this forum for analysis.

[rpeare]

I agree, convert the macro to vba then post the code.