VBA Check if file exists in shared folder in SharePoint/Onedrive

[drefiek2]

Hi,

I am looking for some code which will check whether a certain file exists in a SharePoint folder which has been linked to multiple individual's OneDrives. We all access this SharePoint folder via our individual OneDrives:
C:\Users\USERNAME\OneDrive - COMPANY\SharePointFolderName

The difficulty I possibly have is having a code which will be unique to the person using the spreadsheet...?

The result should be in a little popup message box.

 

[nguyendang]

Hi,

I am looking for some code which will check whether a certain file exists in a SharePoint folder which has been linked to multiple individual's OneDrives. We all access this SharePoint folder via our individual OneDrives:
C:\Users\USERNAME\OneDrive - COMPANY\SharePointFolderName

The difficulty I possibly have is having a code which will be unique to the person using the spreadsheet...?

The result should be in a little popup message box.

Use the Environ function with parameter "USERPROFILE" to return the user folder name.
For example, running the following VBA code on my computer will return "C:\Users\nguye\OneDrive"

Debug.Print Environ$("USERPROFILE") & "\OneDrive"

"$" in "Environ$" means that this function should return a string instead of a variant value.

 

[drefiek2]

Use the Environ function with parameter "USERPROFILE" to return the user folder name.
For example, running the following VBA code on my computer will return "C:\Users\nguye\OneDrive"

Debug.Print Environ$("USERPROFILE") & "\OneDrive"

"$" in "Environ$" means that this function should return a string instead of a variant value.

Hi,
Thanks for your reply. I am not sure I know where to put this code? I currently have the following which works but I want to add a check to see if the PDF file already exists in individual's OneDrive folders (per my original post).

Sub SaveAsPDF()
'
' SaveAsPDF Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
 Select Case MsgBox("Is the date and shift type correct?", vbYesNo Or vbQuestion, Application.Name)
    Case vbNo
        Debug.Print "User exit"
        Exit Sub
    End Select
    Dim SharePointPath As String
    Dim PdfFileName As String
    Dim msg As String

    On Error GoTo SaveError

    SharePointPath = "https://company.sharepoint.com/company" '<<<<<<<<<<<< edit as required.

    PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SharePointPath & PdfFileName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    msg = "Handover successfully uploaded to SharePoint. You can now close the spreadsheet."
    MsgBox msg, vbInformation, "Upload Successful"
    Exit Sub

SaveError:
    msg = "Handover not uploaded to SharePoint. Please contact X." & vbCr & vbCr & Err.Number & " - " & Err.Description
    MsgBox msg, vbCritical, "Upload Failure"
End Sub

 

[nguyendang]

Hi,
Thanks for your reply. I am not sure I know where to put this code? I currently have the following which works but I want to add a check to see if the PDF file already exists in individual's OneDrive folders (per my original post).

Sub SaveAsPDF()
'
' SaveAsPDF Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
 Select Case MsgBox("Is the date and shift type correct?", vbYesNo Or vbQuestion, Application.Name)
    Case vbNo
        Debug.Print "User exit"
        Exit Sub
    End Select
    Dim SharePointPath As String
    Dim PdfFileName As String
    Dim msg As String

    On Error GoTo SaveError

    SharePointPath = "https://company.sharepoint.com/company" '<<<<<<<<<<<< edit as required.

    PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SharePointPath & PdfFileName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    msg = "Handover successfully uploaded to SharePoint. You can now close the spreadsheet."
    MsgBox msg, vbInformation, "Upload Successful"
    Exit Sub

SaveError:
    msg = "Handover not uploaded to SharePoint. Please contact X." & vbCr & vbCr & Err.Number & " - " & Err.Description
    MsgBox msg, vbCritical, "Upload Failure"
End Sub

For SharePoint Folder URL path, I found an answer on Stack Overflow: VBA Check If Sharepoint Folder Exists
However, I would prefer using Microsoft Graph API because it can help me manipulate SharePoint and OneDrive files easily.

 

[drefiek2]

I guess what would be ideal is for the code to check individual OneDrives on individual computers for the file name and folder, since the SharePoint folder is mapped to everyone's OneDrives and everyone has OneDrive on their PC. I just need to find a code which does not isolated independent PC/OneDrive accounts, one which can check the OneDrive of whoever is viewing the spreadsheet at that moment in time.