DoCmd.TransferSpreadsheet import

[mjpearson]

I have a multitude of files to import. I've set up a subroutine to import them all to MS-Access using TransferSpreadsheet. It works reasonably well for my needs however, I have two challenges:

When it runs, Access will generate import-error-tables but the name of the import-error-tables contain the "spreadsheet-range" in the import-error-table-name rather than the import-file-name. Is there a way to get Access to generate the import-error-tables with the file-name rather then the spreadsheet-range-name?

Does TransferSpreadsheet offer any sort of flags or error messages that will alert my subroutine that import-error-tables have been generated as result of the TransferSpreadsheet command? I would like to capture the flag to a log report for each file transfered.

Thanks,

mike

 

[PaulF]

After each import, you might query table MSysObjects for Type = 1 (local table) and instr([Name],"ImportError") > 0 (contains import error in the name). If one exists you can rename the existing table with the import errors to one you can recognize using DoCmd.Rename "OldtableName", acTable, "NewTableName" and copy the new name to a separate table for your log. Then move onto the next file to import.


PaulF

 

[mjpearson]

Thanks Paul,

I was hoping to avoid that process but it looks like it's the only viable solution. Frustrating. I have over 300+ spreadsheets to import and naturally, they are't all in the same format. About 5 different formats. The next step is getting things into first and second order normal.....a breeze by comparison to the import challenge.

Thanks for the ideas.


mike