none
VBA Runtime error 91

    Question

  • Hi all, I haev a VBS script which I'm trying to convert to VBA. and I have run into the following error:

    Runtime error 91 -Object Variable or With Block variable not set

    The line of code the debug highlights:

    Set oConfig = oFSO.OpenTextFile(sCaddProjects & oFSO.GetBaseName(oRootFolder) & "_ProjPaths.txt", 2, True)

    I have declared the following variables outwith any sub procedures albeit without giving some of them a type:

    Dim oRootFolder, sCaddProjects, oConfig, i
    Dim oFSO As Scripting.FileSystemObject
    Set oFSO = New Scripting.FileSystemObject
    Set oRootFolder = oFSO.GetFolder(ProjectPath)
    sCaddProjects = F:\V8i_Workspace\projects\

    Any idea what's causing the error?

    Thanks

    Thursday, June 02, 2011 7:50 AM

All replies

  • For Debugging purpose , Could you get the value of sCaddProjects & oFSO.GetBaseName(oRootFolder) & "_ProjPaths.txt"

    statment using Msgbox or immediate window ? I suspect the file name is not formed properly.

    Thursday, June 02, 2011 8:12 AM
  • I wasn't able to so I had to add the variable declaration for sCaddProjects within the sub the error was occurring. This didn't solve the error message but I can see in the intermediate window that sCaddProjects has the correct value. This led me to check the value of oRootfolder. As I said before this is a conversion from a VBS script. The script was invoked by drag & dropping a folder onto the script icon and the code:

    Set oRootFolder = oFSO.GetFolder(WScript.Arguments(0)

    Within the VBA I now am getting the folder path differently using:

    Dim ProjectPath, str_ProjectPath As String
    str_ProjectPath = "$(PROJECTROOT)"
    ProjectPath = ActiveWorkspace.ExpandConfigurationVariable(str_ProjectPath)
    oRootFolder = ProjectPath

    The problem I am now having is when I run the script using

    Call ProcessFolder(oRootFolder)

    Sub ProcessFolder(oFolder)
        LvlF1 = (iLevel = 1) And (InStr(sFilter1, "/" & oFolder.Name & "/") = 0)

    I get an error within the LvlF1 line stating Runtime error 424  - Object required


    Thursday, June 02, 2011 9:26 AM
  • It isn't very clear what you are doing, but you have
     
        oRootFolder = ProjectPath ' a string
     
         ....
     
        LvlF1 = .... oFolder.Name ... ' object needed
     
    (where oFolder is the same variable as oRootFolder)
     
       

    Enjoy,
    Tony
    www.WordArticles.com
    Thursday, June 02, 2011 3:36 PM
  • Hi Tony, the overall picture of what I am doing is trying to get a VB script converted to work as a VBA macro. Along the way I have encountered issues eg VBScript use arraylist, VBA cannot use arraylist etc... What the script does is :

    • Build a recursive tree of subdirectories.
    • Ignore subdirectories at Level 1 other than Architects, Civil, Elec, Mech or Struc
    • Ignore subdirectories at Level 2 unless they start with 01, 02, 03 or 04

    The working VBScript is:

     

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set aPaths = CreateObject("System.Collections.ArrayList")
    bDebug = False
    
    Set oRootFolder = oFSO.GetFolder(WScript.Arguments(0))
    sRootFolder = oRootFolder.Path
    iLength = Len(sRootFolder) + 1
    iLevel = 0
    sFilter1 = "/Architects/Civil/Elec/Mech/Struc/"
    sFilter2 = "/01/02/03/04/"
    
    ProcessFolder oRootFolder
    WriteOutput
    MsgBox "Completed"
    
    '------------------------------
    'Recursively process one folder
    '------------------------------
    Sub ProcessFolder(oFolder)
      bSkip1 = (iLevel = 1) And (InStr(sFilter1, "/" & oFolder.Name & "/") = 0)
      bSkip2 = (iLevel = 2) And (InStr(sFilter2, "/" & Left(oFolder.Name, 2) & "/") = 0)
      if debug _
      then msgbox "iLevel=" & iLevel & VbLf & "Folder name=" & oFolder.name & " " & bSkip1 & " " & bSkip2
    
      If Not bSkip1 and not bSkip2 Then
        sFolderPath = Replace(Mid(oFolder.Path, iLength), "\", "/")
        If Not sFolderPath = "" _
        Then aPaths.Add "DRG_PATH > $(PROJECT_ROOT)$(PROJECT_NAME)" & sFolderPath & "/"
    
        For Each oSubFldr In oFolder.Subfolders
          iLevel = iLevel + 1
          ProcessFolder oSubFldr
          iLevel = iLevel - 1
        Next
      End If
    End Sub
    '-----------------------------------------------------
    'Sort the path array, then write it to the output file
    '-----------------------------------------------------
    Sub WriteOutput
      aPaths.Sort
      Set oConfig = oFSO.OpenTextFile(sRootFolder & "\ProjSync.txt", 2, True)
      oConfig.WriteLine ">PROJECT_NAME = " & sRootFolder
      oConfig.WriteLine "<PROJECT_ROOT = " & Replace(oRootFolder.ParentFolder, "\", "/")
      For Each sPath In aPaths.ToArray
        oConfig.WriteLine sPath
      Next
      oConfig.Close
    End Sub
    
    I don't have the VBA version to hand (and I have made a good few variable name changes since the conversion) but you can see from the above how sub ProcessFolder is called with oRootFolder as a parameter and then the sub itself uses oFolder as a parameter. I hope that clarifies the issue.

     

    Thursday, June 02, 2011 5:18 PM
  • The pertinent parts of the code you've just posted are:
     
        Set oRootFolder = oFSO.GetFolder(WScript.Arguments(0))
        sRootFolder = oRootFolder.Path
        ...
        ProcessFolder oRootFolder
        ...
        Sub ProcessFolder(oFolder)
    oRootFolder is set to be a folder - an object. ProcessFolder is then called with this object being passed, and inside the ProcessFolder routine it is referred to as oFolder. A variable *s*RootFolder is assigned a string value but not used further in what you have posted.
     
    The pertinent parts of the code you posted before are:
     
        Set oRootFolder = oFSO.GetFolder(WScript.Arguments(0)
        ...
        oRootFolder = ProjectPath
        ...
        Call ProcessFolder(oRootFolder)
        ...
        Sub ProcessFolder(oFolder)
    Here (leaving aside the missing right parenthesis), oRootFolder is also set to be a folder - an object. Then, however, you assign a string to the variable - no longer a folder. I suspect you have confused the similarly named variables, oRootFolder and sRootFolder. When ProcessFolder is then called with the oRootFolder STRING being passed, still referred to inside the ProcessFolder routine as oFolder, it is not the required object.
     
    You would help yourself if you (a) used "Option Explicit", and (b) declared all variables explicitly as being of whatever type they are so that the compiler would pick up on this kind of mistake - a very easy one to make.
     

    Enjoy,
    Tony
    www.WordArticles.com
    Thursday, June 02, 2011 7:28 PM
  • Hi again Tony. The missing parenthesis was a copy & paste error. I have also this morning attempted to organise my variable declarations and assigned them a type.

    I no longer use sRootFolder because the VBA Macro is now determining the folder path using the code I posted in my 2nd post of this topic. I believe the following amendment now makes oRootFolder an object once again:

     

    Option Explicit
    '--------------------------------------------------------------
    'Build a recursive tree of subdirectories.
    'Ignore subdirectories at Level 1 other than Architects, Civil, Elec, Mech or Struc
    'Ignore subdirectories at Level 2 unless they start with 01, 02, 03 or 04
    '--------------------------------------------------------------
    'Declare Global variables
    Private oRootFolder As Object
    Private iLength As String
    Private iLevel As String
    Private alPaths As Collection
    
    Private oFSO As Scripting.FileSystemObject
    
    
    Public Sub RunSync()
    'Declare Local variables
    Set oFSO = New Scripting.FileSystemObject
    Dim ProjectPath As String
    Dim str_ProjectPath As String
    str_ProjectPath = "$(_USTN_PROJECTROOT)"
    ProjectPath = ActiveWorkspace.ExpandConfigurationVariable(str_ProjectPath)
    Set oRootFolder = oFSO.GetFolder(ProjectPath)
    
    Call ProcessFolder(oRootFolder)
    WriteOutput
    MsgBox "Completed"
    End Sub
    

    This now appears to be working but at an alarmingly slow rate before yet an error message appears. I'm trying to access the first values in the VBA collection using:

    I am populating the collection using 

     
    Private colPaths As Collection
    Dim sFolderPath AsString
    colPaths.Add strFolderPath & "/"

    I am using a for each loop to iterate through a series of folders and the line above should add each string path to the collection. MSDN says:

    Instances of the Visual Basic Collection class allow you to access an item using either a numeric index or a String key. You can add items to Visual Basic Collection objects either with or without specifying a key. If you add an item without a key, you must use its numeric index to access it.

    So as you can see from my example I am not specifying a key and therefore want to access items via numerical index as I have done when using an arraylist. I attempt to access the items by: 

     
    Dim colListItem AsObject
    Set colListItem = colPaths(1) MsgBox colListItem
     This gives an runtime error '424 'on the second line stating object required yet I've specified that colListItem is an object and the collection should full of various strings. Using msgbox colPaths.count reveals 1 but I would have thought it would contain a far higher number so am I populating the collection wrongly? Is this what is causing the '424' runtime error?
    Friday, June 03, 2011 10:02 AM
  • I did write a reply to your last post but it seems to have got lost in the system and  you seem to be over the first problem, anyway.
     
    Very loosely, there are two types of variables: objects and 'simple' variables. The syntax for working with these is slightly different. To assign an object reference to a variable, you use "Set" and to assign a value to a simple variable you use "Let" (although "Let" is implied if "Set" isn't specified and "Let" is very rarely explicitly used).
     
    Your stetement "Set colListItem = colPaths(1)" uses "Set", so you are telling VBA you want to assign an object reference to colListItem, but colPaths(1) is a string - a simple variable - not an object reference, so it gives the 424 error.
     
     
    As to what is in your collection, where is it defined, where is it initialised, and where is it added to? Are you, perhaps, re-initialising it inside your recursive procedure?
     

    Enjoy,
    Tony
    www.WordArticles.com
    Friday, June 03, 2011 10:24 AM
  • Hi Tony, Here is where I'm at with the VBA Macro perhaps this will answer your questions:

    Option Explicit
    '--------------------------------------------------------------
    'Build a recursive tree of subdirectories.
    'Ignore subdirectories at Level 1 other than Architects, Civil, Elec, Mech or Struc
    'Ignore subdirectories at Level 2 unless they start with 01, 02, 03 or 04
    '--------------------------------------------------------------
    'Declare Global variables
    Private oRootFolder As Object
    Private iLength As String
    Private iLevel As String
    Private colPaths As Collection
    Private oFSO As Scripting.FileSystemObject
    
    Public Sub RunSync()
      'Declare Local variables
      Set oFSO = New Scripting.FileSystemObject
      Dim ProjectPath As String
      Dim str_ProjectPath As String
      str_ProjectPath = "$(_USTN_PROJECTROOT)"
      ProjectPath = ActiveWorkspace.ExpandConfigurationVariable(str_ProjectPath)
      Set oRootFolder = oFSO.GetFolder(ProjectPath)
    
      Call ProcessFolder(oRootFolder)
      WriteOutput
      MsgBox "Completed"
    End Sub
    
    '------------------------------
    'Recursively process one folder
    '------------------------------
    Sub ProcessFolder(oFolder)
      'Declare Local variables
      Dim bDebug As Boolean
      Dim bSkip1 As Integer
      Dim bSkip2 As Integer
      Dim sFilter1 As String
      Dim sFilter2 As String
      Dim sFolderPath As String
      Dim oSubFldr As Object
      Set colPaths = New Collection
      iLength = Len(oRootFolder) + 1
      iLevel = 0
      sFilter1 = "/Architects/Civil/Elec/Mech/Struc/"
      sFilter2 = "/01/02/03/04/"
      bDebug = False
    
    
      bSkip1 = (iLevel = 1) And (InStr(sFilter1, "/" & oFolder.Name & "/") = 0)
      bSkip2 = (iLevel = 2) And (InStr(sFilter2, "/" & Left(oFolder.Name, 2) & "/") = 0)
      If bDebug = True Then MsgBox "iLevel=" & iLevel & vbLf & "Folder name=" & oFolder.Name & vbLf & "bSkip1=" & bSkip1 & vbLf & "bSkip2=" & bSkip2
    
      If Not bSkip1 And Not bSkip2 Then
        sFolderPath = Replace(Mid(oFolder.Path, iLength), "\", "/")
        If Not sFolderPath = "" _
        Then colPaths.Add sFolderPath & "/"
    
          For Each oSubFldr In oFolder.Subfolders
            iLevel = iLevel + 1
            ProcessFolder oSubFldr
            iLevel = iLevel - 1
          Next
    
        End If
    
      End Sub
    
      '-----------------------------------------------------
      'Sort the path array, then write it to the output file
      '-----------------------------------------------------
    
      Public Sub WriteOutput()
        'Declare Local variables
        Dim colListItem As String
        Dim i As Integer
        Dim oConfig As Object
        Dim sCaddProjects As String
        sCaddProjects = "L:\CADD_Standards\V8i_Workspace\projects\"
        Set oConfig = oFSO.OpenTextFile(sCaddProjects & oFSO.GetBaseName(oRootFolder) & "_ProjSync.txt", 2, True)
        colListItem = colPaths(1)
        oConfig.WriteLine "MS_DEF = $(_USTN_PROJECTROOT)$(_USTN_PROJECTNAME)" & colListItem
    
        For i = 1 To colPaths.Count - 1
          oConfig.WriteLine "MS_DEF > $(_USTN_PROJECTROOT)$(_USTN_PROJECTNAME)" & colPaths(i)
        Next
    
        oConfig.WriteLine "MS_RFDIR = MS_DEF"
        oConfig.Close
      End Sub


    I have made the variable type changes and the script now runs without error. However it is not producing the output it should. Using a current folder that oRootFolder represents, the text file should be populated with unique 71 entries, the above macro produces 2 and the first of those is incorrect as it ignores the folder filters. The other worrying this is that it takes 40 seconds for the macro to complete yet the VBScript completes with the correct content in 5 seconds. Needless to say I'm not finding the conversion a pleasant experience.

     






    Friday, June 03, 2011 11:03 AM
  • From a quick look at that, a problem is the iLevel variable
     
        iLevel = 0
        ...
        bSkip1 = (iLevel = 1) And ....
        bSkip2 = (iLevel = 2) And ....
     
    The conditions will never be met. iLevel should probably be defined as a Long (not a String) and set to zero before the outermost call to ProcessFolder.
     
     
     

    Enjoy,
    Tony
    www.WordArticles.com
    Friday, June 03, 2011 11:28 AM
  • Following your advice I've moved iLevel=0 outwith the sub ProcessFolder into Sub RunSync below Set oRootFolder = oFSO.GetFolder(ProjectPath). I've also changed both iLevel and iLength to Long. running the script brings a runtime 5 error - invalid procedure call or arguement within sub WriteOutput on the line colListItem = colPaths(1)
    Friday, June 03, 2011 12:08 PM
  • That implies the Collection is empty - you'll need to determine why. I would step through the earlier code and see what is happening.
     

    Enjoy,
    Tony
    www.WordArticles.com
    Friday, June 03, 2011 12:18 PM
  • Hi Tony,

    I found the problem, Set colPaths as New Collection was put within sub ProcessFolders and needed to be within Sub Runsync. I'm guessing that the collection was being recreated and losing all previous entries, yet I am unsure why the entry that was being written was wrong. No matter, it now works just as the VBScript does and in a slightly faster time though perhaps that's because the VBScript version sorts the arraylist and my collection is unsorted (which is my next feature to add in)

    Thanks a gain for your advice, I would've been stuck without it

    Friday, June 03, 2011 12:41 PM