Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA Public Sub call to delete files is not working

Posted on 2011-03-01
2
Medium Priority
?
352 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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

916 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