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

RJPotts

Board Regular
Joined
Apr 11, 2007
Messages
143
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
You are using Excel constants when late bound. Mayby try:

Rich (BB code):
NewestVers = .Find(What:=ThisDocName, After:=.Cells(1, 1), _
    LookIn:=-4163, LookAt:=1, SearchOrder:=1, _
    SearchDirection:=1, MatchCase:=False).Offset(0, 5)
 
Upvote 0
You are using Excel constants when late bound. Mayby try:

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,211,983
Messages
6,105,215
Members
447,956
Latest member
haluxuxjr

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