Hi,
I am working with Business Objects VBA and user forms, but I didn't find a user form topic, so I post my message here in the excel topic.
In order to solve an issue I have to manipulate a long string (more than a thousand character, but not reaching ten thousand character), which is an SQL statement.
The first problem I face with is the following: I load the parts of the select statement into a string array (which is a global string array declared in a module), and when I switch between the forms, suddenly I find that an end of file character is inserted at the end of each string in the string array, except for the first element. The declaration of the string is the following:
The code I use for "string cleaning" is the following:
The next problem is that after I did the modifications on the appropriate parts of the select statement, I have to load back the whole SQL statement into a textbox. This SQL statement can be very long, reaching 4000-5000 character in length. Sometimes I can insert only the select statement and VBA don't concatenate it with the string after the select part (I call this part SQLAfterFrom clause). I copy here the code I use to create the sql statement.
Any advice on what I am doing wrong would be highly appreciated.
Thanks,
Gabor
I am working with Business Objects VBA and user forms, but I didn't find a user form topic, so I post my message here in the excel topic.
In order to solve an issue I have to manipulate a long string (more than a thousand character, but not reaching ten thousand character), which is an SQL statement.
The first problem I face with is the following: I load the parts of the select statement into a string array (which is a global string array declared in a module), and when I switch between the forms, suddenly I find that an end of file character is inserted at the end of each string in the string array, except for the first element. The declaration of the string is the following:
Code:
Type SelectStatementType
NbRows As Integer
SelStat(99, 2) As String
DotPlace(99) As Integer
End Type
Global SS As SelectStatementTypeThe code I use for "string cleaning" is the following:
Code:
For i = 2 To SS.NbRows
SS.SelStat(i - 1, 0) = Mid(SS.SelStat(i - 1, 0), 1, Len(SS.SelStat(i - 1, 0)) - 1)
NextThe next problem is that after I did the modifications on the appropriate parts of the select statement, I have to load back the whole SQL statement into a textbox. This SQL statement can be very long, reaching 4000-5000 character in length. Sometimes I can insert only the select statement and VBA don't concatenate it with the string after the select part (I call this part SQLAfterFrom clause). I copy here the code I use to create the sql statement.
Code:
selectstatement = ""
For i = 1 To SS.NbRows
If i = 1 Then
selectstatement = SS.SelStat(i - 1, 0)
Else
selectstatement = selectstatement & Chr(13) & "," & SS.SelStat(i - 1, 0)
End If
Next
SQLAfterFrom = Mid(AnFvParam_02.SQLText.Text, InStr(AnFvParam_02.SQLText.Text, "FROM"))
SQLWholeText = Replace("SELECT " & selectstatement & SQLAfterFrom, "¶", Chr(13))
SQLText.Text = SQLWholeText
MsgBox (SQLWholeText)Any advice on what I am doing wrong would be highly appreciated.
Thanks,
Gabor
