Hi

Can the variables be declared in a more efficient manner in this
case. The below code gets the job done but I was wondering if there
was a better way than to Declare the names twice. Maybe some sort of
loop through the name ranges. Just want to know how to clean this
puppy up?

Thanks in advance.

Chad

Sub BookExists()
'Test to see if a Workbooks exist
Dim FindFile As String
Dim BookName(2) As String, BookName2 As String
Dim LPosition(2) As Integer
Dim Myrng(2) As String
Dim Mylen(2) As Long
Dim Lcount(2) As Long

Myrng(1) = Sheets("Data").Range("A110").Value
Myrng(2) = Sheets("Data").Range("A111").Value
LPosition(1) = InStrRev(Myrng(1), "\")
LPosition(2) = InStrRev(Myrng(2), "\")
Mylen(1) = Len(Myrng(1))
Mylen(2) = Len(Myrng(2))
Lcount(1) = Mylen(1) - LPosition(1)
Lcount(2) = Mylen(2) - LPosition(2)
BookName(1) = Right(Myrng(1), Lcount(1))
BookName(2) = Right(Myrng(2), Lcount(2))

FindFile = Range("BkPath").Value & "\Final Reports"
With Application.FileSearch
.LookIn = FindFile
'* represents wildcard characters
.Filename = BookName(1)
.Filename = BookName(2)
If .Execute > 0 Then 'Workbook exists
'Am doing stuff here
Else 'There is NOt a Workbook
MsgBox BookName(1) & " Does not exist - Please Create
File"
MsgBox BookName(2) & " Does not exist - Please Create
File"
End If
End With

End Sub

It's not declaring them, that's the Dim statements. It's assigning values
that you're talking about.

And you can do something like this:

For iCounter = 1 To 2
LPosition(iCounter) = InStrRev(Myrng(iCounter), "\")
Mylen(iCounter) = Len(Myrng(iCounter))
Lcount(iCounter) = Mylen(iCounter) - LPosition(iCounter)
Next

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Chad" <(E-Mail Removed)> wrote in message
news:363d4f5b-4e18-48ed-b6b3-(E-Mail Removed)...
> Hi
>
> Can the variables be declared in a more efficient manner in this
> case. The below code gets the job done but I was wondering if there
> was a better way than to Declare the names twice. Maybe some sort of
> loop through the name ranges. Just want to know how to clean this
> puppy up?
>
> Thanks in advance.
>
> Chad
>
> Sub BookExists()
> 'Test to see if a Workbooks exist
> Dim FindFile As String
> Dim BookName(2) As String, BookName2 As String
> Dim LPosition(2) As Integer
> Dim Myrng(2) As String
> Dim Mylen(2) As Long
> Dim Lcount(2) As Long
>
> Myrng(1) = Sheets("Data").Range("A110").Value
> Myrng(2) = Sheets("Data").Range("A111").Value
> LPosition(1) = InStrRev(Myrng(1), "\")
> LPosition(2) = InStrRev(Myrng(2), "\")
> Mylen(1) = Len(Myrng(1))
> Mylen(2) = Len(Myrng(2))
> Lcount(1) = Mylen(1) - LPosition(1)
> Lcount(2) = Mylen(2) - LPosition(2)
> BookName(1) = Right(Myrng(1), Lcount(1))
> BookName(2) = Right(Myrng(2), Lcount(2))
>
> FindFile = Range("BkPath").Value & "\Final Reports"
> With Application.FileSearch
> .LookIn = FindFile
> '* represents wildcard characters
> .Filename = BookName(1)
> .Filename = BookName(2)
> If .Execute > 0 Then 'Workbook exists
> 'Am doing stuff here
> Else 'There is NOt a Workbook
> MsgBox BookName(1) & " Does not exist - Please Create
> File"
> MsgBox BookName(2) & " Does not exist - Please Create
> File"
> End If
> End With
>
> End Sub

Hi Jon

Thanks for your reply. It worked a treat. I am very appreciative of
your help, thankyou. I have looked at your website a great deal over
the years and it has been more than helpful. It is a fantastic site
and you should be very proud.

Take care

Chad