Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

String size in Excel VBA

Posted on 2015-01-04
7
Medium Priority
?
108 Views
Last Modified: 2015-01-06
I have a string that contains a long SQL structure for retrieving data from SQL Server. Is there a work around solution to have longer string size to contain this long statement ?

Tks
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 800 total points
ID: 40530985
Use line continuations to split your statement into multiple lines. i.e.
strSQL = "Use line continuations" _
         & "to split your statement" _
         & "into multiple lines"

Open in new window

0
 

Author Comment

by:AXISHK
ID: 40530999
Seem like there is a size limitation in string strSQL, correct ?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40531002
About 2 billion characters. I seriously doubt you're even close to that limit.
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 46

Assisted Solution

by:aikimark
aikimark earned 1200 total points
ID: 40531565
There is a limit on the number of lines that can be continued

You may have to use string concatenation.
Example:
strSQL = "Select * "
strSQL = strSQL & "From mytable "
strSQL = strSQL & "Where fieldname = 42"

Open in new window


You could also read the SQL string from a file source.

================
Are you trying to store the SQL in an Excel cell?
0
 

Author Comment

by:AXISHK
ID: 40532550
The SQL statement is coded in a VBA. Any better way to handle this ?

For string size, it is not limited to 255 chars, correct ?

Tks
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40532906
Coding the string in VBA is fine as long as the lines are not too long (use line continuations or string concatenation).
String size isn't limited to 255 chars.
0
 

Author Closing Comment

by:AXISHK
ID: 40534876
Tks
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

649 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