msgbox statement- easy one

[Rick M]

I have code the opens an existing filename in another directory. What I need is the code if the file is not found to pop up a msgbox stating "file not found" and exit the sub.
my statement looks like:
workbooks.open filename:=_
"c:documents and settingsdesktopforecastdallas.xls"

Thanks

[alexxl]

Search for "FileExists" in VBA help. Write an IF-THEN structure depending on what is returned. Use your existing codes if it is TRUE and use - MsgBox "File not found!" if it is FALSE.

[bergy]

On 2002-05-13 13:20, alexxl wrote:
Search for "FileExists" in VBA help. Write an IF-THEN structure depending on what is returned. Use your existing codes if it is TRUE and use - MsgBox "File not found!" if it is FALSE.

alexxl is right. Here is something I have used before (with assistance from the Help File):

Sub test2()
Set fs = Application.FileSearch
With fs
.LookIn = "c:documents and settingsdesktopforecast"
.FileName = "dallas.xls"
If .Execute > 0 Then
Workbooks.Open FileName:= _
"c:\documents and settings\desktop\forecast\dallas.xls"
Else
MsgBox "File not found."
End If
End With
End Sub

[Rick M]

Thanks, but I still cannot get it to work. It always says" file not found", when I know it is in the correct path

[Ivan F Moala]

On 2002-05-13 13:44, Rick M wrote:
Thanks, but I still cannot get it to work. It always says" file not found", when I know it is in the correct path

Hi Rick
What code did you use and have you typed
the names correctly ?
You could put a break in your code or use the
debug window to check what variables you are
getting.....

[Rick M]

Ivan,
Here is the code:

Sub importcharlotte()
'
' importcharlotte Macro
' Macro recorded 5/13/2002 by Rick Moorman
'

'
Set fs = Application.FileSearch
With fs
.LookIn = "C:Documents and SettingsmoormanDesktopFORECAST"
.Filename = "charlotte.xls"
If .Execute > 0 Then
MsgBox "File not Found"
Else


Workbooks.Open "C:Documents and SettingsmoormanDesktopcharlotte.xls"
Range("A1:H37").Select
Selection.Copy
Windows("Sales Forecast -Actual.XLS").Activate
Sheets("Forecast").Select
ActiveWindow.SmallScroll Down:=177
Range("A215").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-15
Sheets("home").Select
Windows("charlotte.xls").Activate
ActiveWindow.Close


End If
End With




End Sub

[Ivan F Moala]

Hi Ricky
Try;

Sub importcharlotte()

'

' importcharlotte Macro

' Macro recorded 5/13/2002 by Rick Moorman

'



'

Set fs = Application.FileSearch

With fs

    .LookIn = "C:Documents and SettingsmoormanDesktopFORECAST"

    .Filename = "charlotte.xls"

    If .Execute = 0 Then

        MsgBox "File not Found"

    Else

        Workbooks.Open "C:Documents and SettingsmoormanDesktopcharlotte.xls"

        Range("A1:H37").Select

        Selection.Copy

        Windows("Sales Forecast -Actual.XLS").Activate

        Sheets("Forecast").Select

        Range("A215").Select

        ActiveSheet.Paste

        Sheets("home").Select

        Windows("charlotte.xls").Activate

        ActiveWindow.Close

    End If

End With



End Sub

[Rick M]

Still trying:
It works as long as the file exist. When the file does not exist, it errors right after the .execute statement

Workbooks.Open "C:Documents and SettingsmoormanDesktopforecastcharlotte.xls"

like it believes the If .execute = 0 then

Thanks for your help. it is making me crazy.

[Ivan F Moala]

On 2002-05-15 05:47, Rick M wrote:
Still trying:
It works as long as the file exist. When the file does not exist, it errors right after the .execute statement

Workbooks.Open "C:Documents and SettingsmoormanDesktopforecastcharlotte.xls"

like it believes the If .execute = 0 then

Thanks for your help. it is making me crazy.

Not sure why it wouldn't work....worked on
my system ??

Try adding this line >

.NewSearch

eg.
With fs
.NewSearch
.LookIn = "C:Documents and SettingsmoormanDesktopFORECAST"

[Rick M]

Thanks Ivan - fixed it using

Dim Path As String
Dim FileName As String

Path = "C:documents and SettingsmoormanDesktopFORECAST"
FileName = "charlotte.xls"

If UCase(FileName) = UCase(Dir(Path & FileName)) Then
Workbooks.Open Path & FileName


It works - that's all I know