1

I've got this function:

Function GetFullNameCSV() As String
   GetFullNameCSV = Replace(ThisWorkbook.FullName, ".xlsm", ".txt")
End Function

If the code looked like this hard coded:

Filename:= "C:\directory\filename.txt"

I could just replace it with this and get the same result:

Filename:= GetFullNameCSV()

Problem is it appears in the case of this string:

Connection:= "TEXT;C:\directory\filename.txt"

I need to use string concatenation, guessing something like this:

Connection:= "\"TEXT;+GetFullNameCSV()+\""

What the code I need to use? Thanks.

Improve this question

1 Answer 1

Reset to default
3

Assuming that all the sections are always delimited with a ;, just split the string into it's parts and then do your changes and then join it together again.

So with your sample I think it would be something like (please note, written from memory, so might not be completely correct):

Dim parts() as String
parts = Split(str, ";")

parts(2) = GetFullNameCSV() ' assuming that part 2 is the one you want to replace

str = Join(parts, ";")
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

+1 but... that might work, but it's not string concatenation it's a delimited string split, transform, and join. Meaning that to me, that the code is taking something simple and making it complex. Meaning I just want to know how to do string concatenation in VBA. Feel free to leave your suggested approach, but I really need/want the way to do string concatenation.
@blunders: Well, I might have misunderstood your question, if you're just talking about concatenating strings together, all you need is the & operator, so it would be "TEXT;" & strTemp. However, if I have understood your question correctly, then I'd argue that my code is actually very easy for what it does, because under the hood it might do a lot of things, but that's all done by VBA so you don't have to worry about the details.
+1 Thanks, someone else posted that as well and then deleted it before I could ask this... Seems like to me "TEXT;" & strTemp would output "TEXT;"C:\directory\filename.txt when what I need is "TEXT;C:\directory\filename.txt" -- again, thanks!
So, it works -- which is odd, don't have time to think about it. Thanks for giving me the answer I needed. Cheers!
@blunders: The double quotes ("") are String delimiters, which means they get stripped to find the string within.To get the output you said you expected you'd need """TEXT;""" & strTemp. The first quote opens the string, the second escapes the third to make it a literal (then escape, literal, closing after).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.