Opening Specific file in Folder

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,570
Office Version
  1. 2021
Platform
  1. Windows
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

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


your assistance in this regard is most appreciated


Code:
 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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try changing this:

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

to this:

Code:
If objFSO.GetExtensionName(objMyFile) = "csv" And UCase(Left(objFSO.GetBaseName(objMyFile), 12)) = UCase("Increases BR") Then
 
Upvote 0
Thanks for the help, much appreciated
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,030
Messages
6,128,413
Members
449,449
Latest member
Quiet_Nectarine_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top