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.Cou nt)
.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

Delete excel file after copy
 

Hi Jeff

Look in the VBA help for the Kill command


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


"Jeff" wrote in message ...
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.Cou nt)
.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

Delete excel file after copy
 

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" wrote in message ...
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.Cou nt)
.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

Delete excel file after copy
 

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

Delete excel file after copy
 

The above code only works if the file is not open