VBA to create folders and sub folders trimming too much?

[wisemank]

Hi there,

I have code that works really well except for one thing... The is a trim command in the code I used and I would like to remove it but I keep getting out of range or syntax errors.

the issue part:
folder = Trim(Split(Target.TextToDisplay, " ")(1))

When it created the folder it only takes the first word in the string and I would prefer the whole string in the folder name.

complete script:

Public Sub Create_Folder_and_Link(ByVal Target As Hyperlink)

Dim folder As String

'Extracts folder name XXXXX from the TextToDisplay string which has the format "Create XXXXX folder"

folder = Trim(Split(Target.TextToDisplay, " ")(1))

If Right(MAIN_FOLDER, 1) = "" Then
folder = MAIN_FOLDER & folder & ""
Else
folder = MAIN_FOLDER & "" & folder & ""
End If

'Create folder if it doesn't exist

If Dir(folder, vbDirectory) = "" Then MkDir folder

If Dir(folder & "01_Reference_Info", vbDirectory) = "" Then MkDir folder & "01_Reference_Info"
If Dir(folder & "02_Work", vbDirectory) = "" Then MkDir folder & "02_Work"
If Dir(folder & "02_Work\01_CAD", vbDirectory) = "" Then MkDir folder & "02_Work\01_CAD"
If Dir(folder & "02_Work\02_Documents", vbDirectory) = "" Then MkDir folder & "02_Work\02_Documents"
'If Dir(folder & "03_CAD", vbDirectory) = "" Then MkDir folder & "03_CAD"


'Add in column F cell the hyperlink which opens this folder

With Worksheets(Target.Range.Parent.Name)
.Hyperlinks.Add anchor:=.Cells(Target.Range.Row, "G"), Address:=folder, TextToDisplay:="Open " & folder
End With

End Sub



I see why, but don't know how to take the string completely. Any help would be appreciated...

Kip

 

[bobsan42]

probably you should remove the SPLIT
what is the TextToDisplay property of the Hyperlink
Try this:

[B][COLOR=#FF0000][I]folder = Trim(Target.TextToDisplay)[/I][/COLOR][/B]

the SPLIT breaks the path into pieces if you have spaces in it

 

[baitmaster]

Hi Kip

The reason you're only getting the first word is as follows:
Split(textString, " ") converts any text string into an array of single words by cutting it up where the spaces are.
Referring to split(textString," ")(1) means you are looking at ONLY the first item in the array
So you're taking only the first word (i.e. text prior to a space) in a text string, getting rid of everything else, and then Trimming it

So, if you want to work with say the first 3 words, you would need to loop through the first 3 items in the array and join them back together. Alternative ways of joining an array back together could involve JOIN(array, "/")

Question is then, do you really want to do this split? Can you give some examples of your starting text, and what you want to turn it into, so we can suggest the right approach

 

[wisemank]

Hi there,

there are some bits missing here so bare with me...

In column C is a text string the concatenates some program information from the user (highlighted red below). When the user indexes through the columns a hyperlinked text string appears... see below

"Create Hond-001_Inner Tube Design concept folder" and when clicked it executes and creates the folders and shows "Open J:\Hotstamping\Advanced Engineering\Projects\Hond-001_Inner"

You can see the Open results trimmed the "Create" part of the string, but when I removed the trim function the word create remains...

 

[wisemank]

and also the actual details in the red text are not the same...

 

[bobsan42]

The problem is nothing to do with the TRIM function - it is in the way you use SPLIT.
However ...
I would get rid of the Create and Folder but if that the way you like it - try this (both lines):

[COLOR=#FF0000]folder = Trim(Target.TextToDisplay)
folder = Trim (Mid(folder, Len("Create ") + 1, Len(folder) - Len("Create ") - Len(" folder")))[/COLOR]

 

[wisemank]

Winner Winner Chicken Dinner! Perfecto! Gracias!