CODEWITHSUNDEEP
fileexcelselectionvba
user3008321
user3008321

Reputation: 1

Several file selection using vb for excel if part of file basename is same

I am facing a typical problem wherein I have to select files containing same basename (part of it) and different extentions like pdf, xls, idf, etc. I can do it using filedialog which allows manual selection, but i have to repeat the operation several times and it is extremely cumbersome process. So I am looking forward to do it using vb excel iteratively.

Presently, I tokk help from some colleague who did it using linux programming, which I do not like.

I have to subsequently do some operations on them like renaming, moving, etc. I am able perform these tasks successfully.

Upvotes: 0

Views: 86

Answers (1)

Tony Dallimore
Tony Dallimore

Reputation: 12403

This macro demonstrates different uses of the Dir function:

Option Explicit
Sub DemoDir()

  Dim FileName As String
  Dim PathName As String

  ' This gets the path of the active workbook. I find it convenient
  ' with this type of task, to place the workbook containing the macro
  ' in the folder holding the files I want to examine.  Alternatively,
  ' you can set PathName to any folder you can reach.
  PathName = ActiveWorkbook.Path

  ' This locates each file in PathName with a name starting with "Token"
  ' and an extension of "txt".  It output the name of each file located to
  ' the Immediate Window.  This is an easy way to check you are finding
  ' the correct files.
  FileName = Dir$(PathName & "\Token*.txt")
  Do While FileName <> ""
    Debug.Print FileName
    FileName = Dir$
  Loop

  Debug.Print "-------------------------------"

  ' This locates each file in PathName with a name starting with "T"
  ' and any extension.
  FileName = Dir$(PathName & "\T*.*")
  Do While FileName <> ""
    Debug.Print FileName
    FileName = Dir$
  Loop

  Debug.Print "-------------------------------"

  ' This locates each file in PathName with a name starting with "Te"
  ' and whose fourth character is "t" and whose extension starts with "x".
  FileName = Dir$(PathName & "\Te?t*.x*")
  Do While FileName <> ""
    Debug.Print FileName
    FileName = Dir$
  Loop

End Sub

The above macro merely lists file names to the Immediate Window. If you want to rename or move files you need to use the Name statement. I find the name Name confusing and unhelpful but it does what you want. Its syntax is:

Name oldpathname As newpathname

Look up the Name statement in the VB Editor's Help for more information.

Upvotes: 1

Related Questions