How to check through VBA if a file name exists in sharepoint (Microsoft Teams)

[Monicasinha]

I am getting the error "bad file name" on the code "If Dir(https://ts.xy.com/sites/newfolder/Shared Documents/General/Data Summary/" & new_workbookName & ".xlsx") <> "".
Need your help in solving this.


---------------------------------
Sub export()

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
Username = "https://ts.xy.com/sites/newfolder/Shared Documents/General/Data Summary/"
Set new_workbook = Workbooks.Add
new_workbookName = ThisWorkbook.Worksheets("sheet 1").Range("E5")

' Check if same file name exists in sharedfolder
If Dir(https://ts.xy.com/sites/newfolder/Shared Documents/General/Data Summary/" & new_workbookName & ".xlsx") <> "" Then
GoTo ErrMsg
Else
' Save new workbook in the teams folder
new_workbook.SaveAs Username & new_workbookName & ".xlsx", 51

' 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

new_workbook.Close False

MsgBox "Export complete.", vbInformation

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

End Sub

 

[Joe4]

Duplicate to: VBA to check if same name file exists in teams folder

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

Please to be sure read and follow ALL of the rules listed.

Thank you.