Split ActiveCell.Value and delete element if.........

[LuisE]

The active cell holds file(s) path(s) separated by ";".
I'm splitting the content of the cell, if it is not a valid file name I'd
like to delete that element. Is the deletion part doable? THANKS IN ADVANCE

When I use GetOpenFilename the extension of .doc files become .docx.
I'm removing the "x" to avoid confusing the user but when I run
CBool(Len(Dir(FileName) it returns FALSE obviously because a character is
missing. Any suggestions???


This is what I have.



Dim x As Variant
Dim i As Long
x = Split(ActiveCell, ";")

For i = 0 To UBound(x)
If CBool(Len(Dir(x(i)))) = False Then
MsgBox x(i) & "isn't a valid file" 'I WANT TO DELETE INSTEAD OF
MsgBox
End If
Next i

 

[Rick Rothstein \(MVP - VB\)]

If I understand what you want to do, something like this should work for
you...

Dim x As Variant
Dim i As Long
x = Split(ActiveCell.Value, ";")
For i = 0 To UBound(x)
If Len(Dir(x(i))) = 0 Then x(i) = ""
Next
x = Join(x, ";")
Do While InStr(x, ";;") > 0
x = Replace(x, ";;", ";")
Loop
ActiveCell.Value = x

Rick

 

[Vergel Adriano]

maybe like this:

Dim x As Variant
Dim i As Long
Dim strText As String

x = Split(ActiveCell, ";")

For i = 0 To UBound(x)
If Len(Dir(x(i))) > 0 Then
strText = strText & x(i) & ";"
End If
Next i

ActiveCell = Left(strText, Len(strText) - 1)

 

[LuisE]

Rick,

You got it. Works flawlessly. Thanks a lot for the prompt response.

Is the Len(Dir(x(i))) 100% reliable? I don't want to delete something that
I should not.

As for the text files showing extensions like ".docx" I was thinking of
making the ColorIndex of "x" 2, if there is not a better way to not mislead
he user. How can I accomplish it?

 

[Rick Rothstein \(MVP - VB\)]

Is the Len(Dir(x(i))) 100% reliable? I don't want to delete something

that I should not.

Sorry, I forgot to address that in my comments. The only way your
statement...

CBool(Len(Dir(x(i)))) = False

can evaluate to True is if the length of Dir(x(i)) is zero; hence, there is
no need to cast the 0 to a Boolean just to see if it is False.

As for the text files showing extensions like ".docx" I was thinking of
making the ColorIndex of "x" 2, if there is not a better way to not
mislead
he user. How can I accomplish it?

To be quite honest, I didn't really follow what the problem was with the
..doc/.docx (all I did was modify your existing code), so I'm not sure how to
answer you on this. Perhaps if you post some additional details, it might
become clearer to me (unless, of course, someone jumps in and gives you an
answer first).


Rick

 

[LuisE]

Rick, thanks for your time.

I'm using GetOpenFileName to place the name (and path) of the selected file
in the active cell. When a Word file is selected the extension returned is
..docx instead of .doc

e.g
C:\Users\luis\Documents\July 28.docx
I'm affraid the user would be tempted (as I was) to remove the "x"
considering it an error. Obviously if the "x" is removed, the Len(Dir()
validation won't work.

I was thinking of changing the color index of the X to 2 so that it will
become "not visible" at first sight.
How can it be done in this context? Any other ideas?

Thanks

 

[Rick Rothstein \(MVP - VB\)]

I guess if I were doing this in my own project, I would remove the "x" from
the filename returned by the GetOpenFileName function and display it in the
cell that way; then, in every place I needed to use the filename (save,
other manipulations requiring the actual filename, etc.), such as your

If CBool(Len(Dir(x(i)))) = False Then

test, other than displaying it in the cell, I would concatenate the "x" back
on just before making use of it. So, the above test would become...

If CBool(Len(Dir(x(i) & "x"))) = False Then

Rick