DoCmd.TransferSpreadsheet locks Excel file

[Gerold]

Hi,

I'm importing Excel-files into Access XP with DoCmd.TransferSpreadsheet.
This works fine.

After the import I want to copy the files into a different directory and rename them. When I try to use FileCopy I get an error 70 saying that the file is used by another process.

There is no other program running that could lock this Excel-file. I only can imagine that Access itself during processing of "TransferSpreadsheet" is locking the file. How can I free the file from this process or what else can I do?

thanks for the help in advance
Gerold

 

[KenHigg]

Maybe the code is getting ahead of the os. Did you try including a doevents()?


kh

 

[Gerold]

thanks for responding, I tried what you said, but same result.

Maybe I should post part of the code:

first I read some specific cells and information about the data in the Excel file by doing this:

...
Dim App As Excel.Application 'Excel.Application
...

Set App = New Excel.Application
App.Workbooks.Open (Me.T_Path)

... (reading some cells)

App.Workbooks.Close
Set App = Nothing

...

'then the import happens

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"ImportTable", Me.T_Path, False, "A" & Trim(Me.T_FirstR) & ":K" & Trim(Me.T_LastR)

....

'and then the copy command in another subroutine

FileCopy Me.T_Path, ToPath


'and then I get the runtime error 70 "access denied"


Gerold

 

[KenHigg]

Copy them before the import.

???
kh

 

[Gerold]

this also doesn't work - same error - and I also need the import first to show the user the data in a help table so that corrections on the original Excel can be done before he/she decides to definitely import it - after that the file is ready to be copied into the special directory - all in this sequence.

By the way when I close Access after importing into the help table and try to open the Excel file with Excel I get the message that it is locked by me like when another user in a LAN would have opened the file.

How can I force the system to definitly give free this file within Access??

 

[KenHigg]

Hum...

I can't seem to duplicate this error. I have a feeling you may have a ref issue or maybe version issues. Have you checked refs and and have you tried other AcSpreadSheetType constants (acSpreadsheetTypeExcel7, etc)

kh

 

[Gerold]

... "somehow" it suddenly works! - I don't like this, I rather prefer to know why but have no time to investigate.

Anyway many thanks to you kh

Gerold