Search Files

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
I wanted to know...
a) Can we type a formula to search a file in one particular folder, if the filename is typed in a particular cell?

b) If above point can be done can we also search a worksheet in that file?

Lets take an example....

Cell A1 = Filename (i.e. "Inventory")
now i want a formula in B1..
If A1(File) exists in the mentioned folder then true else false)

can someone help???
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
L

Legacy 98055

Guest
Example usage:

=FileExists("C:\Documents and Settings\Tom\Desktop\SomeFile.txt")

if your file's path is located in cell A1, use this:

=FileExists(A1)

In a standard module:
<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Dim</font> fso <font color="#0000A0">As</font> <font color="#0000A0">Object</font>

  <font color="#0000A0">Public</font> <font color="#0000A0">Function</font> FileExists(FileFullName <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font>
       <font color="#0000A0">If</font> fso <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font> <font color="#0000A0">Set</font> fso = CreateObject("Scripting.FileSystemObject")
       FileExists = fso.FileExists(FileFullName)
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("930200674452531").value=document.all("930200674452531").value.replace(/<br \/>\s\s/g,"");document.all("930200674452531").value=document.all("930200674452531").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("930200674452531").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="930200674452531" wrap="virtual">
Option Explicit

Dim fso As Object

Public Function FileExists(FileFullName As String) As Boolean
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
FileExists = fso.FileExists(FileFullName)
End Function</textarea>
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Code:
Function FileExists(FP As String, FN As String) As Boolean
    strFileName = FP & "\" & FN & ".xls"
    On Error Resume Next
    FileExists = FileLen(strFileName) >= 0
End Function

use

=fileexists(a1,a2)

where a1 houses folder name and a2 houses file name.

See: Here


HTH
 

Forum statistics

Threads
1,136,700
Messages
5,677,277
Members
419,683
Latest member
MrVBAConfused

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top