By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,365 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,365 IT Pros & Developers. It's quick & easy.

Error during a Kill function in Access VBA

P: 11
Hi all, Thanks in advance,

I have form in an access db (Access 2000) that users can use to (hopefully) import a raw .txt file into access. The user clicks a button and a FileDialog opens to select the .txt file the want to import. When the user selects the file, VBA opens the txt file in Excel (2007) using the following:

objExcel.Workbooks.Open strFilePath

where strFilePath is the file selected in the FileDialog box.

A “With…End With” runs that sets up the .txt file in a format that Access will recognize and at the end of the “With…End With” it saves the txt file as an excel file so that the code can import the new xls file later:

.ActiveWorkbook.SaveAs FileName:="N:\Temp\VR2Temp.xls", FileFormat:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End With

The code imports the data from Excel into Access using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Data", "N:\Temp\ VR2Temp.xls", True, "Data!A:E"

Everything works fine up to this point. Here I want to delete the temporary Excel file so that the next time the user runs the code a message box doesn’t popup saying “This file already exists. Replace the existing file?” To delete the file I was going to run the following code:

Dim strDeleteFilePath As String
strDeleteFilePath = "N:\Temp\VR2Temp.xls"
Kill strDeleteFilePath

However, I get the following error:

“Run-time error ‘70’: Permission denied.”

The code works fine if I run it on its own. But when I run it with everything else I get the error. I’m pretty sure that I’m missing a line of code to close something, but I have no idea what that line would be. I tried the following but it didn’t work:

objExcel.Workbooks.Close strFilePath

I’m stumped. Any suggestions?
Jul 16 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.