Share via

SharedWorkspace.RemoveDocument doesn't remove document

Ezra Weinstein 1 Reputation point
2022-03-15T17:56:06.273+00:00

As the question states, I cannot get the SharedWorkspace.RemoveDocument command to successfully remove the necessary document from SharePoint. I don't know what I'm missing here, as the command completes without any error and all other parts execute correctly. What am I missing?

Private Sub CommandButton1_Click()

'Confirm save & close command
Dim answer As Integer
Dim error As Integer
Dim delete As String
answer = MsgBox("Are you sure you want to save sheet & close?", vbYesNo + vbQuestion, "WARNING")
If answer = vbYes Then

    'Save sheet and close
    Dim PWO As String
    Dim assembly As String
    Dim terminal As String
    Dim strand As String
    Dim gauge As String
    PWO = Range("D1").Value                 'Get PWO value
    assembly = Range("K1").Value            'Get assembly number
    terminal = Range("K2").Value            'Get part number on wire
    gauge = Range("D2").Value               'Get wire gauge
    If Range("D3").Value = "stranded" Then
        strand = "T"
    ElseIf Range("D3").Value = "solid" Then
        strand = "O"
    Else
        error = MsgBox("Please fill in or correct ""Wire Stranding"" field to be all lowercase.", vbOKOnly, "Input Error")
        Exit Sub
    End If
    delete = ActiveWorkbook.Name
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="<folderpath>" & terminal & "/" & assembly & _
        Space(1) & gauge & strand & Space(1) & PWO & Space(1) & Format(Now(), "MM-DD-YYYY") & ".pdf"

    'If nobody has the file checked out
    If Workbooks.CanCheckOut("<folderpath>" & delete) = True Then
        Application.DisplayAlerts = False
        'Open the file on the SharePoint server
        Workbooks.Open Filename:="<folderpath>" & delete, UpdateLinks:=xlUpdateLinksNever
        If Workbooks(delete).SharedWorkspace.Connected Then
            'Delete the file on the SharePoint server
            Workbooks(delete).SharedWorkspace.delete
        End If
        'Save to another location before closing
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs ("C:\Users\Public\DO_NOT_USE.xlsm")
        'Close the workbook
        Workbooks(delete).Close
        Application.DisplayAlerts = True
    Else
        Application.DisplayAlerts = False
        'Open the File to check if you already have it checked out
        Workbooks.Open Filename:="<folderpath>" & delete, UpdateLinks:=xlUpdateLinksNever
        'See if doc can be checked in
        If Application.Workbooks(delete).CanCheckIn Then
            'Check In, Save and Close
            Application.Workbooks(delete).CheckIn SaveChanges:=True, Comments:="Checked-In before Delete"
            'Open the file again
            Workbooks.Open Filename:="<folderpath>" & delete
            If ActiveWorkbook.SharedWorkspace.Connected Then
                'Delete the file on the SharePoint server
                ActiveWorkbook.SharedWorkspace.RemoveDocument
            End If
            'Save to another location before closing
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs ("C:\Users\Public\DO_NOT_USE.xlsm")
            Application.DisplayAlerts = True
            'Close the workbook
            Workbooks(delete).Close
        End If
    End If

'    ActiveWorkbook.Close
Else
    Exit Sub
End If

End Sub
Microsoft 365 and Office | Development | Other
Microsoft 365 and Office | SharePoint | For business | Windows
Developer technologies | Visual Basic for Applications
0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.