VBA to check if same name file exists in teams folder

[Monicasinha]

Hi

Below is the VBA code that I am using to export data from my workbook to Microsoft teams folder. The newly created file with exported data has a name that is taken from a cell in my workbook itself.
When I hit the Macros button, it should check if same name file already exists in the designated teams folder. If answer is "yes", it should not overwrite, instead there should be a message to change the name and then only it should save.
My current code however lets the file overwrites. Can you please help.
---------------------------------------------------------------------------------------------------
Sub export1()



Dim worksheet_name As Variant
Dim new_workbook As Workbook
Dim saved_folder As String
Dim fileName As Variant
Dim Username As String

Application.DisplayAlerts = False
' Open a new workbook. Name the workbook
Set new_workbook = Workbooks.Add
new_workbookName = ThisWorkbook.Worksheets("sheet 1").Range("E5")

' Copy format and data from column C & E from my workbook and paste it in newly created workbook
ThisWorkbook.Worksheets("sheet 1").Activate
Columns("C:E").Select
Selection.Copy
new_workbook.Worksheets(1).Activate
Columns("B:D").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ThisWorkbook.Worksheets("sheet 1").Activate
Columns("C:E").Select
Selection.Copy
Columns("B:D").Select
new_workbook.Worksheets(1).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Save new workbook in the teams folder
Username = "https://ts.xy.com/sites/newfolder/Shared Documents/General/Data Summary/"

new_workbook.SaveAs Username & new_workbookName & ".xlsx", 51

Application.DisplayAlerts = True

new_workbook.Close False


MsgBox "Export complete.", vbInformation

Exit Sub
ErrMsg: MsgBox ("Change the version number")
new_workbook.Close False

End Sub

 

[Monicasinha]

I am unable to get the UNC Path. Also, I was told by one of my colleagues that even if I use it, that may be local to my machine and others may not be able to access it.

 

[Micron]

If that refers to the suggestion to map a drive, that was only so you could get the server address from the properties sheet for the mapped drive, not for everyone to use your mapping. What I showed you in post 10 worked for everybody. The only difference was that it was in Access, which should not matter.