dynamic file opening

[arian29]

I need a code in vba which would read a file name in a specified folder (dynamic path). the file name changes every time and there can be more than one files and the code should pick them all. The file is recognised by a word say "QWE" which exists in the file name everytime. e.g. "E2 QWE T556M.XLSX" etc...

 

[Richard Schollar]

Hi

Dir is your friend:

Dim arrFiles As Variant
Dim strFile As STring, pth As String
Dim i As Long

pth = "C:\Somefolder\"

strFile = Dir(pth & "*QWE*")

If strFile<>"" Then
   i = 1
   Do
      ReDim arrFiles(1 To i)
      arrFiles(i) = pth & "strFile"
      strFile = Dir
      i = i + 1
   Loop While strFile<>""
Else
  MsgBox "No file found":Exit Sub
End If

'now use array of files:

For i = 1 To Ubound(arrFiles)
  Workbooks.Open arrFiles(i)
Next i

 

[arian29]

hi, when the code come to Workbooks.Open arrFiles(i), i get an error > 'D:\strFile.xlsx' could not be found. please check spelling.........

 

[Richard Schollar]

Sorry - code in Do Loop was wrong - should have been this:

Do
      ReDim arrFiles(1 To i)
      arrFiles(i) = pth & strFile   '=== Note no quotes around strFile!
      strFile = Dir
      i = i + 1
   Loop While strFile<>""

 

[arian29]

now i am gettin the error.. " could not be found.
i have placed two files this time. if there is one file , the code is working fine but not for more than one files.

 

[Richard Schollar]

Apologies - this should correct:

Dim arrFiles As Variant
Dim strFile As String, pth As String
Dim i As Long

pth = "D:\"

strFile = Dir(pth & "*QWE*")

If strFile <> "" Then
    ReDim arrFiles(1 To 1)
   i = 1
   Do
    arrFiles(i) = pth & strFile
    strFile = Dir
    If strFile <> "" Then
      i = i + 1
      ReDim Preserve arrFiles(1 To i)
    Else
      Exit Do
    
    End If
   Loop
Else
  MsgBox "No file found": Exit Sub
End If

'now use array of files:

For i = 1 To UBound(arrFiles)
  Workbooks.Open arrFiles(i)
Next i

 

[arian29]

Its working perfectly now.. Thanks a lot