upsfa
asked on
Excel VBA Public Sub call to delete files is not working
I have some vba code in Excel (2010) that exports each sheet into a separate csv file. This works well. Then I have created a sub that I try to call at the end of the process which will delete only one of the csv files. It works when I run the delete file sub directly, but when I call it it does not delete the file. There are 9 files created and I just want to combine 8 of them, so I want to delete the “Summary” file before I combine them (I know I could exclude it from being exported, but I still need the solution since I want to delete all of the individual files, after they have been combined. (I have not created the code to combine the files yet. I have s simple bat file that works well, I plan to call that).
Private Sub SaveAllSheetsAsCSV()
On Error GoTo Heaven
' This creates multipls csv files
' each sheet reference
Dim Sheet As Worksheet
' path to output to
Dim OutputPath As String
' name of each csv
Dim OutputFile As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' ask the user where to save
OutputPath = "C:\temp"
If OutputPath <> "" Then
' save for each sheet
For Each Sheet In Sheets
OutputFile = OutputPath & "\" & Sheet.Name & ".csv"
' make a copy to create a new book with this sheet
' otherwise you will always only get the first sheet
Sheet.Copy
' this copy will now become active
ActiveWorkbook.SaveAs Filename:=OutputFile, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
Next
End If
Finally:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub
Heaven: MsgBox "Couldn't save all sheets to CSV." & vbCrLf & "Source: " & Err.Source & " " & vbCrLf & "Number: " & Err.Number & " " & vbCrLf & "Description: " & Err.Description & " " & vbCrLf
GoTo Finally
Call Wait4Me(50, 1)
Call DelSummFile
End Sub
Public Sub DelSummFile()
'Loop through all the files in the directory by using Dir$ function
Dim MyFile As String
MyFile = Dir$("c:\temp\Summary.csv")
Do While MyFile <> ""
KillProperly "c:\temp\" & MyFile
'need to specify full path again because a file was deleted 1
MyFile = Dir$("c:\temp\Summary.csv")
Loop
End Sub
Sub DelFiles()
'Loop through all the files in the directory by using Dir$ function
Dim MyFile As String
MyFile = Dir$("c:\temp\Sheet*.csv")
Do While MyFile <> ""
KillProperly "c:\temp\" & MyFile
'need to specify full path again because a file was deleted 1
MyFile = Dir$("c:\temp\sheet*.csv")
Loop
End Sub
Public Sub KillProperly(Killfile As String)
If Len(Dir$(Killfile)) > 0 Then
SetAttr Killfile, vbNormal
Kill Killfile
End If
End Sub
Function Wait4Me(Delay As Integer, DispHrglass As Integer)
Dim DelayEnd As Double
DoCmd.Hourglass DispHrglass
DelayEnd = DateAdd("s", Delay, Now)
While DateDiff("s", Now, DelayEnd) > 0
Wend
DoCmd.Hourglass False
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks,