vba code in Excel

[ExcelNoviceInIL]

I am trying to create a vba code in Excel to go to a list of files and
compare the file name that I entered manually with the list to determine if
there is a file out there by that name. If there is not a file out there, an
error message will be generated telling me that the file does not exist and
to enter a new file name.

[Tom Ogilvy]

sName = "MyFile.xls"
if dir("C:\MyFolder\" & sName) = "" then
msgbox sName & " does not exist, enter another name"
exit sub
end if


or instead, use

sname = Application.GetOpenFileName()

--
Regards,
Tom Ogilvy


"ExcelNoviceInIL" <ExcelNoviceInIL@discussions.microsoft.com> wrote in
message news:3EDA9347-903B-4A93-BB88-18D6D431B6B1@microsoft.com...
> I am trying to create a vba code in Excel to go to a list of files and
> compare the file name that I entered manually with the list to determine
if
> there is a file out there by that name. If there is not a file out there,
an
> error message will be generated telling me that the file does not exist
and
> to enter a new file name.

[ExcelNoviceInIL]

Thank you for the help. It worked perfectly!

"Tom Ogilvy" wrote:

> sName = "MyFile.xls"
> if dir("C:\MyFolder\" & sName) = "" then
> msgbox sName & " does not exist, enter another name"
> exit sub
> end if
>
>
> or instead, use
>
> sname = Application.GetOpenFileName()
>
> --
> Regards,
> Tom Ogilvy
>
>
> "ExcelNoviceInIL" <ExcelNoviceInIL@discussions.microsoft.com> wrote in
> message news:3EDA9347-903B-4A93-BB88-18D6D431B6B1@microsoft.com...
> > I am trying to create a vba code in Excel to go to a list of files and
> > compare the file name that I entered manually with the list to determine
> if
> > there is a file out there by that name. If there is not a file out there,
> an
> > error message will be generated telling me that the file does not exist
> and
> > to enter a new file name.
>
>
>

[Jai]

Just wanted to add a little more to this. The response to the
dir(Pathname + filename) is the file name if it exists in the given path.
That is if myworkbook.xls is in C:\Directory, then
filename = dir(C:\Directory\myworkbook.xls) will return myworkbook.xls
To open files without getting all those warnings for already open files and
files not existing, I use a small utility that checks and returns the file
handle as given below

dim mywb as workbook

set mywb = OpenFile(PathName, FileName, FilePassword)

function OpenFile( Pathname as String, Filename as String, Password as
String) as Workbook
- checks whether the workbook is already open. I call a function IsOpen
that checks for the workbook in the workbooks collection. If it exists, it
returns a workbook handle which Openfile returns. In case the wb is not open,
use the directory command to check if the file exists. On getting the
required string as response, use the workbooks.Open method to open it and
return the workbook handle.

Perhaps I have complicated things. Any suggestions for improvement and
increased efficiency are most welcome.

Jai


"ExcelNoviceInIL" wrote:

> Thank you for the help. It worked perfectly!
>
> "Tom Ogilvy" wrote:
>
> > sName = "MyFile.xls"
> > if dir("C:\MyFolder\" & sName) = "" then
> > msgbox sName & " does not exist, enter another name"
> > exit sub
> > end if
> >
> >
> > or instead, use
> >
> > sname = Application.GetOpenFileName()
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "ExcelNoviceInIL" <ExcelNoviceInIL@discussions.microsoft.com> wrote in
> > message news:3EDA9347-903B-4A93-BB88-18D6D431B6B1@microsoft.com...
> > > I am trying to create a vba code in Excel to go to a list of files and
> > > compare the file name that I entered manually with the list to determine
> > if
> > > there is a file out there by that name. If there is not a file out there,
> > an
> > > error message will be generated telling me that the file does not exist
> > and
> > > to enter a new file name.
> >
> >
> >