CODEWITHSUNDEEP
excelvba
alowflyingpig
alowflyingpig

Reputation: 738

Loop not looping through files in DIR (Outer Loop)

I have written a code that works, a DIR loop that loops through CSV files, does code, then calls next until the end. The problem started when I added another loop within this loop, DIR loop to call PDF's...

I have stepped through the code and the problem is the first outer loop not calling the next CSV (the PDF loop works fine). I've done some research but can't make any adjustments work.

Any assistance is appreciated. I don't mind not being given the answer if you could show me where to look to resolve.

Sub Coles_claims_consolidation()
'Coles Claims Import Macro

    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object



    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(ThisWorkbook.Path & "\csv_macro\")
    For Each oFile In oFolder.Files
        Debug.Print oFile.Name
    Next 'oFile

        'Loop through each file in the folder
    For Each oFile In oFolder.Files
        If oFile.Name Like "*.pdf" Then
            FileCopy oFile, ThisWorkbook.Path & "\" & FiName2 & ".pdf"
        End If
    Next oFile

    Set oFile = Nothing
    Set oFolder = Nothing
    Set oFSO = Nothing



End Sub

Upvotes: 0

Views: 142

Answers (2)

VBasic2008
VBasic2008

Reputation: 55073

A Working Solution?

Change the line

oFSO.GetBaseName(oFile) = FiName2

to

FiName2 = oFSO.GetBaseName(oFile)

EDIT:

Is this your final solution then?

Sub Coles_claims_consolidation()
'Coles Claims Import Macro

    Dim sPath As String
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim FiName2 As String

    sPath = ThisWorkbook.Path & "\csv_macro\"

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)

    For Each oFile In oFolder.Files
        FiName2 = oFSO.GetBaseName(oFile)
        If oFile.Name Like "*.pdf" Then
            FileCopy oFile, ThisWorkbook.Path & "\" & FiName2 & ".pdf"
        End If
    Next oFile

    Set oFile = Nothing
    Set oFolder = Nothing
    Set oFSO = Nothing

End Sub

Upvotes: 0

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

oFile already has the file path included with it. You should set the target file path in oFolder and loop within that directory.

If you only want .pdf files then keep the If oFile.Name line - otherwise if you want all files then delete the if statement.

Set oFolder = oFSO.GetFolder(ThisWorkbook.Path & "\csv_macro\")

For Each oFile In oFolder.Files
    If oFile.Name Like "*.pdf" Then
        FileCopy oFile, ThisWorkbook.Path & "\" & FiName2 & ".pdf"
    End If
Next oFile

Upvotes: 3

Related Questions