Problem with Window Message

[maperalia]

I wonder if you can help me to make this windows message works properly.
The macro I have opens "lst" and "dat" files by selecting a cell (see
below). However, I wanted to place a windows message warning that I did not
select a cell with the filename.

The problem is that my windows message appears regardless I pick the correct
cell with the filename and then open the file.

Could you please tell me what I am doing wrong?

Thanks in advance.
Maperalia


'**************************
Sub Open_file_dat_or_lst()
Application.ScreenUpdating = False

WO = Worksheets("DEFAULTS").Range("C3")
directory = "S:\GEOTEST\shears\" & WO & "\"
filetext = Selection.Value

If Dir(directory & filetext & ".lst") <> "" Then
Else
MsgBox "SELECT FILENAME CELL WITH DESCRIPTION"
End If

Workbooks.Open directory & filetext
Application.ScreenUpdating = False


If Right(filetext, 4) = ".dat" Then
Open_dat_File
ElseIf Right(filetext, 4) = ".lst" Then
Open_lst_File
Else
MsgBox (" .... unknown file type...")
End If
Application.ScreenUpdating = True

End Sub
'**************************

 

[Dave Peterson]

I'd double check the names listed in the worksheet and the names of the files in
that folder.

And maybe even add a msgbox to show that directory and filetext. Maybe the
directory name needs to be formated

.... & format(wo, "00000") & ...

 

[maperalia]

Dave;
Thanks for your quick response.
I sorry but I could not understand the format you adviced me:
..... & format(wo, "00000") & ...

Maperalia

 

[Dave Peterson]

What's in that WO variable? Is it a number? Do you need leading 0's in the
folder name?

If the answers are yes, then I should have been more explicit:

directory = "S:\GEOTEST\shears\" & format(WO,"00000") & "\"

So if WO contained 123, then directory would hold:
S:\GEOTEST\shears\00123\

====
You'd have to do the same kind of thing with a date:

directory = "S:\GEOTEST\shears\" & format(WO,"yyyy-mm-dd") & "\"

 

[maperalia]

Dave;
I did the change in the macro and I still have the problem where my windows
message appears regardless I pick the correct cell with the filename and then
open the file.

Could you please help me to fix it.

Thanks.
Maperalia

 

[Dave Peterson]

I still think it's your data.

I'd spend time looking for typos--maybe leading/trailing/embedded spaces.

Dave;
I did the change in the macro and I still have the problem where my windows
message appears regardless I pick the correct cell with the filename and then
open the file.

Could you please help me to fix it.

Thanks.
Maperalia

 

[maperalia]

Dave;
You are absolutely right. It is my data because I have used this statement
to open an excel file which has this description "B10 @ 10" and it is working
find.

However, the description I have now on my cells is either "(e-mail address removed)" or
"(e-mail address removed)" and for some reason it not taking it.

Do you think there is a way to adjust it?

Thanks.
Maperalia

 

[Dave Peterson]

Your code adds .lst in this line:

If Dir(directory & filetext & ".lst") <> "" Then

If you always put the extension in the cell, then don't include that ".lst" in
your code.

If you sometimes include it and sometimes not, what extension would you look
for?

FileText = Selection.Value

If Mid(FileText, Len(FileText) - 3, 1) = "." Then
'has extension, don't adjust anything
Else
FileText = FileText & ".lst"
End If

You may have to look for either .lst or .dat, too.

Dave;
You are absolutely right. It is my data because I have used this statement
to open an excel file which has this description "B10 @ 10" and it is working
find.

However, the description I have now on my cells is either "(e-mail address removed)" or
"(e-mail address removed)" and for some reason it not taking it.

Do you think there is a way to adjust it?

Thanks.
Maperalia