Deleting File with Kill not always working

165 Views Asked by At

I am a newbie working on my first VBA project. The application processes orders and collects info that needs to be printed on labels that go on packages. The company that prints our labels will take the label information from an excel workbook. So, I have a button in my application to copy all of the label information for labels that need to be printed to a new workbook. The workbook's name is Label_Info-mm-dd-yy with mm-dd-yy being the date the spreadsheet was created. I have code in there to check if the file exists and if so it prompts user do they want to delete it. Sometimes when the code deletes the file, I get an error and the application stops.

Here is an excerpt of the code

`   ' Create Excel file to hold label info
    ' Active Workbook is where VBA code and spreadsheet databases are
    Set App = ActiveWorkbook
    ' Building name of excel file I want to copy label info into
    LabelFile = "Label_Info_" & [Text(Now(),"MM-DD-YY")] & ".xlsx"
    ' Find Get current working directory using VBA
    Directory = CurDir
    FullName = Directory & "\" & LabelFile
    ' Create new workbook 
    Workbooks.Add
    ' Check if file exists
    If Dir(FullName) = LabelFile Then
        'File exists
        msgValue = MsgBox("File already exists do you want to delete", vbYesNo + vbInformation, "Confirmation")
        If msgValue = vbYes Then
            MsgBox ("Deleting file called " & FullName)
            ' This next line of code should delete the file and most times it does
            Kill FullName
        Else
            ' If user does not want to active app wb, reset list box and exit sub
            App.Activate
            Call Reset
            Exit Sub
        End If
    End If
0

There are 0 best solutions below