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

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

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

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