1

Is there a way in excel VBA to find a file from a folder and open it?

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
Erick Guevarra
  • 31
  • 1
  • 1
  • 2

3 Answers3

2

Here's an example of how you could use CMD to do this in Excel-VBA:

Sub FindFile()

Dim fileName      As String
Dim parentFolder  As String
Dim found         As String

parentFolder = "C:\Users\Macro Man\Documents\" '// Note the trailing "\" this is required!

fileName = "findMe.html" '// Change as required

With CreateObject("WScript.Shell")
    found = CStr(Split(.Exec("CMD /C DIR """ & parentFolder & "*" & fileName & """ /S /B /A:-D").StdOut.ReadAll, vbCrLf)(0))

    If Not Trim(found) = "" Then
        .Run "CMD /C START """ & Trim(found) & """", 0, True
    Else
        MsgBox "File not found!", vbInformation
    End If
End With

End Sub

It uses a DIR command to find the file (the /S parameter specifies that it should look through all sub-directories) and then uses the START command to open the file in it's native application.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
1

here you can find another example on how to search in folders:

Is it possible to list all the files and folders in a custom directory - excel vba

the example lists all files in a given folder and all sub folders.

and here an example on how you can open a text file and search for given values for example:

MS VB For Application - Read href value in a txt file

If you specify more precise what you need we can probably give more specific answers.

Community
  • 1
  • 1
cboden
  • 813
  • 9
  • 14
-1

if by "find" you mean open a file with a specific name, then you can simple do it like this:

Filename = "sampleFile.xlsx"
Application.Workbooks.Open ("d:\sample\path\" + Filename)
szaman
  • 2,159
  • 1
  • 14
  • 30
  • it will open non-excel files in excel. for opening with a default appilcation you could try [this post](http://stackoverflow.com/questions/18921168/how-can-excel-vba-open-file-using-default-application). – szaman Dec 04 '15 at 10:35
  • This assumes that you already know the location of the file, and will only open Excel files. Also you should use `&` for string concatenation. [See this](https://msdn.microsoft.com/en-us/library/te2585xw.aspx) for the reason why – SierraOscar Dec 04 '15 at 10:54
  • I was able to open it but the content of the html is not displayed. its just all white – Erick Guevarra Dec 04 '15 at 10:55
  • @MacroMan OP didn't specify what they mean by "find" and the questions is pretty vague, so i would say my answer is valid. – szaman Dec 04 '15 at 11:21
  • 1
    @foerno I would argue that the word "find" is pretty self-explanatory... Your code will only open an excel-associated file in a _known_ location - the question title obviously states that the OP is trying to find the file in a directory or sub-directory and then open it. – SierraOscar Dec 04 '15 at 11:23
  • @MacroMan the question title has been edited so it was more ambiguous when i posted this reply. ;) – szaman Dec 04 '15 at 16:53