DoCmd.TransferSpreadsheet 3270 error if spreadsheet closed

[completelybobbins]

Hi All

Thoughts on the following welcome, as I've got no clue!

I have code that imports two named ranges from the same spreadsheet to two different tables, snippet as below.

    For i = 1 To foundFiles
 
    fileName = .foundFiles(i)
    DoCmd.SetWarnings False
    'Import the summary information for this document to table docDetail
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "docDetail", fileName, True, "docDetail"
    'Import progress of the actions for this document to table approvalProgress
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "approvalProgress", fileName, True, "approvalProgress"
    DoCmd.SetWarnings True
 
    Next i

When I run this with the spreadsheet open both ranges import without errors and all is well. When the spreadsheet is closed (it's intended to pull data from large numbers of these sheets, so this needs to work) the first bit ('docDetail') imports fine, but the second fails with an Error 3270 'Property Not Found' error and no data imported.

Can't even begin to think how to solve this. It's code I've used before (with different ranges) without problem. The only difference is the database is now split with linked tables, but the fact that one works and not the other confuses me...

Cheers for any suggestions!

Rob

 

[completelybobbins]

Bit more info: when I make a local copy of the table approvalProgress (copy/paste job) both cases work fine, so it's something to do with the spreadsheet being closed and access using a linked table.