Check In a Workbook Without Closing

MarkFromSurrey

New Member
Joined
Mar 4, 2014
Messages
2
So, been googling for ages now and not found anything that helps.

My situation is that I have a SharePoint list which is used to track project activities. The team update the SharePoint list and I have an Excel spreadsheet generated from one of the views of the list that is then used as a weekly report.

I have a Macro which lives in the Excel spreadsheet (let's call this the master WB) which updates the sheet, copies the contents to a new workbook and formats that as the report file (which then gets distributed - not all the recipients can have access to SharePoint).
Now, that all works well and I am happy with that, BUT I would like the original (Master WB) which lives on SharePoint to check itself back in to SharePoint to remove the need to do so manually.

So using something along the lines of:
ActiveWorkbook.Checkin True, "Some comment about checking in"

Will check the workbook in, BUT it will also immediately close the workbook, and stop processing any more VBA. Sometimes the comment is added to the version history, sometimes not.

I would like to check in and continue running the master workbook as there are a couple of things I would like the macro to do after checking in. I know I could move the checkin comment to the very end, but that does not really fit with the flow of what I am doing, and I don't like the idea of the code just being cut like that (rather than completing gracefully).

Is there a way to perform the check in without closing the file? I know I could do this from a different Excel Workbook (move the whole macro to another workbook and run against the master WB) but I would prefer not to do this, as if I (we) can get round the checkin and close issue then I have a few more situations where it would be really useful.

Thanks in advance for any help.

Regards,
Mark
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I stumbled upon this situation too.

What I ended up doing was:

1) Create a global variable to check if the closing event came from the CheckIn at the top of a module:

Code:
Global bSharePointCheck As Boolean

2) In the CheckIn procedure I added this line:
Code:
bSharePointCheck = True

3) In the Workbook module / code I added this lines:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If bSharePointCheck = True Then
        bSharePointCheck = false
        Cancel = True
    End If
End Sub

Hope it helps!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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