• Use of FileSearch in VBA

    • This topic has 3 replies, 3 voices, and was last updated 24 years ago.
    Author
    Topic
    #355505

    Good evening

    Can someone pls offer an explanation regarding the following piece of code
    :
    1:The routine initially reports a filecount of zero, returns an error “subscript out of range”
    Rerun the script and all is well. It almost appears as if there is a need to pause so that the something can catch up ????

    2: The call to “RegisterDocument” reads the document properties and updates an access datatable. The data is on another server and the routine completes 235 ( yes 235) loops then the database stops updating the records.
    My question is “Can MS Access run out of “Buffer” if the server cannot update the records at the speed of the routine opening & reading the document properties …? The offending document can be processed alone ?!!!! nuts

    Meantime I will continue with the assumption that I have a logic error somewhere the routine that populates the database.
    ——————–
    Sub BulkUpDateDataBase()
    Dim FS
    Dim i As Integer
    Set FS = Application.FileSearch
    Dim strFileName()
    Application.ScreenUpdating = False
    With FS.PropertyTests
    .Add Name:=”Files of Type”, _
    Condition:=msoConditionFileTypeWordDocuments, _
    Connector:=msoConnectorOr
    End With

    ReDim strFileName(FS.FoundFiles.Count)
    MsgBox (FS.FoundFiles.Count)
    With FS
    .LookIn = “C:DataWordDocs”
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
    For i = 1 To .FoundFiles.Count
    strFileName(i) = .FoundFiles(i)
    Documents.Open (strFileName(i))
    RegisterDocument
    ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
    Next i
    Else
    MsgBox “There were no files found.”
    End If
    End With
    End Sub
    ———-
    thanks in advance
    Geof

    Viewing 1 reply thread
    Author
    Replies
    • #524517

      On the first question, I think the ReDim needs to be done after you run the search. The only possible explanation for this not failing every time is that you omitted “Set FS = Nothing” at the end, and somehow the value was retained between calls to the sub. Not sure exactly when that happens; I thought only if your variable was declared as Static.

      No coherent thoughts on the second question.

      • #524521

        The FileSearch values do get retained between calls (possibly even between sessions) so each FileSearch should have an:
        FS.NewSearch
        at the start to clear the old settings.

    • #524525

      Thanks for the tips folks
      Moving the Redim definitely tidied up the initial zero count.
      Setting FS to nothing cured a problem that I had forgotten about.
      I will set FileSearch to New each time.

      On tha Access front, I will assume the problem lies between the keyboard and the back of the chair for a while yet.
      Cheers
      G

    Viewing 1 reply thread
    Reply To: Use of FileSearch in VBA

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: