Formatting Fixed Width Text by concatenating Multiple strings (1 Viewer)

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
Hello,

I am struggling on formatting text string which is a result of concatenating multiple columns from a table.

Here is the scenario I am using Treeview control (MSComctlLib.TreeView) as Treeview does not support multiple columns, I would like to concatenate string and format in a way that it will visually look like a multi-column TreeView (see attached).

As the length of Column text (1,2,3,4) may vary and I would like to show this in a formatted way so that everything looks like a data in column. I will appreciate any idea/directions in this?

Best regards
Mohsin
 

Attachments

  • MultiColumnFormattedString.jpg
    MultiColumnFormattedString.jpg
    99.9 KB · Views: 104

vba_php

Forum Troll
Local time
Today, 18:09
Joined
Oct 6, 2019
Messages
2,884
I would like to concatenate string and format in a way that it will visually look like a multi-column TreeView (see attached).
what your image looks like is what would be written in an access query like this:
Code:
select field1 & " " & field2 & " " & field3 & " " field4, field1, field2, field3, field4 from table.
that's exactly what it looks like. then of course you would display that query result through a form's interface.
 

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
what your image looks like is what would be written in an access query like this:
Code:
select field1 & " " & field2 & " " & field3 & " " field4, field1, field2, field3, field4 from table.
that's exactly what it looks like. then of course you would display that query result through a form's interface.

This is not the requirement, I would like to concatenate and format the string so that it looks like separate column data in Treeview (As treeview does not support multi-columns and I am looking for a way to format the string so that the output rendered in Treeview is formatted as the way it looks like seperate column, let me know if this makes sense?
 

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
The field1, field2, field3, field4 length also varies so I would like to trim the columns to specific characters to achieve the desired results. I will appreciate if there is any code to concatenate and format fixed string texts.
 

vba_php

Forum Troll
Local time
Today, 18:09
Joined
Oct 6, 2019
Messages
2,884
The field1, field2, field3, field4 length also varies so I would like to trim the columns to specific characters to achieve the desired results. I will appreciate if there is any code to concatenate and format fixed string texts.
I don't have time to write code for you right now, but it is very simple. in the Access VBA help articles, look up the functions LEFT(), MID(), RIGHT() and INSTR(). You will use those to do what you want. they can be learned in 10 minutes tops.
 

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
I don't have time to write code for you right now, but it is very simple. in the Access VBA help articles, look up the functions LEFT(), MID(), RIGHT() and INSTR(). You will use those to do what you want. they can be learned in 10 minutes tops.

Thanks but this is not what I am looking for, I am looking for something that works in Console App to format strings let say PadLeft/PadRight. I am entirely looking for something that format string so that it should visually look like separate columns. Treeview does not support multi-columns by design and the only way I can do this to concatenate and format the string so that it visually looks like columns.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:09
Joined
Sep 21, 2011
Messages
14,048
You could try using a | as a separator to mimic columns perhaps.
Then use Left() function for as many characters as you choose for each.

So something along the lines of

Code:
strToDisplay = Left(Field1,10) & "|" &  Left(Field2,10) &  "|" & Left(Field3,10)
etc.

Might have to make the font a font like Courier New to keep the same spacing for each character.?

HTH
 

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
These line of code does not produces 100% required output as some columns in line got extra spacing compared to other.

Code:
For j = 0 To UBound(FixedColumnStringArray)
	strFieldValue= Left$(rstTreeview.Fields(j+1).value, s(j))
	strLineForTreeViewNode= strLineForTreeViewNode& IIf(j = 0, "", vbTab) & strFieldValue& String$(s(j) - Len(strFieldValue), Chr$(32))
Next j
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:09
Joined
Sep 21, 2011
Messages
14,048
So use Trim() function?
 

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
So use Trim() function?
Thanks Gasman, I have just tried Trim and the same results so far. Any other directions. I am not sure if something with vbTab that breaks the string formatting.

For j = 0 To UBound(FixedColumnStringArray)
strFieldValue= Left$(Trim(rstTreeview.Fields(j+1).value), s(j))
strLineForTreeViewNode= strLineForTreeViewNode& IIf(j = 0, "", vbTab) & strFieldValue& String$(s(j) - Len(strFieldValue), Chr$(32))
Next j
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:09
Joined
Sep 21, 2011
Messages
14,048
What is this meant to be doing?

Code:
String$(s(j) - Len(strFieldValue)
Build it up piece by piece.
 

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
What is this meant to be doing?

Code:
String$(s(j) - Len(strFieldValue)
Build it up piece by piece.

Hi Gasman,

I have pasted my code below. Please check and let me know your thoughts

Code:
'Some Variable Declarations here
'Some Recordset Operations here
'Some Treeview Initialize events here

Dim FixedColumnStringArray(4) As Integer

FixedColumnStringArray(0) = 25 'Column 1 Width
FixedColumnStringArray(1) = 25 'Column 2 Width
FixedColumnStringArray(2) = 25 'Column 3 Width
FixedColumnStringArray(3) = 35 'Column 4 Width
FixedColumnStringArray(4) = 35 'Column 5 Width

strRecordKey=rstTreeView!UniqueKey

For j = 0 To UBound(FixedColumnStringArray)
	strFieldValue= Left$(Trim(rstTreeview.Fields(j+1).value), FixedColumnStringArray(j))
	strLineForTreeViewNode= strLineForTreeViewNode& IIf(j = 0, "", vbTab) & strFieldValue& String$(FixedColumnStringArray(j) - Len(strFieldValue), Chr$(32))
Next j


strLineForTreeViewNode = Trim(Left(strLineForTreeViewNode, 255))

Set cEmails = cMain.AddChild(strRecordKey, strLineForTreeViewNode)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:09
Joined
May 21, 2018
Messages
8,463
You can make each column equal width by padding the string as necessary. Call this function in conjunction with your code before concatenating. Pass the string in and pad it as necessary.

Code:
Public Function GetFixedWidth(strIn As String, MaxCharacters As Integer)
  'Leave at least a space between columns
  Dim pad As String
  Dim padCount As Integer
  strIn = Left(strIn, MaxCharacters - 1) ' See if the str is longer than you column maxcharacters leave one for pad
  padCount = MaxCharacters - Len(strIn)
  pad = String(padCount, " ") ' pad with appropriate
  strIn = strIn & pad
  GetFixedWidth = strIn
End Function
 

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
Thanks MajP, I have tried this for padding and the only workaround that works for me is to use monospaced font suggested by Gasman, I will appreciate if there are still any ways to format the string for non-monospaced font.

You can make each column equal width by padding the string as necessary. Call this function in conjunction with your code before concatenating. Pass the string in and pad it as necessary.

Code:
Public Function GetFixedWidth(strIn As String, MaxCharacters As Integer)
  'Leave at least a space between columns
  Dim pad As String
  Dim padCount As Integer
  strIn = Left(strIn, MaxCharacters - 1) ' See if the str is longer than you column maxcharacters leave one for pad
  padCount = MaxCharacters - Len(strIn)
  pad = String(padCount, " ") ' pad with appropriate
  strIn = strIn & pad
  GetFixedWidth = strIn
End Function
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:09
Joined
May 21, 2018
Messages
8,463
I know of no way to do this without monospace. That was assumed.
 

Mohsin Malik

Registered User.
Local time
Tomorrow, 03:09
Joined
Mar 25, 2012
Messages
175
I am closing this out and the only way that works in this case is to use a monospaced font. I have posted the final output (Dummy Data) that may help anyone having a similar issue.
 
Last edited:

Users who are viewing this thread

Top Bottom