1

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
Bruce Dautrich
  • 19
  • 4
  • 1
    What's the error? – findwindow Sep 07 '22 at 18:36
  • `Workbooks.Add` why are you adding a new workbook? Also, you can't delete it if it's the activeworkbook, that is `App` can't be `FullName`. – findwindow Sep 07 '22 at 18:44
  • `App` is an inappropriate variable name for a workbook. Since the code is in it, you need to use `ThisWorkbook` instead of `ActiveWorkbook`. Who knows what `CurDir` will return? If e.g. the new files are in the same folder as `ThisWorkbook`, then use `FullName = ThisWorkbook.Path & Application.PathSeparator & LabelFile`. The check should be something like `If Len(Dir(FullName)) > 0 Then` i.e. the file exists. Share more relevant code (e.g. the variable declarations) to receive more accurate advice or a solution. – VBasic2008 Sep 07 '22 at 18:57
  • Sorry, all the error has not occurred since. So, I do not know the error because I did not capture it the first time. I am adding a new workbook because I have a database of label information that is in various states. The VBA application I wrote search this database and finds rows of label information that is ready to go to label printer. They want to receive an excel spreadsheet that has this label info so I am creating a spreadsheet to send to them which has the label info that matches the criteria that it is ready to send to them. – Bruce Dautrich Sep 09 '22 at 03:47
  • The workbook reference seems to be okay because all of the other processing works. The Curdir returns the pathname to the current directory because I have multiple debug statements in the code and one of them prints this out. So I also know the fullname if the correct name of file including the path to it – Bruce Dautrich Sep 09 '22 at 03:47
  • I'd update `[Text(Now(),"MM-DD-YY")]` to `Format(Date, "mm-dd-yy")` – Darren Bartrup-Cook Sep 09 '22 at 11:01
  • Darren thanks but the full name value is correct – Bruce Dautrich Sep 10 '22 at 13:03

0 Answers0