Cannot Kill Excel File

[Benjamins via AccessMonster.com]

Hi,

Below is the Code that i am working:

Dim objXL As Object, objWkb As Object, objSht As Object
Set objXL = CreateObject("Excel.Application")
Set objWkb = objXL.Workbooks.Open([FileName])
Set objSht = objWkb.Worksheets("Sheet1")

With objSht
'Code to modify the excel file Info
End With

objWkb.SaveAs strTempFile 'Save the Excel file as a Tempoary to prevent
overwriting the orginial file
objWkb.Close savechanges:=False
objXL.Quit

Set qdf = CurrentDb.CreateQueryDef("TempQuery", "SELECT T1.* FROM [Excel 8.
0;HDR=YES;IMEX=1;Database=" & strTempFile & "].[Sheet1$] as T1;")

'Code that work on the query

CurrentDb.QueryDefs.Delete "TempQuery"
Kill strTempFile

Every thing working fine until it reached the Kill strTempFile saying No
perrmission.

Is there anything i have done wrong andhow can i rectify it.

Thanks.

 

[Alex Dybenko]

Hi,
Even query was deleted - access still holds the connection to excel file,
until you close access or connect to another file. So you can keep some
dummy excel file around and make your TempQuery connected to it after "work
on the query" to release your excel file

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Benjamins via AccessMonster.com]

Hi Alex,

Thanks for your advise that let me found out that i forget to set qdf =
Nothing. Once i set that, every thing work fine.

Thanks

Hi,
Even query was deleted - access still holds the connection to excel file,
until you close access or connect to another file. So you can keep some
dummy excel file around and make your TempQuery connected to it after "work
on the query" to release your excel file

Hi,

[quoted text clipped - 29 lines]

Thanks.

 

[Alex Dybenko]

ok, it was easier, then I thought!

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi Alex,

Thanks for your advise that let me found out that i forget to set qdf =
Nothing. Once i set that, every thing work fine.

Thanks

Hi,
Even query was deleted - access still holds the connection to excel file,
until you close access or connect to another file. So you can keep some
dummy excel file around and make your TempQuery connected to it after
"work
on the query" to release your excel file

Hi,

[quoted text clipped - 29 lines]

Thanks.