Workaround DIR() limitation

[aggiemarine07]

Ok so I am running into a problem with using the below code since I started storing it on OneDrive. Basically, what the code is supposed to do is copy the existing worksheet to a new workbook and then save the workbook; prior to this though it is supposed to delete any previous/existing version of the file prior to creating the new workbook. My problem is that the code errors out on the

If Len(Dir$(strFile)) > 0 Then Kill strFile

line of the code

Sub Macro1()

mypath = Application.ActiveWorkbook.Path
'delete previous weeks sheet
Dim strFile  As String: strFile = Application.ActiveWorkbook.Path & "\" & Mid(FileOnly, 1, InStr(FileOnly, ".") - 1) & " " & Format(Date - 7, "YYYY.MM.DD") & ".xlsb"
If Len(Dir$(strFile)) > 0 Then Kill strFile
ThisWorkbook.Sheets("Allocations").Copy
ActiveWorkbook.SaveAs mypath & "\" & Mid(FileOnly, 1, InStr(FileOnly, ".") - 1) & " " & Format(Date, "YYYY.MM.DD") & ".xlsb", FileFormat:=50

End Sub

I apologize in advance if this is a duplicate and/or has been answered elsewhere but I have searched Google and other forums but have not found a good answer, so maybe I am searching with the wrong keywords. Thanks!

 

[DanteAmor]

What data do you have in this variable: "FileOnly"? I guess it's the name of Thisworkbook

It is not necessary to delete the file previously, just put this instruction "Application.DisplayAlerts = False" at the beginning of your macro

Try this:

Sub Macro1()
  Dim mypath As String, strFile As String, FileOnly
  Application.DisplayAlerts = False
 
  mypath = Application.ActiveWorkbook.Path
  FileOnly = ThisWorkbook.Name
  strFile = mypath & "\" & Mid(FileOnly, 1, InStr(FileOnly, ".") - 1) & " " & Format(Date - 7, "YYYY.MM.DD") & ".xlsb"
  ThisWorkbook.Sheets("Allocations").Copy
  ActiveWorkbook.SaveAs strFile, FileFormat:=50
End Sub

 

[DanteAmor]

If you also want to delete the file, then it would be like this:

Sub Macro1()
  Dim mypath As String, strFile As String, FileOnly As String
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  mypath = Application.ActiveWorkbook.Path
  FileOnly = ThisWorkbook.Name
  strFile = mypath & "\" & Mid(FileOnly, 1, InStr(FileOnly, ".") - 1) & " " & Format(Date - 7, "YYYY.MM.DD") & ".xlsb"
  If Len(Dir$(strFile)) > 0 Then Kill strFile
  ThisWorkbook.Sheets("Allocations").Copy
  ActiveWorkbook.SaveAs strFile, FileFormat:=50
  ActiveWorkbook.Close False
End Sub

 

[aggiemarine07]

@DanteAmor - thank you! I completely forgot about putting that snippet to turn off alerts

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.