Solved

Excel VBA Public Sub call to delete files is not working

Posted on 2011-03-01
2
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

697 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