Delete excel file after copy

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

Hi Jeff

Look in the VBA help for the Kill command


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jeff" <Jeff@discussions.microsoft.com> wrote in message news:BB0BE47F-066F-45F9-85B4-26D6E4CAFE88@microsoft.com...
> 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

That is what I needed. Thanks! Ron

"Ron de Bruin" wrote:

> Hi Jeff
>
> Look in the VBA help for the Kill command
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:BB0BE47F-066F-45F9-85B4-26D6E4CAFE88@microsoft.com...
> > 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
>
>
>

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

The above code only works if the file is not open