Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Look Up Specific files (Excel 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look Up Specific files (Excel 2003)

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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look Up Specific files (Excel 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look Up Specific files (Excel 2003)

    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

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Look Up Specific files (Excel 2003)

    Hi Hans,

    Thank you for providing the codes on this.

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

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •