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

 

[Micron]

You could adopt a naming convention whereby the filename ends in a number, get that number, add 1 and save as with a number higher than the last one? Then the user doesn't have to provide a name. Otherwise could use Dir function to see if the name already exists. Perhaps
If Not Dir(Username & new_workbookName & ".xlsx") = "" Then <<if true, file name was found

Dir function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[Monicasinha]

You could adopt a naming convention whereby the filename ends in a number, get that number, add 1 and save as with a number higher than the last one? Then the user doesn't have to provide a name. Otherwise could use Dir function to see if the name already exists. Perhaps
If Not Dir(Username & new_workbookName & ".xlsx") = "" Then <<if true, file name was found

Dir function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Thanks!
1. There is a format in which only name is to be saved. So I wont be able to use that number.
2. I have tried DIR earlier but it doesnt work in Shared folders/teams.

Thanks

 

[Micron]

This is SharePoint or just a normal network folder? I have no idea about Dir with SP but it certainly should work with regular folders, shared or not. If you can use vba to save a new file somewhere, it only makes sense that the same programing language could detect a file by name.

 

[Monicasinha]

This is Microsoft teams folder. Here Dir doesnt seems to work.

 

[Micron]

So it's SharePoint then.
Lots of solutions offered to make Dir work with sp, one being to map a drive letter to the sp server/folder path so that Windows Web Client will automatically provide access. If multiple users is the problem there, then I suggest you research for a solution that works for you. Another is to make a hyperlink out of the file path and test it.
There is also a vba method named Checkout that might work for you. I have used it before but don't know what is required of that at the sp end. By that I mean if the files had to have a certain property or be in a particular folder style I don't know. I just think that if you could make use of the method it would return either true or false if the file existed, and an error if it didn't. You'd trap the error and just save the file.

It would be so easy if you were allowed to save a file with a number. The only other suggestion I have if you can't get Dir, hyperlink or Checkout to work is to store the saved filepath in a sheet.

 

[Monicasinha]

Using Dir function throws the error "type mismatch"

 

[Micron]

Did you map a drive letter to the sp server first?

 

[Monicasinha]

I made some chnage in code and use dir and 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") <> "".

Can you pls see if I am making some mistake somewhere..
---------------------------------
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

Quote Reply
Report

 

[Micron]

AFAIK, you have to know the server name and path. That is not the same as the url (web address) that you are using. See if you can map a drive letter to it and when doing so, you should be able to get the server name. For me, it was more like this

\\sharepoint\CompanyName\DivisionName\BusinessUnitName\DepartmentName\etc\etc

As far as I know, the \\ denotes this as a UNC path. What you don't want is something that starts with https.
Get an IT guy to show you how to map a drive if you don't know how, or research it. I'm presuming you're allowed to do this even if you don't know how yet.
Or research the other suggestions. Or maybe ask why you can't have a number at the end of the file name. Seems kind of innocuous to me, and would solve your problem and perhaps reduce user effort and mistakes as well.