Search code examples
excelvbawindows-10office365onedrive

Identify if user has OneDrive and change file path accordingly


I have the following VBAs to save an Excel workbook on the desktop of the User.
The first one is used if the User does not have OneDrive.
The second one is used if the User has OneDrive.

Sub Without_OneDrive()
ActiveWorkbook.SaveCopyAs "C:\Users\firstname.lastname\Desktop\Test.xlsm"
End Sub

Sub With_OneDrive()
ActiveWorkbook.SaveCopyAs "C:\Users\firstname.lastname\OneDrive - limango.com\Desktop\Test.xlsm"
End Sub

All this works fine.


However, now I have the situation that some users have OneDrive and other ones don't.
Therefore, I am wondering if there is a solution to identify if the user has OneDrive or not and then decide which file path should be used. Something like this:

Sub SaveWorkbook()
If OneDrive exist then
ActiveWorkbook.SaveCopyAs "C:\Users\firstname.lastname\OneDrive - limango.com\Desktop\Test.xlsm"
Else
ActiveWorkbook.SaveCopyAs "C:\Users\firstname.lastname\Desktop\Test.xlsm"
End If
End Sub

Solution

  • This can be done, using the Dir VBA function, as you can see in the following source code excerpt:

    Public Function DirExists() as Boolean
    
    Dim DirName As String
    
        DirName = VBA.FileSystem.Dir("C:\Users\firstname.lastname\OneDrive - limango.com", vbDirectory)
    
        If DirName = VBA.Constants.vbNullString Then
            DirExists = False
        Else
            DirExists = True
        End If
    
    End Sub
    

    For your information, I got this piece of code from this URL.