'Translating' Excel 2003 Macro into Word 2003 - Run-time error 424

[RJPotts]

Hi everyone,

Apologies first, I couldnt see a way of preventing word-wrap in the code posted below.

I've created a macro in Excel which will check the version number of the workbook (set / stored by a separate macro, as a custom defined property) against the latest version of that file from a log of documents. In Excel it's working fine (currently as a macro but will be moved to become an add-in when finished) and the code is as follows;

<code>
Sub VersionCheckMacro()
Dim ThisDocName
Dim ThisDocVers
Dim NewestVers

ThisDocName = Application.ActiveWorkbook.CustomDocumentProperties("Name").Value
ThisDocVers = Application.ActiveWorkbook.CustomDocumentProperties("Version").Value

Application.ScreenUpdating = False

Workbooks.Open Filename:=ThisWorkbook.Path & "\Document Version Log.xls", ReadOnly:=True

If WorksheetFunction.CountIf(Sheets("Data").Range("C:C"), ThisDocName) > 0 Then
With Sheets("Data").Range("C:C")

NewestVers = .Find(What:=ThisDocName, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 5)
End With

ActiveWorkbook.Close False

Application.ScreenUpdating = True

If NewestVers > ThisDocVers Then
MsgBox "Document Name: " & ThisDocName & vbCrLf & vbCrLf & "Document Version: " & ThisDocVers & vbCrLf & vbCrLf & "Newest Version: " & NewestVers & vbCrLf & vbCrLf & "Your version of this file has been superceded." & vbCrLf & "Please refer to the Document Version Log" & vbCrLf & "to find the latest version."
End If

If NewestVers = ThisDocVers Then
MsgBox "Document Name: " & ThisDocName & vbCrLf & vbCrLf & "Document Version: " & ThisDocVers & vbCrLf & vbCrLf & "Newest Version: " & NewestVers & vbCrLf & vbCrLf & "Your version of this file matches the newest" & vbCrLf & "version according to the Document Version" & vbCrLf & "Log and is suitable for use."
End If

If NewestVers < ThisDocVers Then
MsgBox "Document Name: " & ThisDocName & vbCrLf & vbCrLf & "Document Version: " & ThisDocVers & vbCrLf & vbCrLf & "Newest Version: " & NewestVers & vbCrLf & vbCrLf & "Your version of this file is newer than the latest" & vbCrLf & "version entered on the Document Version Log." & vbCrLf & "Please update the log file!"
End If

Else:

If WorksheetFunction.CountIf(Sheets("Data").Range("C:C"), ThisDocName) = 0 Then
MsgBox "This document name is not recorded" & vbCrLf & "in the Document Version Log. Please" & vbCrLf & "update the log as required."
ActiveWorkbook.Close False
Application.ScreenUpdating = True
End If

End If

End Sub
</code>

I've been asked to include Word documents in the project. I've been able to make a few changes to the code above & I can successfully open the Excel workbook containing the document version log but I'm struggling with 'translating' the Find function. The code I have in Word so far is;

<code>
Sub VersionCheckMacro()
Dim ThisDocName
Dim ThisDocVers
Dim NewestVers

ThisDocName = Application.ActiveDocument.CustomDocumentProperties("Name").Value
ThisDocVers = Application.ActiveDocument.CustomDocumentProperties("Version").Value

'Application.ScreenUpdating = False

Dim xlApp As Object
Dim xlBook As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName:=ThisDocument.Path & "\Document Version Log.xls")
xlApp.Visible = True

<b>If WorksheetFunction.CountIf(xlBook.Sheets("Data").Range("C:C"), ThisDocName) > 0 Then</b>
With xlBook.Sheets("Data").Range("C:C")

NewestVers = .Find(What:=ThisDocName, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 5)
End With

ActiveWorkbook.Close False

'Application.ScreenUpdating = True

