Solved

Excel VBA Public Sub call to delete files is not working

Posted on 2011-03-01
2
339 Views
Last Modified: 2012-05-11
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

Open in new window

0
Comment
Question by:upsfa
2 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35009317
Probably because you have  "Exit Sub" in line 37?

Sid
0
 
LVL 1

Author Comment

by:upsfa
ID: 35009400
Thanks, that was it.  But removing the Exit Sub is bad - it puts me in a loop where the error message keeps appearing.  I just put my calls before the exit sub and it works fine.

Thanks,
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question