Look Up Specific files (Excel 2003)

Topic

New Reply

Hi All

I want to look up Folders and its files and open these files based on the value in column C of the active sheet

The data in column C consist of names with abbreviation in front of the names, such as “ABC John”
The folders are named with abbreviation such as “ABC”
The files are named under abbrreviation such as “ABC”

These are folders and files resides in F:MyProcess

TIA

regards, francis

Reply | Quote

Replies

Are all the folders direct subfolders of F:MyProcess, for example F:MyProcessABC, or can they also be sub-subfolders, for example F:MyProcessTestABC?

Reply | Quote

Hi Hans

These specific folders are direct subfolders in F:MyProcessABC and the files are in F:MyProcessABCABC Ltd

thanks for looking into this.

regards, francis

Reply | Quote

Perhaps you can use the following macro as starting point:

Sub OpenFiles()
Dim r As Long
Dim m As Long
Dim strVal As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim wbk As Workbook

‘ Last filled row in column C
m = Range(“C” & Rows.Count).End(xlUp).Row
‘ Loop through the rows
For r = 1 To m
‘ Get value
strVal = Range(“C” & r)
‘ Extract part before the first space
strName = Left(strVal, InStr(strVal, ” “) – 1)
‘ Folder name
strPath = “F:MyProcess” & strName & “”
‘ First file
strFile = Dir(strPath & strName & “*.xls”)
‘ Loop through the files
Do While Not strFile = “”
‘ Open workbook
Set wbk = Workbooks.Open(strPath & strFile)
‘ Do something with the workbook here
‘ …
‘ Close the workbook
wbk.Close
‘ Next filename
strFile = Dir
Loop
Next r
End Sub

Reply | Quote

Hi Hans,

Thank you for providing the codes on this.

I’ll try on it and let you know the outcome.

cheers, francis

Reply | Quote