CheckIn SharePoint Excel file fail

[ddander54]

This code works perfectly for me to check if the file can be checked out. The problem is the CheckIn part....I'm getting an error: Method or Data Member not found and CanCheckIn is highlighted. I have tried substituting the FileName for the docCheckIn variable as suggested below without any luck as well. Any help would be appreciated.

Option Explicit

'https://stackoverflow.com/questions/51606517/how-to-check-in-a-file-on-sharepoint
'https://stackoverflow.com/questions/55120943/sharepoint-checkin-file-method-or-data-member-not-found
  'Set WbObj = Workbooks("workbookname.xls") 'if it's open
  'Set WbObj = Workbooks.Open(docCheckIn)    'if it isn't open
  'edit: Workbooks(docCheckIn).CanCheckIn will work but the docCheckIn must be a workbook name, not full path. Alternatively set the workbook as object using your docCheckIn path and use WbObj.CanCheckIn
  'edit2: bear in mind that the Workbooks.checkin docCheckIn will have to be changed the same way to either Workbooks("workbookname.xls").checkin or WbObj.checkin

Sub SharePointCheckOutIn()
    Dim docCheckOut As String
    Dim docCheckIn As String
    'docCheckIn = "WorkMix_Re-Forecast_2022.xlsx"
    docCheckOut = "http://kb/sites/PMO/Metrics and Reporting/2022 Financials/WorkMix_Re-Forecast_2022.xlsx"
    Call UseCheckOut(docCheckOut)
    Call UseCheckIn(docCheckIn)
End Sub

Sub UseCheckOut(docCheckOut As String)
     ' Determine if workbook can be checked out.
    If Workbooks.CanCheckOut(docCheckOut) = True Then
        Workbooks.CheckOut docCheckOut
    Else
        MsgBox "Unable to check out this document at this time."
    End If
End Sub

Sub UseCheckIn(docCheckIn As String)
    docCheckIn = "WorkMix_Re-Forecast_2022.xlsx"
     ' Determine if workbook can be checked in.
    If Workbooks.CanCheckIn(docCheckIn) = True Then  '<<<<Method or Data Member Not Found and CanCheckIn is highlighted
        Workbooks.CheckIn docCheckIn
    Else
        MsgBox "Unable to check in this document at this time."
    End If
End Sub

Thanks in Advance,
Don

 

[Micron]

Because you're not following Workbooks with a valid workbook name?

Workbooks(docCheckIn).CanCheckIn ?

Why not pass the workbook name to the sub rather than set the variable value inside the sub?
UseCheckIn "WorkMix_Re-Forecast_2022.xlsx" <<you only need parentheses if you use Call.

 

[ddander54]

Because you're not following Workbooks with a valid workbook name?

Workbooks(docCheckIn).CanCheckIn ?

Why not pass the workbook name to the sub rather than set the variable value inside the sub?
UseCheckIn "WorkMix_Re-Forecast_2022.xlsx" <<you only need parentheses if you use Call.

Micron,

Thanks for your response.

I get the same error...see below.

 

[Micron]

That's not what I posted. You still have it backwards.
Please don't post pics of code, post code and use code tags (vba button on posting toolbar).

 

[ddander54]

That's not what I posted. You still have it backwards.
Please don't post pics of code, post code and use code tags (vba button on posting toolbar).

Micron,

Sorry, I was only trying to show the yellow and blue highlighting incase they meant something different to you that what I'm describing.

If I (newb) understand your reply correctly I modified 2 lines and still get the same error.

Option Explicit

'https://stackoverflow.com/questions/51606517/how-to-check-in-a-file-on-sharepoint
'https://stackoverflow.com/questions/55120943/sharepoint-checkin-file-method-or-data-member-not-found
  'Set WbObj = Workbooks("workbookname.xls") 'if it's open
  'Set WbObj = Workbooks.Open(docCheckIn)    'if it isn't open
  'edit: Workbooks(docCheckIn).CanCheckIn will work but the docCheckIn must be a workbook name, not full path. Alternatively set the workbook as object using your docCheckIn path and use WbObj.CanCheckIn
  'edit2: bear in mind that the Workbooks.checkin docCheckIn will have to be changed the same way to either Workbooks("workbookname.xls").checkin or WbObj.checkin

Public Sub SharePointCheckOutIn()
    Dim docCheckOut As String
    Dim docCheckIn As String
    docCheckOut = "http://kb/sites/PMO/Metrics and Reporting/2022 Financials/WorkMix_Re-Forecast_2022.xlsx"
    Call UseCheckOut(docCheckOut)
    'Call UseCheckIn(docCheckIn)
    UseCheckIn "WorkMix_Re-Forecast_2022.xlsx"  '<<<<Modified this
End Sub

Sub UseCheckOut(docCheckOut As String)
     ' Determine if workbook can be checked out.
    If Workbooks.CanCheckOut(docCheckOut) = True Then
        Workbooks.CheckOut docCheckOut
    Else
        MsgBox "Unable to check out this document at this time."
    End If
End Sub

Sub UseCheckIn(docCheckIn As String)
     ' Determine if workbook can be checked in.
    If Workbooks(docCheckIn).CanCheckIn = True Then  '<<<<Modified this
        Workbooks.CheckIn docCheckIn
    Else
        MsgBox "Unable to check in this document at this time."
    End If
End Sub

Don

 

[Micron]

You have it backwards in the second line?

    If Workbooks(docCheckIn).CanCheckIn = True Then  '<<<<Modified this
        Workbooks.CheckIn docCheckIn

    If Workbooks(docCheckIn).CanCheckIn = True Then  '<<<<Modified this
        Workbooks(docCheckIn).CheckIn

It has been a while since I've done this, but that's what I think your issue is. See

Workbook.CanCheckIn method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[ddander54]

You have it backwards in the second line?

    If Workbooks(docCheckIn).CanCheckIn = True Then  '<<<<Modified this
        Workbooks.CheckIn docCheckIn

    If Workbooks(docCheckIn).CanCheckIn = True Then  '<<<<Modified this
        Workbooks(docCheckIn).CheckIn

It has been a while since I've done this, but that's what I think your issue is. See

Workbook.CanCheckIn method (Excel)

Office VBA reference topic

learn.microsoft.com

Micron,
Sorry, still not working, but now a different error......

Sub UseCheckIn(docCheckIn As String)
     ' Determine if workbook can be checked in.
    If Workbooks(docCheckIn).CanCheckIn = True Then  '<<<< Code stops on this line Error Runtime error '9': Subscript out of Range.
        Workbooks(docCheckIn).CheckIn                           '<<<<< Changed per your suggestion
    Else
        MsgBox "Unable to check in this document at this time."
    End If
End Sub

Don

 

[Micron]

Different error usually means progress, inasmuch that progress is fixing one problem and finding the next problem. Add this temporarily and see what the message is

' Determine if workbook can be checked in.
msgbox docCheckin
Stop

you must disable or delete those lines when/if it is solved.

 

[ddander54]

Different error usually means progress, inasmuch that progress is fixing one problem and finding the next problem. Add this temporarily and see what the message is

' Determine if workbook can be checked in.
msgbox docCheckin
Stop

you must disable or delete those lines when/if it is solved.

Micron,

Thanks for hanging in there with me. The Message Box displays the filename. WorkMix_Re-Forecast_2022.xlsx

Don

 

[Micron]

Is all of this code in the same worksheet module, or have you cobbled it together from other places?

EDIT - The workbook does exist, yes? And the path is correct?