[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Line break in Excel VBA

Posted on 2014-08-21
12
Medium Priority
?
170 Views
Last Modified: 2014-11-17
what is the correct syntax to break up the extra lengthy string within VBA with Excel 2010.  I want to wrap this text so it is easier to read - just like MZ tools use to do.

Thanks,

K
         .commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext8 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Queryposttext8

Open in new window

0
Comment
Question by:Karen Schaefer
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 40277474
The underscore is used in VBA as a line continuation character:
.commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Querypretext8 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
Range("H4").Value & "'" & Queryposttext8

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 40277537
thanks that helped accept now my results are not returning the correct value.

I am currently getting a duplicate:

and call_failure.failure_date >= '2014-07-09' and call_failure.failure_date <= '2014-08-21' '2014-07-09' and call_failure.failure_date <= '2014-08-21'

Got any ideas where it is duplicating within the string?
0
 

Author Comment

by:Karen Schaefer
ID: 40277545
Here is the code:
Sub RefreshQuery()
 Dim Querypretext1 As String
 Dim Queryposttext1 As String
 Dim valueToFilter As String
 Dim valueToFilter2 As String
 Dim ValueToFilter3 As String
 Dim Comtext As String
 Dim Querypretext2 As String
 Dim Queryposttext2 As String
 Dim Querypretext3 As String
 Dim Queryposttext3 As String
 Dim Querypretext4 As String
 Dim Queryposttext4 As String
 Dim Querypretext5 As String
 Dim Queryposttext5 As String
 Dim Querypretext6 As String
 Dim Queryposttext6 As String
 Dim Querypretext7 As String
 Dim Queryposttext7 As String
 Dim Querypretext8 As String
 Dim Queryposttext8 As String
  
 
 valueToFilter = "call_failure.failure_date >="
 valueToFilter2 = "call_failure.failure_date <="
 ValueToFilter3 = " and call_failure.failure_date <="

 With ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net drivetest").OLEDBConnection
      
     Comtext = .commandtext
     Queryposttext1 = Queryparam(Comtext, valueToFilter, valueToFilter2, Querypretext1)
     Queryposttext2 = Queryparam(Queryposttext1, valueToFilter, valueToFilter2, Querypretext2)
     Queryposttext3 = Queryparam(Queryposttext2, valueToFilter, valueToFilter2, Querypretext3)
     Queryposttext4 = Queryparam(Queryposttext3, valueToFilter, valueToFilter2, Querypretext4)
     Queryposttext5 = Queryparam(Queryposttext4, valueToFilter, valueToFilter2, Querypretext5)
     Queryposttext6 = Queryparam(Queryposttext5, valueToFilter, valueToFilter2, Querypretext6)
     Queryposttext7 = Queryparam(Queryposttext6, valueToFilter, valueToFilter2, Querypretext7)
     Queryposttext8 = Queryparam(Queryposttext7, valueToFilter, valueToFilter2, Querypretext8)
      
     
    ' queryPreText1 = .commandtext
   '  paramPosition1 = InStr(Right(.commandtext, Len(.commandtext) - InStrRev(.commandtext, "AND")), valueToFilter) + Len(valueToFilter) - 1 + InStr(.commandtext, "AND")
  '   queryPreText1 = Left(queryPreText1, paramPosition1)
  '   queryPostText1 = .commandtext
  '   Postlen = Len(queryPostText1) - paramPosition1 - (Len(Range("H3").Value) + 3)
   '  queryPostText1 = Right(queryPostText1, Postlen)
     
    ' queryPreText2 = queryPostText1
    ' paramPosition2 = InStr(Right(queryPostText1, Len(queryPostText1) - InStrRev(queryPostText1, "AND")), valueToFilter) + Len(valueToFilter) - 1 + InStr(queryPostText1, "AND")
   '  queryPreText2 = Left(queryPreText2, paramPosition2)
     
     
     
 '    queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
    ' .commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Queryposttext7
         .commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext8 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Queryposttext8
    Debug.Print Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext2 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext3 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext4 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext5 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext6 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext7 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value & "'" & Querypretext8 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & _
                Range("H4").Value
                ' & "'" & Queryposttext8

 End With
 ActiveWorkbook.Connections("database name").Refresh
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40277594
1) where is Querypretext.. assigned?

