Deleting Source .CSV file after embedding it to worksheet via VBA

Sinodus

New Member
Joined
Jan 8, 2019
Messages
2
[FONT=&quot]Hi…I’m trying to embed a .csv file to an excel sheet and then delete the source file using Windows 10 and Excel 2016. My code embeds the sourcefile to the worksheet ok but when it tries to delete the original .csv file I get a “Run-time error ’70’: Permission denied message. I’ve tried the same code using a .txt file instead and it seems to run fine. Is there any way to release the .csv file from excel so it can be deleted without having to convert my file to a .txt?[/FONT]
[FONT=&quot]Public Sub EmbedFile()[/FONT]
[FONT=&quot] Dim oFile As OLEObject[/FONT]
[FONT=&quot] Dim fPath As String[/FONT]
[FONT=&quot] fPath = “C:TempTest.csv”[/FONT]
[FONT=&quot] Set oFile = ActiveSheet.OLEObjects.Add(Filename:=fPath, Link:=False, DisplayAsIcon:=True, IconIndex:=0, IconLabel:=fPath)[/FONT]
[FONT=&quot] Set oFile = Nothing[/FONT]
[FONT=&quot] Kill fPath[/FONT]
[FONT=&quot]End Sub[/FONT]
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,272
Welcome to MrExcel forums.

That error occurs because the .csv file is still open as a workbook at the Kill command. The solution is to close it (Test.csv) before deleting it:
Code:
    Application.Workbooks("Test.csv").Close
 

Watch MrExcel Video

Forum statistics

Threads
1,099,471
Messages
5,468,813
Members
406,611
Latest member
hanman453

This Week's Hot Topics

Top