Open & copy specific files

[howard]

I have several files in Directory C:\Sales for eg increases BR.Aug 2017.xls, increases CABR.Aug 2017xls


I have tried to write code to open a specific name after the text increases contained in the name of the workbook for eg "BR" that is after increases


when running the code below nothing happens


Kindly amend my code so that when "BR" appears after increases that file is selected and then copied


I think that it is only this section of code that needs to be amended


Your assistance is most appreciated

 If objFSO.GetExtensionName(objMyFile) = "csv" And StrConv(Left(objFSO.GetBaseName(objMyFile), 4), vbUpperCase) = "in BR" Then

 Sub Open_Spec_Files()
    
    Dim objFSO       As Object
    Dim objMyFolder  As Object
    Dim objMyFile    As Object
    Dim wbMyWorkBook As Workbook
    Dim lngLastRow   As Long
    
    Application.ScreenUpdating = False
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objMyFolder = objFSO.GetFolder("C:\Sales") 
   
    For Each objMyFile In objMyFolder.Files
      
        If objFSO.GetExtensionName(objMyFile) = "csv" And StrConv(Left(objFSO.GetBaseName(objMyFile), 4), vbUpperCase) = "in BR" Then
            '...set the wbMyWorkBook variable by opening the workbook and copy the data from range A4:O[lngLastRow].
            Set wbMyWorkBook = Workbooks.Open(objMyFolder & "\" & objMyFile.Name)
            'Finds the last row across columns A to O (inclusive) of the first sheet (note a csv file can only have one sheet) in the *.csv file
            On Error Resume Next
                lngLastRow = wbMyWorkBook.Sheets(1).Range("A:O").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            On Error GoTo 0
            If lngLastRow >= 4 Then
                With wbMyWorkBook
                    .Sheets(1).Range("A4:O" & lngLastRow).Copy Destination:=ThisWorkbook.ActiveSheet.Range("A2")
                    .Close SaveChanges:=False 'Close the *.csv without saving and changes
                End With
            End If
        End If
    Next objMyFile
   Set objFSO = Nothing
    Set objMyFolder = Nothing
    
    Application.ScreenUpdating = True

End Sub

 

[Norie]

This will never be true for 2 reasons, firstly you are extracting 4 characters from the filename and 'in BR' is 5 characters and secondly you are converting those 4 characters to upper case and 'in BR' is mixed case, i.e. both upper and lower.

StrConv(Left(objFSO.GetBaseName(objMyFile), 4), vbUpperCase) = "in BR"

Also, with a filename like 'increases BR.Aug 2017.xls' the first 4 characters are 'incr'.

 

[howard]

Thanks for the reply Norie

I will change 4 to 5. How do I amend "vbUpperCase" for mixed characters ?

 

[Norie]

You still won't get a match as the file name doesn't start with 'In BR'.

 

[howard]

I was just using that as an example if file name was "In BR.Aug 2017.xls"

Please advise how to amend "vbUpperCase" to accept any case

 

[Norie]

I wouldn't use StrConv, I'd use UCase on both the filename and the term you are trying to match.

I'd also be more likely to use Like with a wildcard to find a file based on what the name starts with, in fact you could do the whole thing, including checking the extension, in one go using Like.

If UCase(objMyFile.Name) Like  UCase("In BR*.CSV") Then

 

[howard]

Thanks for the advise Norie