VBA: Setting an object within a for loop, object stuck on first value

Question

I have some code that takes in a file path from a cell and reads the list of folders at the directory. I want it to do this in a loop for two cells (in my case B8 and B9). At the moment the code is looking at the first file path twice, rather than both paths once. The section of the code I believe is causing me the problem is here:

Dim objFSO As Object
Dim objFolder As Object


For k = 8 to 9
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    MsgBox k
    Set objFolder = objFSO.GetFolder(Range("B" & k).Value)
    MsgBox objFolder
    'do the bit of code that reads the files    

Next

The first message box returns 8 & later 9 as expected, but MsgBox objFolder stays stuck on B8.value. I feel like I need to be clearing objFolder by setting it to null or similar but have tried a few variations of this with no success.

Update to provide more code in case I'm unwittingly doing something I shouldn't be:

The whole thing reads in the file paths, finds a particular text file at the path, unzips it and then imports the text files into two tabs.

Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim Directory(15) As String
Dim ZIPFile As Variant


Set objFSO = CreateObject("Scripting.FileSystemObject") 
'moved to outside now

For k = 8 To 9


Set objFolder = objFSO.GetFolder(Range("B" & k).Value)
i = 0

For Each objFile In objFolder.Files
Directory(i) = objFile.Path
i = i + 1
Next objFile


For i = 0 To 14
If Right(Directory(i), 6) = "FQ.zip" Then ZIPFile = Directory(i)

Next

    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim strDate As String

        DefPath = "Path name..."

        'Create the folder name
        strDate = Format(Now, " dd-mm-yy h-mm-ss")
        FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"


        MkDir FileNameFolder

        Set oApp = CreateObject("Shell.Application")

        oApp.Namespace(Fname).items
Sheets(1).Range("F" & k).Value = Replace(Right(ZIPFile, 25), ".zip", "") & "\EL-contract-rg.txt"

        oApp.Namespace(FileNameFolder).CopyHere _
         oApp.Namespace(ZIPFile).items.Item(Replace(Right(ZIPFile, 26), ".zip", "") & "\EL-contract-rg.txt")

        MsgBox "You find the files here: " & FileNameFolder

        On Error Resume Next
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True



Sheets(k - 6).Select
With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & FileNameFolder & "EL-contract-rg.txt", Destination:=Range("$A$1") _
        )
        .Name = "Sample"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With



 Next



End Sub

I don't think this will solve your issue, but Set objFSO should not be done inside that loop. — David Zemens
– David Zemens, Commented Dec 1, 2015 at 20:05
Unable to replicate, even using your code as-is. Are you sure that B8 and B9 actually contain different values? — David Zemens
– David Zemens, Commented Dec 1, 2015 at 20:07
objFolder can and should be set within the loop. objFSO should not be. — David Zemens
– David Zemens, Commented Dec 1, 2015 at 20:08
I can reproduce this if I use On Error Resume Next and the folder in B9 doesn't exist. — Kyle
– Kyle, Commented Dec 1, 2015 at 20:13
You use On Error Resume Next and never reset error handling. Underneath For k = 8 To 9 Put On Error GoTo 0 and let us know what happens. — Kyle
– Kyle, Commented Dec 1, 2015 at 20:31

David Zemens · Accepted Answer · 2015-12-01 20:19:25Z

This works as-expected, incidentally, your code also works as expected and does not exhibit the problem you describe in OP.

@Kyle has identified a possible cause of this problem, which would be improper error-handling with On Error Resume Next which will exhibit this failure of the specified folder path doesn't exist.

On Error Resume Next is the Devil's work, unless you know how to use it locally and trap errors. It is generally preferable to anticipate errors and code for those exceptions, like below, where we use the .FolderExists method of the FSO class to handle what would otherwise be a runtime error:

Sub foo()
Dim objFSO As Object
Dim objFolder As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")

For k = 8 To 9
    If objFSO.FolderExists(Range("B" & k).Value) Then
        Set objFolder = objFSO.GetFolder(Range("B" & k).Value)
        Debug.Print k & vbTab & objFolder
    End If
Next

End Sub

Collectives™ on Stack Overflow

VBA: Setting an object within a for loop, object stuck on first value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related