Check in file on sharepoint using VBA

[Spyke831]

Hi,the following code fails at - Workbooks(filePath).CheckInwith the following errorrun-time error '9'subscript out of rangeI cant see why this is happening. I am using Excel 2003 and MOSS 2007 (as far as i can tell)<Code>Private Sub ArchiveRecord() Dim RemoveDate As String, filePath As String Unload UserForm1 RemoveDate = Format((Date + 1095), "dd-mmm-yy") Application.ScreenUpdating = False filePath = ThisWorkbook.path & "/Book2.xls" Workbooks.CheckOut filePath Workbooks.Open (filePath) Workbooks(ThisWorkbook.Name).Sheets("Test1").Copy After:=Workbooks("Book2.xls").Sheets("Sheet1") ActiveSheet.Name = ActiveSheet.Name & " (Delete on " & RemoveDate & ")" Workbooks(filePath).CheckIn Application.ScreenUpdating = TrueEnd Subany help greatly received.Matt

 

[NewOrderFac33]

I had LOADS of trouble with checking in before I discovered a very odd way to get around it.
1. I made sure that I made a change to the workbook (but not one that altered any data. The one I always found successful was to do a column AutoFit thus:

Range("A:A").EntireColumn.AutoFit

It didn't seem to matter as to which column I applied the command to - just that it worked.

2. In my particular application, as the CheckIn was on the Active Workbook (which would obviously close it) I made sure that

ActiveWorkbook.Checkin

was the last line in my procedure.

I don't know WHY 1. works - I just know that it does!

Hope this helps

Pete

P.S. On here, don't forget to begin and end your code with HTML tags, to make it read more clearly.
OpenSquareBracket ([) followed by the word "code" (not in quotes) + CloseSquareBracket (])
THEN YOUR CODE
followed by:
OpenSquareBracket ([) + "/code" (not in quotes) + CloseSquareBracket (])

 

[Spyke831]

Pete,Thanks for the reply, I now get the following error - Runtime Error '1004' Method 'CheckIn of object '_Workbook' failed. The altered code is below. (Thanks for the code tip for posts too).

Private Sub ArchiveRecord()Dim RemoveDate As String, filePath As StringUnload UserForm1RemoveDate = Format((Date + 1095), "dd-mmm-yy")Application.ScreenUpdating = FalsefilePath = ThisWorkbook.path & "/Book2.xls"Workbooks.CheckOut filePathWorkbooks.Open (filePath)Workbooks(ThisWorkbook.Name).Sheets("Test1").Copy After:=Workbooks("Book2.xls").Sheets("Sheet1")ActiveSheet.Name = ActiveSheet.Name & " (Delete on " & RemoveDate & ")"Application.ScreenUpdating = TrueActiveWorkbook.CheckInEnd Sub

Thanks for the helpMatt

 

[Spyke831]

Sorry Pete dont know why the code formated like that I will try again

 

[Spyke831]

Private Sub ArchiveRecord()
Dim RemoveDate As String, filePath As String
Unload UserForm1
RemoveDate = Format((Date + 1095), "dd-mmm-yy")
Application.ScreenUpdating = False
filePath = ThisWorkbook.path & "/Book2.xls"
Workbooks.CheckOut filePath
Workbooks.Open (filePath)
Workbooks(ThisWorkbook.Name).Sheets("Test1").Copy After:=Workbooks("Book2.xls").Sheets("Sheet1")
ActiveSheet.Name = ActiveSheet.Name & " (Delete on " & RemoveDate & ")"
Application.ScreenUpdating = True
ActiveWorkbook.CheckIn
End Sub

 

[NewOrderFac33]

I AM looking at this - unfortunately, I've just locked up a critical workbook during my testing, so may be a little while coming back to you with a solution!

 

[NewOrderFac33]

Good afternoon, Spyke,
See if you can make use of any of my solution below which checks if something can be checked out before doing so, then checks if it can be checked back in again, before doing so.

'The MyWorkbook variable MUST be declared outside CheckItOut and CheckItIn to make it available to both.
Public MyWorkbook As Workbook


Sub CheckItOut()
    
    Dim FilePath As String
    Dim MasterWorkbook As String
    
    On Error GoTo ErrorCheckOut
    
    MasterWorkbook = ActiveWorkbook.Name
    FilePath = "\\SharePoint\Sites\Dev\Build\BR\Project Plans\BRP-004 Product Database (ALL)\01 Project Plan\Test Workbook.xlsm"
    
    If Workbooks.CanCheckOut(FilePath) = True Then
        MsgBox (FilePath & Chr(10) & Chr(10) & "CAN be checked out.")
        Workbooks.CheckOut FilePath                 'Check out workbook
        Set MyWorkbook = Workbooks.Open(FilePath)   'Open workbook & set MyWorkbook variable to allow CheckIn by CheckItIn proc.
        Workbooks(MasterWorkbook).Activate          'Reactivate caller workbook
        Exit Sub
    Else
        MsgBox (FilePath & Chr(10) & Chr(10) & "can NOT be checked out at this time. Please try again later.")
        Exit Sub
    End If
    
ErrorCheckOut:
    MsgBox ("A CheckOut error occurred!")

End Sub


Sub CheckItIn()
    
    On Error GoTo ErrorCheckIn
    
    MyWorkbook.Sheets("Sheet1").Range("A1").EntireColumn.AutoFit
    
    'The workbook's name was stored to "MyWorkbook" by CheckItOut - this variable is now used to check the workbook back in.
    If MyWorkbook.CanCheckIn = True Then
        MsgBox (MyWorkbook.Name & Chr(10) & Chr(10) & "CAN be checked in.")
        MyWorkbook.CheckIn                          'Check in workbook
        Exit Sub
    Else
        MsgBox (MyWorkbook.Name & Chr(10) & Chr(10) & "can NOT be checked in at this time. Please try again later.")
        Exit Sub
    End If

ErrorCheckIn:
    MsgBox ("A CheckIn error occurred!")

End Sub

 

[Spyke831]

Thanks Pete I will try this as soon as I get back into work tomorrow<BR>and let you know the outcome<br> Spyke