Delete workbook after importing data

[sharky12345]

I'm using this to import data from multiple files in a folder;

<code class="vb keyword">Sub</code> <code class="vb plain">ImportCSVsWithReference()</code>
<code class="vb comments">'UpdatebyKutoolsforExcel20151214</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xSht  </code><code class="vb keyword">As</code> <code class="vb plain">Worksheet</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xWb </code><code class="vb keyword">As</code> <code class="vb plain">Workbook</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xStrPath </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xFileDialog </code><code class="vb keyword">As</code> <code class="vb plain">FileDialog</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xFile </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb spaces">    </code><code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">GoTo</code> <code class="vb plain">ErrHandler</code>
<code class="vb spaces">    </code><code class="vb keyword">Set</code> <code class="vb plain">xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)</code>
<code class="vb spaces">    </code><code class="vb plain">xFileDialog.AllowMultiSelect = </code><code class="vb keyword">False</code>
<code class="vb spaces">    </code><code class="vb plain">xFileDialog.Title = </code><code class="vb string">"Select a folder [Kutools for Excel]"</code>
<code class="vb spaces">    </code><code class="vb keyword">If</code> <code class="vb plain">xFileDialog.Show = -1 </code><code class="vb keyword">Then</code>
<code class="vb spaces">        </code><code class="vb plain">xStrPath = xFileDialog.SelectedItems(1)</code>
<code class="vb spaces">    </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces">    </code><code class="vb keyword">If</code> <code class="vb plain">xStrPath = </code><code class="vb string">""</code> <code class="vb keyword">Then</code> <code class="vb keyword">Exit</code> <code class="vb keyword">Sub</code>
<code class="vb spaces">    </code><code class="vb keyword">Set</code> <code class="vb plain">xSht = ThisWorkbook.ActiveSheet</code>
<code class="vb spaces">    </code><code class="vb keyword">If</code> <code class="vb plain">MsgBox(</code><code class="vb string">"Clear the existing sheet before importing?"</code><code class="vb plain">, vbYesNo, </code><code class="vb string">"Kutools for Excel"</code><code class="vb plain">) = vbYes </code><code class="vb keyword">Then</code> <code class="vb plain">xSht.UsedRange.Clear</code>
<code class="vb spaces">    </code><code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">False</code>
<code class="vb spaces">    </code><code class="vb plain">xFile = Dir(xStrPath & </code><code class="vb string">"\" & "</code><code class="vb plain">*.csv")</code>
<code class="vb spaces">    </code><code class="vb keyword">Do</code> <code class="vb keyword">While</code> <code class="vb plain">xFile <> </code><code class="vb string">""</code>
<code class="vb spaces">        </code><code class="vb keyword">Set</code> <code class="vb plain">xWb = Workbooks.Open(xStrPath & "\" & xFile)</code>
<code class="vb spaces">        </code><code class="vb plain">Columns(1).Insert xlShiftToRight</code>
<code class="vb spaces">        </code><code class="vb plain">Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name</code>
<code class="vb spaces">        </code><code class="vb plain">ActiveSheet.UsedRange.Copy xSht.Range(</code><code class="vb string">"A"</code> <code class="vb plain">& Rows.Count).</code><code class="vb keyword">End</code><code class="vb plain">(xlUp).Offset(1)</code>
<code class="vb spaces">        </code><code class="vb plain">xWb.Close </code><code class="vb keyword">False</code>
<code class="vb spaces">        </code><code class="vb plain">xFile = Dir</code>
<code class="vb spaces">    </code><code class="vb keyword">Loop</code>
<code class="vb spaces">    </code><code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">True</code>
<code class="vb spaces">    </code><code class="vb keyword">Exit</code> <code class="vb keyword">Sub</code>
<code class="vb plain">ErrHandler:</code>
<code class="vb spaces">    </code><code class="vb plain">MsgBox </code><code class="vb string">"no files csv"</code><code class="vb plain">, , </code><code class="vb string">"Kutools for Excel"</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub</code>

Can someone show me how to delete the file after importing please?

 

[simon_frick]

Try adding this at the top:

Dim wbName as String
Dim arrList As Object
Set arrList = CreateObject("System.Collections.ArrayList")

Then in the loop save the name to the list:

    Do While xFile <> ""
        Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
        wbName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        arrList.Add wbname
        Columns(1).Insert xlShiftToRight
        Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
        ActiveSheet.UsedRange.Copy xSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        xWb.Close False
        xFile = Dir
            Loop

Then when you are done delete the files:

For Each Item In arrList    Kill Item
Next Item