Search Files

[rajivsoni]

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???

 

[Legacy 98055]

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="filterrogid: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]

Hi,

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

 

[rajivsoni]

hey krish... it gives me an error
#Name

 

[Krishnakumar]

Works fine for me.

The code goes in a Standard Module.