Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clearing a Variable When File Closed (Word 2003)

    Thanks to the help I have already received, I have been able to modify the multifile Find and Replace macro so that it works almost perfectly. But I have been unable to solve one last item. How to have the macro reset the FileDialogFolderPicker to Nothing when the document is closed instead of when Word is exited? Currently, the code is as follows:

    ' If this is the first time this macro is run, ask for directory input

    If NotFirstTime = False Then

    With Application.FileDialog(msoFileDialogFolderPicker)

    Dim Msg As String
    Msg = "Select the directory where the other files to be searched are located."

    Response = MsgBox(Msg, vbInformation + vbOKCancel, "Location of Find and Replace Files")
    If Response = vbCancel Then
    Exit Sub
    End If

    ' Set folder to start in

    .InitialFileName = "D:"
    If .Show = True Then
    strFolder = .SelectedItems(1)
    End If
    End With
    . . . . . .

    ' No longer first time
    NotFirstTime = True

    Else

    ' If not the first time
    strFindText = InputBox("Enter the text to find.")
    If strFindText = "" Then
    Exit Sub
    End If
    . . . . . .

    With this code, the user only has to select the folder where the other documents are located once while working on a document. But if the user finishes working on document A , closes it, and opens document B, when the macro is run again, the user is not asked to choose the folder -- the macro continues to look in the same folder as it did with document A unless Word is exited before opening document B and running the macro on document B. I would appreciate any suggestions. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Clearing a Variable When File Closed (Word 2003)

    You can do this by modifying the value of a variable in a DocumentChange application event. This would allow you to change the value whenever the user switches documents.

    You can find a few examples of how to create an application event by searching this forum for WithEvents. You would also need to store your variable somewhere that both the DocumentChange event and the other module could access it - probably easiest to just create a Global variable.

    StuartR

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing a Variable When File Closed (Word 2003)

    <P ID="edit" class=small>(Edited by JanB on 13-Dec-05 08:10. corrected typo)</P>You could store the value of NotFirstTime in a CustomDocumentProperty and use that value to decide what the macro should do. I wrote this code from the top of my head, so isn't tested, but you'll get an idea. I always use stringvalues in CustomDocumentValues, so i can make the routines generic.

    Dim strNotFirstTime As String
    Dim strCDPname As String
    strCDPname = "FirstTime"
    strNotFirstTime = GetValueOfCDP(strCDPname)
    If NotFirstTime <> "True" Then
    'Your code
    SetValueOfCDP strCDPname, "True"
    Else
    'Your code
    End If
    ...
    ...

    Sub SetValueOfCDP(strName As String, strValue As String)
    If Not ExistsCDP(strName) Then
    ActiveDocument.CustomDocumentProperties.Add Name:=strName, _
    Value:=strValue, LinkToContent:=False, Type:=msoPropertyTypeString
    Else
    ActiveDocument.CustomDocumentProperty(strName).Val ue = strValue
    End If
    End Sub

    Public Function GetValueOfCDP(strName As String) As String
    Dim prop As DocumentProperty
    GetValueOfCDP = ""
    If Documents.Count < 1 Then Exit Function 'To prevent an error if no doc present
    For Each prop In ActiveDocument.CustomDocumentProperties
    If UCase(prop.Name = Ucase(strName) Then
    GetValueOfCDP = ctiveDocument.CustomDocumentProperty(strName).Valu e
    Exit For
    End If
    Next prop
    Set prop = Nothing
    End Function

    Function ExistsCDP(strName As Variant) As Boolean
    Dim prop As DocumentProperty
    ExistsCDP = False
    If Documents.Count < 1 Then Exit Function 'To prevent an error if no doc present
    For Each prop In ActiveDocument.CustomDocumentProperties
    If UCase(prop.Name = Ucase(strName) Then
    ExistsCDP = True
    Exit For
    End If
    Next prop
    Set prop = Nothing
    End Function

  4. #4
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing a Variable When File Closed (Word 2003)

    Thanks, Stuart. I will search the WithEvents as you suggest. I haven't yet used application events, so it will be a new adventure for me.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2005
    Location
    Poughkeepsie, New York, USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clearing a Variable When File Closed (Word 2003)

    Thank you, Jan. I appreciate the code that I can use as a guide. It is clear that I have a very long way to go for even a semblance of mastering VBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •