Solved

Excel VBA Public Sub call to delete files is not working

Posted on 2011-03-01
2
336 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now