If NewestVers > ThisDocVers Then
MsgBox "Document Name: " & ThisDocName & vbCrLf & vbCrLf & "Document Version: " & ThisDocVers & vbCrLf & vbCrLf & "Newest Version: " & NewestVers & vbCrLf & vbCrLf & "Your version of this file has been superceded." & vbCrLf & "Please refer to the Document Version Log" & vbCrLf & "to find the latest version."
End If

If NewestVers = ThisDocVers Then
MsgBox "Document Name: " & ThisDocName & vbCrLf & vbCrLf & "Document Version: " & ThisDocVers & vbCrLf & vbCrLf & "Newest Version: " & NewestVers & vbCrLf & vbCrLf & "Your version of this file matches the newest" & vbCrLf & "version according to the Document Version" & vbCrLf & "Log and is suitable for use."
End If

If NewestVers < ThisDocVers Then
MsgBox "Document Name: " & ThisDocName & vbCrLf & vbCrLf & "Document Version: " & ThisDocVers & vbCrLf & vbCrLf & "Newest Version: " & NewestVers & vbCrLf & vbCrLf & "Your version of this file is newer than the latest" & vbCrLf & "version entered on the Document Version Log." & vbCrLf & "Please update the log file!"
End If

Else:

If WorksheetFunction.CountIf(xlBook.Sheets("Data").Range("C:C"), ThisDocName) = 0 Then
MsgBox "This document name is not recorded" & vbCrLf & "in the Document Version Log. Please" & vbCrLf & "update the log as required."
ActiveWorkbook.Close False
Application.ScreenUpdating = True
End If

Set xlBook = Nothing
Set xlApp = Nothing

End If
</code>

Thats giving me a 'Run-time error '424': Object required' message on the following part of the code:

<code>
If WorksheetFunction.CountIf(xlBook.Sheets("Data").Range("C:C"), ThisDocName) > 0 Then
</code>
(highlighted in 2nd main code example above)

Can anyone please give me any pointers about what I need to change to get this working successfully in Word?

Best regards and in advance, many thanks!
Richard

 

[RJPotts]

Changing:

<code>
If WorksheetFunction.CountIf(xlBook.Sheets("Data").Range("C:C"), ThisDocName) > 0 Then
With xlBook.Sheets("Data").Range("C:C")
</code>

to

<code>
If xlApp.WorksheetFunction.CountIf(xlBook.Sheets("Data").Range("C:C"), ThisDocName) > 0 Then
With xlBook.Sheets("Data").Range("C:C")
</code>

Seems to fix that bit. However, I'm now getting a 'Run-time error '9': Subscript out of range' error message on this bit;

<code>
NewestVers = .Find(What:=ThisDocName, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 5)
</code>

Is anyone -please- able to figure out what / where the problem is there?

Best regards and many thanks
Richard

 

[Andrew Poulsom]

You are using Excel constants when late bound. Mayby try:

NewestVers = .Find(What:=ThisDocName, After:=.Cells(1, 1), _
    LookIn:=-4163, LookAt:=1, SearchOrder:=1, _
    SearchDirection:=1, MatchCase:=False).Offset(0, 5)

 

[RJPotts]

You are using Excel constants when late bound. Mayby try:

NewestVers = .Find(What:=ThisDocName, After:=.Cells(1, 1), _
    LookIn:=-4163, LookAt:=1, SearchOrder:=1, _
    SearchDirection:=1, MatchCase:=False).Offset(0, 5)

Hi Andrew,

Many thanks indeed!! That worked perfectly

I'm quite likely to have a few more components from this project to finish off in Excel and then translate to Word. As an example to your solution LookIn:=xlValues became LookIn=-4163. Will I find that every instance of LookIn:=xlValues will become LookIn=-4163 or is it something that can vary? If it's a constant do you maybe know where I can find a list of similar 'translations'?

Many thanks again!
Richard

 

[Andrew Poulsom]

They are constants. You can find the values in the Excel's Visual Basic Object Browser.

 

[RJPotts]

They are constants. You can find the values in the Excel's Visual Basic Object Browser.

Ah, I see! Apologies for my ignorance, I've never needed to use the VB Object Browser.

Many thanks
Richard