# VBA to check if same name file exists in teams folder



## Monicasinha (Monday at 11:10 AM)

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 (Monday at 11:22 AM)

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 (Monday at 11:24 AM)

Micron said:


> 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
> 
> 
> ...


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 (Monday at 11:29 AM)

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 (Monday at 11:46 AM)

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


----------



## Micron (Monday at 12:14 PM)

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 (Monday at 12:24 PM)

Using Dir function throws the error "type mismatch"


----------



## Micron (Monday at 12:35 PM)

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


----------



## Monicasinha (Monday at 12:50 PM)

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 (Monday at 1:07 PM)

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.


----------



## Monicasinha (Monday at 11:10 AM)

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 (Today at 11:16 AM)

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 (Today at 11:20 AM)

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.


----------

