?
Solved

Passing a string value from one module to another without quotes in VB in Excel 2010

Posted on 2014-07-15
5
Medium Priority
?
307 Views
Last Modified: 2014-07-15
Experts,

I am trying to pass the following string to a module.

"('8772282202','1356366002','0250079405')"

When defining the string it is requiring me to put quotes around it.  However to use the string I do not want the quotes

I have this code to build it.
 strInList = "('" & strInList & "')"  

What do I do to be able to build the string and then use it, but without the quotes.

I want to my final result to be:
('8772282202','1356366002','0250079405')

This string will be used in a query
0
Comment
Question by:morinia
  • 2
  • 2
5 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40197134
are you using a query string to access your query or what?
0
 
LVL 35

Expert Comment

by:Norie
ID: 40197144
If you are concatenating this string with another the " wouldn't be included.
0
 

Author Comment

by:morinia
ID: 40197149
I read in variables from an Excel column.  The variables were stringed together as above.  I am concatenating the variables to build the string that would be used in the query.

ie.
Proc Sql;
   Create table active_members
      as  Select  a.*  from all_Members a
where  memberid in .................... (this is where I am concatenating the string in VB)
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 2000 total points
ID: 40197162
when you add the string to the query string your building the output of the string would be :
dim sql as string
dim ps as string
ps="('8772282202','1356366002','0250079405')"
sql="Create table active_members "
sql=sql & "as  Select  a.*  from all_Members a "
sql=sql & "where  memberid in " & ps

Open in new window

will be
Create table active_members as  Select  a.*  from all_Members a where  memberid in ('8772282202','1356366002','0250079405')
0
 
LVL 35

Expert Comment

by:Norie
ID: 40197167
As I said, when you concatenate the string the double quotes will not be included.
strInList = "('8772282202','1356366002','0250079405')"

strSQL =   "Create table active_members " &  _
                  "as  Select  a.*  from all_Members a " &  _
                  "where  memberid in "  & strInList

MsgBox strSQL

Open in new window

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

850 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