deleting workbooks as i go

[kardifflad]

Hi. I have a macro that merges all excel workbooks within a given folde
into one master workbook (and on 1 sheet). i need to do this every da
as new workbooks go into the folder. Because of this i need to delet
the workbooks that i have copied so that they are not copied again th
next day. Can anyone help please? I'm sure its fairly easy and mayb
just a change by the "close workbook" line, but if you don't know the
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 = Tru

 

[Don Guillett]

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

Look in the vba help index for KILL

 

[kardifflad]

sorry i can't find anything relevant under kill

 

[GS]

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

 

[GS]

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/DatabaseProgramming.zip

--
Garry

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