File Properties of Web Documents

[bedsingar]

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm trying to integrate the use of SharePoint into our monthly working practices and need a way to be able to mark a document as processed. <o></o>
<o></o>
The majority of the important documents will be in xls format so it makes sense to initially focus on XL for a solution although I'm fairly confident it will work on WD also. <o></o>
<o></o>
Essentially my SharePoint site (SP) has an additional column in the document Library called 'processed'.<o></o>
<o></o>
When an XL file is opened from SP, users can manually modify this property by going to file, properties & then ticking the box next to the property "processed". <o></o>
<o></o>
What I need to know is how to use VB to automate that process - I.E switching it between processed & not processed - & report via way of propmpt window what the current variant is. <o></o>
<o></o>
The last bit is easy - so all I need is the code to change the property. <o></o>
<o></o>
Any Ideas ? (P.S tried recorder & it doesn't record any actions for this process)<o></o>
<o></o>
Thanks<o></o>

 

[Andrew Poulsom]

Welcome to MrExcel.

Which tab of the Properties dialog is used? Maybe take a look at the CustomDocumentProperties Property in VBA Help.

 

[Comfy]

Here's some old code that I used to use, transfered to C# now.

This would take info entered in an XL book, open a WD change the inbuilt properties and move the file to a folder location.

You shoulod be able to adjust it for your needs and change it from inbuilt properties to custom properties, if you google this you should find loads of info.

Sub WorkFlowChange()
    
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim i As Integer
    Dim j As Integer
    Dim FSOobj As Object
    Dim StrFileName As String
    Dim infoFile As String
    Dim MyCompany As String
    Dim strMsg As String
    Dim strMsg2 As String
    Dim strMsg3 As String
    Dim UpdateRng As Long
    Dim AuthorName As String
    Dim myComment As String
    Dim myDept As String

    On Error Resume Next
    
Application.ScreenUpdating = False

    Set FSOobj = CreateObject("Scripting.FilesystemObject")
    strMsg = "Do you wish to Overwrite the existing file?"
    strMsg2 = "Have you saved the information file to C: with the name " & infoFile & " ?"
    strMsg3 = "Have you opened the file and enter the changes made into the properties?"
    UpdateRng = Columns(3).Find("*", SearchDirection:=xlPrevious).Row

For j = 2 To UpdateRng
    StrFileName = Cells(j, 1).Value & ".doc"
    MyCompany = Cells(j, 2).Value
    myDept = Cells(j, 3).Value
    infoFile = Cells(j, 4).Value & ".doc"
    AuthorName = Cells(j, 5).Value
    myComment = Cells(j, 6).Value
    
    If StrFileName = "" & ".doc" Then
        MsgBox ("File Name cannot be Blank")
    ElseIf MyCompany = "" Then
        MsgBox ("Company cannot be Blank")
    ElseIf myDept = "" Then
        MsgBox ("Folder Names cannot be Blank")
    ElseIf infoFile = "" & ".doc" Then
        MsgBox ("Info Doc Filename cannot be Blank")
    ElseIf AuthorName = "" Then
        MsgBox ("Changes by cannot be Blank")
    ElseIf myComment = "" Then
        MsgBox ("Changes Made cannot be Blank")
    Else
        FSOobj.CreateFolder "C:\" & MyCompany & "\" & myDept 'Directory where folder will be placed
        Set wrdApp = CreateObject("Word.Application")
        wrdApp.Visible = False
            Set wrdDoc = wrdApp.Documents.Open("C:\" & infoFile) 'Word File Location
        With wrdDoc
            For i = 1 To 1
                .BuiltinDocumentProperties("Title").Value = ""
                .BuiltinDocumentProperties("Author").Value = AuthorName
                .BuiltinDocumentProperties("Comments").Value = myComment
            Next i
            If Dir("C:\" & MyCompany & "\" & myDept & "\" & StrFileName) = "" Then
            .SaveAs ("C:\" & MyCompany & "\" & myDept & "\" & StrFileName)
            .Close ' close the document
                Else
                Select Case MsgBox(strMsg, vbYesNoCancel)
                Case vbYes
                'If Yes was chosen, save and overwrite existing file.
                .SaveAs ("C:\" & MyCompany & "\" & myDept & "\" & StrFileName)
                ActiveDocument.Close
    
                Case vbNo
Filename:
                NameReplace = InputBox("Please enter the new File Name", "Rename File", StrFileName, 11000, 5500) 'Sets msg box and its placement on the screen
                        If Dir("C:\" & MyCompany & "\" & myDept & "\" & NameReplace) = "" Then
                        .SaveAs ("C:\" & MyCompany & "\" & myDept & "\" & NameReplace)
                        .Close
                            Else
                            Select Case MsgBox(strMsg, vbYesNoCancel)
                            Case vbYes
                            'If Yes was chosen, save and overwrite existing file.
                            .SaveAs ("C:\" & MyCompany & "\" & myDept & "\" & NameReplace)
                            ActiveDocument.Close
                                    Case vbCancel
                                    ActiveDocument.Close
                                        Case vbNo
                                         GoTo Filename
                            End Select
                        End If
            Case vbCancel
            ActiveDocument.Close ' If Cancel is chosen, close document
        End Select
    End If
        End With
        wrdApp.Quit ' close the Word application
        Set wrdDoc = Nothing
        Set wrdApp = Nothing
    
    End If
Next j
    Application.ScreenUpdating = True
    
        MsgBox "VBA Complete"
myExit:
End Sub

This was written in 2003 and quite some time ago now and it's far from perfect. but it did the job at the time

/Comfy

 

[bedsingar]

Cheers guys,

Heres the working code incase anyone needs it in the future

Sub docpropertyprocessed()
Dim PromptMsg As String
Dim response As Variant

On Error Resume Next

Application.ScreenUpdating = False

'prompt for comment:

PromptMsg = "Please enter comment here "
response = Application.InputBox(PromptMsg, "Add Comment", "")

If Not response = False Then
GoTo adjustproperties
Else
pt = MsgBox("User clicked Cancel", vbCritical, "No Comment Added")
End If

adjustproperties:

'adjust properties of file

ActiveWorkbook.CustomDocumentProperties("Processed").Value = 1
ActiveWorkbook.BuiltinDocumentProperties("Comments").Value = response


'save workbook as existing name

ActiveWorkbook.Save

If ActiveWorkbook.CustomDocumentProperties("Processed").Value = 1 Then
MsgBox ("Status Updated to Processed")
Else
MsgBox ("That didn't work - Check that property Processed exisits on SP Document Library")

End If
Application.ScreenUpdating = True

End Sub