Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split ActiveCell.Value and delete element if.........
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split ActiveCell.Value and delete element if.........
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split ActiveCell.Value and delete element if.........
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) -- Hope that helps. Vergel Adriano "LuisE" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split ActiveCell.Value and delete element if.........
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split ActiveCell.Value and delete element if.........
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)" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split ActiveCell.Value and delete element if.........
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 "LuisE" wrote in message ... 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)" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete element is collection | Excel Programming | |||
Window Split and activecell | Excel Programming | |||
Delete String Element | Excel Programming | |||
Delete rows with common element | Excel Programming | |||
Delete the row where the activecell is located with VBA | Excel Programming |