I am wondering if you could greatly simplify this code by assigning your substrings to arrays, like so:
Sub RefreshQuery()
    Dim strQueryPreText(8) As String
    Dim strQueryPostText(8) As String
    Dim strValueToFilter(3) As String
    Dim strComtext As String
    Dim x As Integer
    
    strValueToFilter(1) = "call_failure.failure_date >="
    strValueToFilter(2) = "call_failure.failure_date <="
    strValueToFilter(3) = " and call_failure.failure_date <="
    
    With ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net drivetest").OLEDBConnection
        strComtext = .CommandText
        strQueryPostText(1) = QueryParam(strComtext, strValueToFilter(1), strValueToFilter(2), strQueryPreText(1))
        For x = 2 To 8
            strQueryPostText(x) = QueryParam(strQueryPostText(x - 1), strValueToFilter(1), strValueToFilter(2), strQueryPreText(x))
        Next x
        For x = 1 To 8
            .CommandText = .CommandText & " " & strQueryPreText(x) & " '" & Range("H3").Value & "'" & strValueToFilter(3) & " ' " & Range("H4").Value & "' "
        Next x
       Debug.Print .CommandText
    
    End With
    ActiveWorkbook.Connections("database name").Refresh
End Sub

Open in new window


Regards,
-Glenn
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40278690
Is that the entire output from the Debug.Print statement? There should be eight sets of query statements. You should print out the Querypretest variables to verify they contain the values you are expecting.
0
 

Author Comment

by:Karen Schaefer
ID: 40279597
Glen,

thanks again, however still getting dual dates:

select standardized_issue_category as reportgroup,  count(call_failure.id) as Occurence ,failure_date,
left(cluster,3) as cluster, is_dropped_call,project_name, standardized_issue_category as defect_id, platform_name, subcluster
from call_failure where standardized_issue_category = 'Traces Unavailable'
and defect_id is null and other_defect is null
and (tracking_status <> 'On Hold' or tracking_status is null)
and call_failure.failure_date >= '2014-07-09' and call_failure.failure_date <= ' 2014-08-22'   '2014-07-09' and call_failure.failure_date <= '2014-08-21' '2014-07-09' and call_failure.failure_date <= '2014-08-21'
group by standardized_issue_category  , failure_date,cluster, is_dropped_call,project_name,defect_id, platform_name, subcluster

union
select standardized_issue_category as reportgroup,  count(call_failure.id) as Occurence ,failure_date,
left(cluster,3) as cluster, is_dropped_call,project_name, standardized_issue_category as defect_id, platform_name, subcluster
from call_failure where standardized_issue_category = 'Traces Unavailable'
and defect_id is null and other_defect is null
and (tracking_status <> 'On Hold' or tracking_status is null)
and call_failure.failure_date >= '2014-07-09' and call_failure.failure_date <= ' 2014-08-22'
0
 

Author Comment

by:Karen Schaefer
ID: 40279604
and also getting error:

Application defined or object defined error on the :
        .commandtext = .commandtext & " " & strQueryPreText(x) & " '" & Range("H3").Value & "'" & strValueToFilter(3) & " ' " & Range("H4").Value & "' "

portion of the code.
0
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40282830
Put a break point on the line with the error and view the object values in either the Immediate window or add a watch for each variable.

Also, to reiterate, please print out the strQueryPreText variables to verify they contain the values you are expecting. My guess is that your QueryParam function is not returning the strQueryPreText values that you are expecting.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40283322
Another thing to check is the actual number of strQueryPreText values being assigned.  It looks like in your example only three values (identical) are assigned.  The loop method could be modified to use UBound(strQueryPreText) to limit it.

Can you please state what the values in cells H3 and H4 are?  I assume they're dates, but want to be sure.

