• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to open an excel file from the folder based on cell value

ThrottleWorks

Excel Ninja
Hi,

I have one excel file. In column A, I have values such as ABC1200, DEF1300 etc.
I have a folder, this folder has multiple excel files. File names will be XYZ-ABC1200, UJM-DEF1300.

For example, cell A1 value is ABC1200, the macro should copy this value, go to folder.
Search this value, and open the file XYZ-ABC1200.

At present I am doing it manually, copy the cell value, go to the folder.
Paste in search bar, check the search results and open the correct file.

I tried searching on Google, but I am getting results for matching exact cell value with file name.
My problem is, I can not match exact, I have to check contains.

Can anyone help me in this please.
 

ThrottleWorks

Excel Ninja
Hi Xiq,

Thanks for the help. I am thinking something different.

I will derive the file names using the below mentioned code.
Wil create a matching list based on the derived names and original list.

Then try to pull the names, have just started, will post further results.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1042
Code:
Option Explicit
Sub GetFileNames()
  
    Dim xRow As Long
    Dim xDirect$, xFname$, InitialFoldr$
  
    InitialFoldr$ = "G:\" '<<< Startup folder to begin searching from
  
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
                ActiveCell.Offset(xRow) = xFname$
                xRow = xRow + 1
                xFname$ = Dir
            Loop
        End If
    End With
End Sub
 

ThrottleWorks

Excel Ninja
Hi,

I want to use cell value as filename and open the file.
Filename is exact match to cell value, I used various codes from Google but it is not working.

Can anyone help me in this please.
 

Attachments

Debraj

Excel Ninja
Hi Sachin,

I think, the problem is.. due to subfolder.. may be your OS is searching that file in subfolder too. .and you missed to search on that location..
Try This..

Code:
Dim i As Integer

Sub SeachLikeWin7()
  i = 0
  Dim fldStart As Object, mask As String, fld As Object
    Sheets(2).Hyperlinks.Delete
    Range("C7:C" & [C7].End(xlDown).Row).Clear

    Set fso = CreateObject("scripting.FileSystemObject")

    Set fldStart = fso.GetFolder([c4] & "\")
    mask = [c2]
    ListFiles fldStart, mask
    For Each fld In fldStart.SubFolders
        ListFiles fld, mask
        ListFolders fld, mask
    Next
End Sub

Sub ListFolders(fldStart As Object, mask As String)
    Dim fld As Object
    For Each fld In fldStart.SubFolders
        ListFiles fld, mask
        ListFolders fld, mask
    Next

End Sub

Sub ListFiles(fld As Object, mask As String)
    Dim fl As Object 'File
    For Each fl In fld.Files
        If InStr(fl.Name, mask) > 0 Then
            'Range("B" & 7 + I) = fld.Path
            Range("c" & 7 + i) = fl.Name
          
            ' Here you can directly open the required file..
            Sheets(2).Hyperlinks.Add Range("c" & 7 + i), fld.Path & "\" & fl.Name
            i = i + 1
        End If
    Next
End Sub
 

Attachments

Last edited:

ThrottleWorks

Excel Ninja
Hi Debraj, thanks a lot for the help. Your code is working nice.
However the code is creating a hyperlink and I have to click the link to open the file. (Please correct me if I am wrong)

I want to open the file and process it further, sorry should have mention it earlier. My mistake. Should not have happen.

The folder might contain aprox 350 files, I have the file names in one column.
I will open the file as per the cell value and process it.

I am supposed to work in below mentioned flow.

Open master file, in the A column I have file names.
Cell A1, Cell A2, Cell A3 and so on.
Open 1 file at a time, process it, close it, next file.

I tried this code.

Sub Test()
Dim Path As String
Dim File As String
File = Range("k5").Value

Path = "original path is mentioned here"
Workbooks.Open Filename:=Path & File
End Sub
This is also giving me bug, do not know where the mistake is.
Could you please help if you get time, thanks a lot for the code provided in the earlier post.
 

Debraj

Excel Ninja
Hi Sachin,

Sorry for late response..
Please try the below code..
Code:
Sub SeachLikeWin7()
  i = 0
  Dim fldStart As Object, mask As String, fld As Object
  Sheets(2).Hyperlinks.Delete
  Range("C7:C" & [C7].End(xlDown).Row).Clear

  Set fso = CreateObject("scripting.FileSystemObject")

  Set fldStart = fso.GetFolder([c4] & "\")
  mask = [c2]
  ListFiles fldStart, mask
  For Each fld In fldStart.SubFolders
  ListFiles fld, mask
  ListFolders fld, mask
  Next
End Sub

Sub ListFolders(fldStart As Object, mask As String)
  Dim fld As Object
  For Each fld In fldStart.SubFolders
  ListFiles fld, mask
  ListFolders fld, mask
  Next

End Sub

Sub ListFiles(fld As Object, mask As String)
  Dim fl As Object 'File
  For Each fl In fld.Files
  If InStr(fl.Name, mask) > 0 Then
  'Range("B" & 7 + I) = fld.Path
  ' Here you can directly open the required file..
  'Sheets(2).Hyperlinks.Add Range("c" & 7 + i), fld.Path & "\" & fl.Name
  Workbooks.Open fld.Path & "\" & fl.Name
  Set actwb = ActiveWorkbook
  'Process...
  '---
   
  '---
  actwb.Close
  End If
  Next
End Sub
 

ThrottleWorks

Excel Ninja
Hi Debraj, good afternoon, Sir you should not be sorry, you are helping me.
I read your code yesterday only but could not reply by using mobile.

Sorry for late reply, I will share the results tomorrow, PC at home is not working properly.
Have a nice Sunday. :) thanks a lot for the help.
 

ThrottleWorks

Excel Ninja
Hi Debraj, your code is amazing, :awesome: thanks lot for the help.
I think you even worked on weekend to help me, that is really great.

Have a nice day ahead.
 
Last edited:

ThrottleWorks

Excel Ninja
Hi Debraj,

I just tried the code pasted below.

Code:
Sub Test()
    Application.ScreenUpdating = False
        Dim File As String
        File = Range("k5").Value
        Dim Path As String
        Path = "\\mypath" (this is dummy path)
        ChDir Path
        Workbooks.Open Filename:=File
    Application.ScreenUpdating = True
End Sub
This is also working, no doubt your code is excellent, just wanted to share what I tried.
Thanks a lot for your effort and time. :)
 

victorT43

New Member
Hi Debraj,

sorry to asking you in this threads.. i had try your code but why always delete/replace previously list of documents..
just works with some list , about 8 -12 rows below
need help to display all of documents.

thank you
 
Top