Code that errors after workbook is reopened

[SkipVought]

This one has had me stumped all morning.

I can copy the code or export/import the module with the same results:

1. copy the code into a new workbook

2. save the workbook in a folder with several workbooks that all begin with DSC_CBU_Actions_

3. run the code sucessfully. Save and run again.

4. save, close & open the workbook

5. run the code UNSUCESSFULLY

On the statement

Set wb = Workbooks.Open(.Name, , True)

I get a run time error 1004: filename could not be found

nothing has changed, except for the workbook being reopened.

Sub CopyData()
    Dim oFS, oFolder, oFile, wb, rng As Range, ws As Worksheet
    
    Set oFS = CreateObject("Scripting.FileSystemObject")
    
    Set oFolder = oFS.GetFolder(ThisWorkbook.Path)

    For Each oFile In oFolder.Files
        With oFile
            If .Name Like "DSC_CBU_Actions_*" Then
                Set wb = Workbooks.Open(.Name, , True)
                With wb
                    For Each ws In wb.Worksheets
                        With ws
                            Set rng = .Columns(1).Find("1")
                            .Range(rng, .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Copy _
                                Sheet1.Cells(Sheet1.[A1].CurrentRegion.Rows.Count + 1, 1)
                        End With
                    Next
                    wb.Close
                End With
            End If
        End With
    Next
    
    Set wb = Nothing
    
    Set oFolder = Nothing
    
    Set oFS = Nothing
    
End Sub

Any thoughts?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

I ended up using the FileSearch object, thanks to John Higgins recent thread707-1212915, but still don't understand why the FileSystemObject does not work.



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[PHV]

and what about this ?
Set wb = Workbooks.Open(.[!]Path[/!], , True)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[combo]

Do you try to open a link this way? Its real extension is "lnk", whatever is displayed, and can't be open in excel.

combo

 

[SkipVought]

I've come to the conclusion that I was trying to use the wrong approch by using a FSO.

The FoundFiles object does the trick!

Thanx for each of your contributions.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}