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?
