Delete excel file after copy

J

Jeff

Good morning all
I have a workbook that I copy sheets from all of the workbooks in a
directory. Sean Connolly sent me some code to do this and mentioned about
moving or deleting the files when done so that I don't copy them twice. I do
not know how to delete them after copying. I hope someone can help with this.
The code looks like this:
Sub CopySheets()
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim wsName As String

' Create a FileDialog object as a Folder Picker dialog box.
' Allows user to select the folder at run-time.
' (Or just hardcode the value of wsName below).
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd
' Use the Show method to display the Folder Picker
' dialog box and return the user's action.
If .Show = -1 Then ' The user pressed the OK button.
vrtSelectedItem = .SelectedItems(1)
' Folder Picker does not allow multi-select so must be 1.
Else ' The user pressed Cancel.
Exit Sub
End If
End With

' Assuming all files that you're looking for
' are Excel workbooks with a .xls extension ...
wsName = Dir(vrtSelectedItem & Application.PathSeparator & "*.xls")
Application.ScreenUpdating = False ' Prevent screen flickering
Do While Len(wsName) > 0
Workbooks.Open Filename:=wsName, ReadOnly:=True
With ActiveWorkbook
.Sheets(1).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
.Close SaveChanges:=False
End With
wsName = Dir ' Get the next workbook (if there is one)
Loop
Application.ScreenUpdating = True

'Set the object variable to Nothing.
Set fd = Nothing
End Sub
Thanks! Jeff
 
D

damorrison

Sub Killfile()
Dim MyFile As String 'This line of code is optional
On Error Resume Next 'On hitting errors, code resumes next code
MyFile = "D:\Kill\killfile.xls"
Kill MyFile
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top