Opening Specific file in Folder

[howard]

I have several files in folder C:\sales increases for eg Increases BR Aug 2017.xls, Increases CABM Aug 2017.xls etc


I would like to amend my code, which does nothing when activated so as to open a specific workbook containing BR appearing after the text increases

I think that this section needs to be corrected

   If objFSO.GetExtensionName(objMyFile) = "csv" And StrConv(Left(objFSO.GetBaseName(objMyFile), 12), vbProperCase) = "Increases BR" Then

your assistance in this regard is most appreciated

 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 Increases") 
   
    For Each objMyFile In objMyFolder.Files
       
        If objFSO.GetExtensionName(objMyFile) = "csv" And StrConv(Left(objFSO.GetBaseName(objMyFile), 12), vbProperCase) = "Increases 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

 

[Akuini]

Try changing this:

If objFSO.GetExtensionName(objMyFile) = "csv" And StrConv(Left(objFSO.GetBaseName(objMyFile), 12), vbProperCase) = "Increases BR" Then

to this:

If objFSO.GetExtensionName(objMyFile) = "csv" And UCase(Left(objFSO.GetBaseName(objMyFile), 12)) = UCase("Increases BR") Then

 

[howard]

Thanks for the help, much appreciated

 

[Akuini]

You're welcome & thanks for replying.

---

And you can also click ‘Thank for this post’ and ‘Like this post’ icon in the post that you like.