macro to open specific Files in a folder

[howard]

I have tried to write code to open all files in C:\Fixed assets that contain fixed assets for eg BR1 Fixed assets.xlsm, Souther Fixed Assets.xlsm etc

 Sub Open_files()
ChDir "C:\Fixed Assets"
Dim A As Variant
Dim LR As Long


If TypeName(A) = "Boolean" Then Exit Sub


Dim FileAry As Variant, Fle As Variant
   With Application.FileDialog(3)
      .InitialFileName = "C:\Fixed Assets\"
      .AllowMultiSelect = True
      .InitialFileName = "*Fixed*Assets*.xls*"
      If .Show Then Set FileAry = .SelectedItems()
   End With
   For Each File In FileAry
   If TypeName(A) = "Boolean" Then Exit Sub

   
      With Workbooks.Open(File)
                   
   End With

I would like this amended so that it excludes workbooks containing "Consolidated" and "Email"



Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Try this:

Sub Open_FixedAssets()
  Dim sFile As String
  Dim sPath As String
  
  sPath = "C:\Fixed Assets\"
  sFile = Dir(sPath & "*Fixed Assets*.xls*")
  
  Do While sFile <> ""
    Workbooks.Open sPath & sFile
    sFile = Dir()
  Loop
End Sub

 

[howard]

Thanks Dante

Kindly amend code to exclude workbooks containing "Consolidated" and "Email" per my post # 1

 

[DanteAmor]

Try this:

Sub Open_files()
  Dim FileAry As Variant, File As Variant
  Dim A As Variant
  
  ChDir "C:\Fixed Assets"
  If TypeName(A) = "Boolean" Then Exit Sub
  
  With Application.FileDialog(3)
    .InitialFileName = "C:\Fixed Assets\"
    .AllowMultiSelect = True
    .InitialFileName = "*Fixed*Assets*.xls*"
    If .Show Then Set FileAry = .SelectedItems()
  End With
  
  Application.ScreenUpdating = False
  For Each File In FileAry
    If TypeName(A) = "Boolean" Then Exit Sub
    If InStr(1, File, "Consolidated", vbTextCompare) = 0 And _
       InStr(1, File, "Email", vbTextCompare) = 0 Then
      With Workbooks.Open(File)
    
      End With
    End If
  Next
  Application.ScreenUpdating = True
End Sub

 

[howard]

thanks Dante-this works perfectly

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.