VBA - Save As to File Location...but if Existing File is Open by Another User

[johnny51981]

Howdy:
I have a set of Excel Processes that I run each day, they refresh through Power Query, and then I Save As specific sheets as CSV files. The VBA provided below is how it looks now, the "FilePath" named range is currently set to a folder on my local machine.

My question is:
I would like to Save As the CSV files directly to a Shared Network Drive, but sometimes users of said CSVs leave the previous update open from the Network location (rather than moving a copy to their computer). Is there a way to run the below process if it is free to save over, but if it is held up by another user, it would Save As with a different file name, such as including the current date to the end of the file name?

Sub ExportUnbilledWIP()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim TargetFolder As String
    TargetFolder = Range("FilePath")

    ThisWorkbook.Sheets("Unbilled WIP with Daily Excel").Copy
    ActiveWorkbook.SaveAs Filename:=TargetFolder & "Unbilled WIP with Daily Excel - CUSTOMER.CSV", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close

    MsgBox "Unbilled WIP with Daily Excel - CUSTOMER file export is now complete"
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

 

[johnny51981]

I was able to get it figured out.

 

[Fluff]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[ranjanagarwal]

I was able to get it figured out.

It will be great if you can post your solution. I have similar issues. I am looking to move and save sheets from workbook to the same location where the main workbook is saved.
Any help will be greatly appreciated.

 

[Fluff]

@ranjanagarwal please stop duplicating your question. You need to keep to your own thread.