Hi. I have a macro that merges all excel workbooks within a given folder into one master workbook (and on 1 sheet). i need to do this every day as new workbooks go into the folder. Because of this i need to delete the workbooks that i have copied so that they are not copied again the next day. Can anyone help please? I'm sure its fairly easy and maybe just a change by the "close workbook" line, but if you don't know then you don't know i guess. Thanks in advance. Here is the code i'm using:

Sub ImportData()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim LastRow As Long

'\\ Disable Application Defaults to remove screen flicker, messages, Etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

'\\ Set folder to work from
Path = "\\Holding folder\" 'Change path as needed
FileName = Dir(Path & "\*.xls", vbNormal)
If FileName = "" Then
MsgBox "There are no files awaiting import", vbOKOnly
Exit Sub
End If
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)

'\\ Copy from workbooks and paste into this workbook.
If Range("A4") < 0 Then
LastRow = Range("A65536").End(xlUp).Row + 1
Range("A4:G" & LastRow).Select
Selection.Copy
Windows("Merging Tool.xls").Activate
If Range("A4") < 0 Then
LastRow = Range("A65536").End(xlUp).Row + 1
Range("A" & LastRow).Select
ActiveSheet.Paste
Else
Range("a4").Select
ActiveSheet.Paste

End If

End If

'\\ Close Workbooks

Wkb.Close SaveChanges:=True
FileName = Dir()

Loop

'\\ Re-enable application defaults
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

On Thursday, May 17, 2012 10:15:33 AM UTC-5, kardifflad wrote:
Hi. I have a macro that merges all excel workbooks within a given folder
into one master workbook (and on 1 sheet). i need to do this every day
as new workbooks go into the folder. Because of this i need to delete
the workbooks that i have copied so that they are not copied again the
next day. Can anyone help please? I'm sure its fairly easy and maybe
just a change by the "close workbook" line, but if you don't know then
you don't know i guess. Thanks in advance. Here is the code i'm using:

Sub ImportData()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim LastRow As Long

'\\ Disable Application Defaults to remove screen flicker, messages,
Etc.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

'\\ Set folder to work from
Path = "\\Holding folder\" 'Change path as needed
FileName = Dir(Path & "\*.xls", vbNormal)
If FileName = "" Then
MsgBox "There are no files awaiting import", vbOKOnly
Exit Sub
End If
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)

'\\ Copy from workbooks and paste into this workbook.
If Range("A4") < 0 Then
LastRow = Range("A65536").End(xlUp).Row + 1
Range("A4:G" & LastRow).Select
Selection.Copy
Windows("Merging Tool.xls").Activate
If Range("A4") < 0 Then
LastRow = Range("A65536").End(xlUp).Row + 1
Range("A" & LastRow).Select
ActiveSheet.Paste
Else
Range("a4").Select
ActiveSheet.Paste

End If

End If

'\\ Close Workbooks

Wkb.Close SaveChanges:=True
FileName = Dir()

Loop

'\\ Re-enable application defaults
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True




--
kardifflad
Look in the vba help index for KILL

sorry i can't find anything relevant under kill.

kardifflad brought next idea :
sorry i can't find anything relevant under kill.

You need to look again! 'Kill' is the VBA command statement used to
delete files.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

This sounds like a good scenario for using ADODB to grab the data from
each workbook without having to 'open' them in Excel. Once the data is
entered into your consolidation worksheet you simply delete the file
and move on to the next one (until Dir = "")! This approach will
obviate much of the overhead in tow with having to open/close each
workbook, and make your process much faster and efficient<IMO.

Here's a good place to get started using ADODB with Excel files...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion