Add close and delete statement whenusing a array to import csv files into one worksheet

[Bevg]

Hi,
I found this wonderful code on the internet and it opens a group of CSV files in a temp folder, copies the date into a worksheet in a workbook. Now I would like to delete the csv files from the temp folder however I'm getting a "Permissions Denied" error on my Kill coding. After researching I confirmed that I do have access because I was able to manually navigate to the temp download folder and delete the csv files. No problem. I did have the "Kill" code after the "End If", could that be the problem?

So I wondering if someone could help me add to the code to check to see if the csv files are open then close them then delete them.

Any help, suggestions, or examples would be greatly appreciated!

Thank you, Bevg

Here is the code:

Sub ImportMultipleCSV()

Dim myfiles
Dim i As Integer

myfiles = Application.GetOpenFilename(filefilter:="Report (*.csv), *.csv", MultiSelect:=True)

If IsArray(myfiles) Then
For i = LBound(myfiles) To UBound(myfiles)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myfiles(i), Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
.Name = "Sample"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ","
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next i
Else
MsgBox "No File Selected"
End If

Dim xConnect As Object
For Each xConnect In ActiveWorkbook.Connections
If xConnect.Name <> "ThisWorkbookDataModel" Then xConnect.Delete
Next xConnect

End Sub

[Greg M]

Hi there,

Does the following addition to your code do what you need? It seems to work at my end.

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[Bevg]

Thank you for your advice.

I inserted the Kill code at the location suggested and only one file appeared when four use to appear in the separate worksheet. Only one file was deleted and I'm receiving a Run-time error '13': Type mismatch error

Then I moved the code after the "End If" statement and I received the Run-time error 9 Subscript out of range

and the Kill PathName:=myfiles(i) code is highlighted in yellow.

I tried the code in different variations
Kill Myfiles(i)
Kill myfiles
Kill C:\Users\folder\AppData\Local\Packages\folder\TempState\Downloads\*.*

and still receive the same error. This is frustrating

[Bevg]

Hi,
I found the solution.
The Kill code needs to be placed AFTER the "Next xConnect" code and the actual path to the files to be deleted needs to be supplied in order for the code to work correctly

Kill "C:\Users\folder\folder\folder\folder\Microsoft.MicrosoftEdge_8wekyb3d8bbwe\TempState\Downloads\*.*"
MsgBox "File Deleted Successfully "

Bev

[Greg M]

Hi again,

I think you'll find that you can avoid specifying the FullName of each of the files by using:

Please Login or Register  to view this content.

Regards,

Greg M