CheckIn SharePoint Excel file fail

D

ddander54

Board Regular
Joined
Oct 18, 2012
Messages
97
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.

VBA Code: 
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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.
 
Upvote 0
Micron said:
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.
Click to expand...
Micron,

Thanks for your response.

I get the same error...see below.
 

Attachments

  • Method Error.jpg
    Method Error.jpg
    48.6 KB · Views: 14
Upvote 0
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).
 
Upvote 0
Micron said:
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).
Click to expand...
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.

VBA Code: 
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
 
Upvote 0
You have it backwards in the second line?
VBA Code: 
    If Workbooks(docCheckIn).CanCheckIn = True Then  '<<<<Modified this
        Workbooks.CheckIn docCheckIn
VBA Code: 
    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
learn.microsoft.com

Workbook.CanCheckIn method (Excel)

Office VBA reference topic
learn.microsoft.com
 
Upvote 0
Micron said:
You have it backwards in the second line?
VBA Code: 
    If Workbooks(docCheckIn).CanCheckIn = True Then  '<<<<Modified this
        Workbooks.CheckIn docCheckIn
VBA Code: 
    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
learn.microsoft.com

Workbook.CanCheckIn method (Excel)

Office VBA reference topic
learn.microsoft.com
Click to expand...
Micron,
Sorry, still not working, but now a different error......
VBA Code: 
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
 
Upvote 0
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.
 
Upvote 0
Micron said:
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.
Click to expand...
Micron,

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

Don
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA to save file to SharePoint and attach to outlook template saved in SharePoint
Replies
6
Views
240
Chris8630
C
S
  • Question Question
How to shorten file name which is used to name the sheet name in a master file using VBA?
Replies
4
Views
130
mumps
mumps
M
  • Question Question
Share excel file on Sharepoint using VBA
Replies
1
Views
1K
mariyarashkova
M
A
  • Solved
Extract and Merge Files
Replies
1
Views
131
rowebca
rowebca
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
182
sijpie
sijpie

Forum statistics

Threads
1,224,504
Messages
6,179,142
Members
452,892
Latest member
JUSTOUTOFMYREACH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top