View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
LuisE LuisE is offline
external usenet poster
 
Posts: 133
Default Split ActiveCell.Value and delete element if.........

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)" wrote:

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


"LuisE" wrote in message
...
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