Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Use of FileSearch in VBA

    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 ?!!!! <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

    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 = "CataWordDocs"
    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

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Use of FileSearch in VBA

    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.

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Use of FileSearch in VBA

    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.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Use of FileSearch in VBA

    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

Posting Permissions

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