And is this statement the .CommandText value at the start (i.e., for your OLEDB connection)?
Select standardized_issue_category as reportgroup,  count(call_failure.id) as Occurence ,failure_date,
 left(cluster,3) as cluster, is_dropped_call,project_name, standardized_issue_category as defect_id, platform_name, subcluster
 from call_failure where standardized_issue_category = 'Traces Unavailable'
 and defect_id is null and other_defect is null
 and (tracking_status <> 'On Hold' or tracking_status is null)
0
 

Author Comment

by:Karen Schaefer
ID: 40283940
Well the original Sql Statment contains 8 Select statements via the Unions, however, the current code is duplicating its select statements.

Please keep in mind I inherited the original code and I need to improve the processing of the query which sets the Properties of the Data Connection.  The current code fails due to the duplication of the Sql statement.

so somewhere in the code it its inputted the original data connections property and then adding the revised statement.  causing the error.

"Can you please state what the values in cells H3 and H4 are?  I assume they're dates, but want to be sure."  
Yes they are the dates which are inputted on the form.

However after review of the step thru code , it appears I just need to remove the existing property value for the datasource and replace it with the updated with the correct dates.  How would replace the sql statement on the fly?

thanks for the assistance

Sub RefreshQuery()
    Dim strQueryPreText(8) As String
    Dim strQueryPostText(8) As String
    Dim strValueToFilter(3) As String
    Dim strComtext As String
    Dim x As Integer
    
    strValueToFilter(1) = "call_failure.failure_date >="
    strValueToFilter(2) = "call_failure.failure_date <="
    strValueToFilter(3) = " and call_failure.failure_date <="
    
    With ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net drivetest").OLEDBConnection
        strComtext = .commandtext
        strQueryPostText(1) = Queryparam(strComtext, strValueToFilter(1), strValueToFilter(2), strQueryPreText(1))
        For x = 2 To 8
            strQueryPostText(x) = Queryparam(strQueryPostText(x - 1), strValueToFilter(1), strValueToFilter(2), strQueryPreText(x))
        Next x
        'Debug.Print strQueryPostText(x)
        For x = 1 To 8
            .commandtext = .commandtext & " " & strQueryPreText(x) & " '" & Range("H3").Value & "'" & strValueToFilter(3) & " ' " & Range("H4").Value & "' "
        Next x
       Debug.Print .commandtext
    
    End With
 ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net drivetest").Refresh
End Sub

Open in new window


Sub RefreshQuery_daily()
 Dim Querypretext1 As String
 Dim Queryposttext1 As String
 Dim valueToFilter As String
 Dim valueToFilter2 As String
 Dim Comtext As String
 Dim Querypretext2 As String
 Dim Queryposttext2 As String
  
 
 valueToFilter = "date_of_daily >="
 valueToFilter2 = "date_of_daily <="
 ValueToFilter3 = " and date_of_daily <="

 With ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net master_failure_daily").OLEDBConnection
      
     Comtext = .commandtext
     Queryposttext1 = Queryparam(Comtext, valueToFilter, valueToFilter2, Querypretext1)
           
     .commandtext = Querypretext1 & " '" & Range("H3").Value & "'" & ValueToFilter3 & " '" & Range("H4").Value & "'" & Queryposttext1
 End With
 ActiveWorkbook.Connections("eng-sql1.eng.mobilephone.net master_failure_daily").Refresh
End Sub

Open in new window

Function Queryparam(commandtext As String, valueToFilter As String, valueToFilter2 As String, ByRef Querypretext As String)
Dim paramPosition1 As Integer
Dim paramPosition2 As Integer
 
     Querypretext = commandtext
     paramPosition1 = InStr(Right(commandtext, Len(commandtext) - InStrRev(commandtext, "AND")), valueToFilter) + Len(valueToFilter) - 1 + InStr(commandtext, "AND")
     Querypretext = left(Querypretext, paramPosition1)
     paramPosition2 = InStr(Right(commandtext, Len(commandtext) - InStrRev(commandtext, "AND")), valueToFilter2) + Len(valueToFilter2) - 1 + InStr(commandtext, "AND")
     queryPostText = commandtext
     Postlen = Len(queryPostText) - paramPosition2 - 13
     queryPostText = Right(queryPostText, Postlen)
     Queryparam = queryPostText
'     Debug.Print Queryparam
End Function

Open in new window

0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40446858
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question