Solved

Excel VBA Public Sub call to delete files is not working

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

630 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