Using Range w/ SQL statement in VBA

S

supdawg

Well-known Member
Joined
Mar 18, 2007
Messages
608
So I know almost nothing about SQL. I've been reverse engeneering a odc file that pulls a query from a database.

I have modified it to be able to pull specific columns out of the table. What I would like to do is use a range as my

Code: 
PKMSID = InputBox("PKMS Login")
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};UID=" & PKMSID & ";SIGNON=1;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=Q" _
        ), Array("GPL WM0272PRDD;SYSTEM=PKMS0272.US.CORP;")), Destination:= _
        Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT PHPICK00.PHPKTN, PDPICK00.PDSTYL, PDPICK00.PDOPQT, PDPICK00.PDSTYD, PHPICK00.PHPSTF" & Chr(13) & "" & Chr(10) & "FROM CAPM01.WM0272PRDD." _
        , _
        "PDPICK00 PDPICK00, CAPM01.WM0272PRDD.PHPICK00 PHPICK00" & Chr(13) & "" & Chr(10) & "WHERE PDPICK00.PDPCTL = PHPICK00.PHPCTL AND ((PHPICK00.PHWH" _
        , "SE='BNA') AND (PHPICK00.PHPSTF=<'90') [B]AND (PHPICK00.PHPKTN IN ('677722691', '693205564'[/B])))")

The bolded part is where I would like to replace those two numbers with a range of numbers from an excel file.

Could be thousands of rows to look up. I know I could probably concantonate a string, but I would have to think there is an easier way to do this.

Just need to convert the range into a variable I can call from this code. Just not sure how exactly to do this.

Any help would be greatly appreciated. I am sure it's probably simple to those who are familiar with this.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Why not write a little function which accepts a range and returns a string (such as '677722691', '693205564') ready to be concatenated into your SQL query?
Code: 
Function GetInClauseFromRange(ByVal rngSource As Range) As String
 
 
End Function

The function will have to iterate through the cells in the range, putting single quotes around each value to produce the final output. For improved performance you can dereference the range like this:
Code: 
dim vValues As Variant
 
vValues = rngSource.Value

If rngSource is more than one cell then vValues will be a 2-D array. It's faster to loop through an array than a range.
 
Upvote 0
Hi Colin, thanks for the reply. I've been searching for a solution. I found a post on this site and it gave me a handy function:

Code: 
Function MakeSQL(rng As Range) As String
 
  Dim oCell As Range
  
  For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
  Next oCell
  
  MakeSQL = "IN ( " & Mid(MakeSQL, 2) & " )"
  
End Function

http://www.mrexcel.com/forum/showthread.php?t=530906

But I tried to get that to work in my code, but I couldn't get the syntax right. :confused:

I think it may work? Just need to figure out how to use this function with the code I posted above? Sorry, not very good at VBA I admit. I tried a couple different things and I kept getting errors.
 
Upvote 0
Okay, let's use that function you found as a basis. It's similar enough to what I suggested.

Let's suppose that Sheet1!A1:A2 contains the values 677722691 and 693205564. The last line of your code then becomes:

Code: 
 , "SE='BNA') AND (PHPICK00.PHPSTF=<'90') AND (PHPICK00.PHPKTN " & MakeSQL(Sheet1.Range("A1:A2")) & "))")
 
Upvote 0
Getting closer:

In the locals window, I see that the MakeSQL = "IN ( , '677722691' , '693205564' )"

Should there be a comma in the first part?

When I try to run as is, I get SQL Syntax Error... Is the function not correct?
 
Upvote 0
Yeah, there shouldn't be a comma at the start - it should read in the local window:
MakeSQL = "IN ('677722691' , '693205564' )"
Click to expand...


This is a result of a small mistake in the function you posted. Have a look at it and see if you can spot what it is. :)
 
Upvote 0
Ughh... :)

I am so terrible at noticing small errors in code. :(

I know there shouldn't be a beginning comma in the function and it should after the value, however, I don't know how I am supposed to end it without leaving an extra comma after the last item in the range. :/

If you could kindly point it out to me, I'd be very happy. I could spend 30 minutes here trying different things before I figured it out. Getting those commas, parantheses, and quotes straight is soooo hard for me for some reason.

This is what I tried... added the comma at the end.

MakeSQL = MakeSQL & " '" & oCell.Value & "', "
 
Upvote 0
Is it the mid function?

I was able to get it to this:

"IN ('677722691', '693205564')"

But still get SQL error.
 
Last edited:
Upvote 0
Sure, no problem, I thought I'd let you have a crack at it first. So let's have a look at what the function does:

Rich (BB code): 
Function MakeSQL(rng As Range) As String
 
  Dim oCell As Range
 
  For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
  Next oCell
 
  MakeSQL = "IN ( " & Mid(MakeSQL, 2) & " )"
 
End Function


Rich (BB code): 
Function MakeSQL(rng As Range) As String
The function is called MakeSQL. It receives a Range and it returns a String.


Rich (BB code): 
Dim oCell As Range
It uses one variable, which is a Range variable called oCell.


Rich (BB code): 
For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
Next oCell
The function loops through each cell in rng, appending each cell's value (surrounded by a few other characters) to the end of a string. Actually, to be sure that it loops through cells (not rows or columns), the first line should really be:
Rich (BB code): 
For Each oCell In rng.Cells

Let's suppose that rng is three cells containing the values 1, 2 and 3. Once the loop has finished, MakeSQL will hold this value:
Rich (BB code): 
 , '1' , '2' , '3'
Note that there is a space at the front before the first comma.

The next line of code is as follows:
Rich (BB code): 
MakeSQL = "IN ( " & Mid(MakeSQL, 2) & " )"


Let's break this into bit size pieces.

Rich (BB code): 
Mid(MakeSQL, 2)

The Mid function returns characters from the middle of a string. The 2 indicates the starting character. So what this does is it effectively lops off the first character of MakeSQL; MakeSQL becomes this:
, '1' , '2' , '3'
Click to expand...
There is now no space before the first comma.

The rest of the code line just wraps a few characters around it - "IN ( " at the start and " )" at the end - so it becomes:
IN ( , '1' , '2' , '3' )
Click to expand...

So the trouble is that the Mid function is failing to remove the first comma. SQL doesn't need these spaces, so a simple fix is to remove the space here:
Rich (BB code): 
For Each oCell In rng
    MakeSQL = MakeSQL & " , '" & oCell.Value & "'"
Next oCell
---->
Rich (BB code): 
  For Each oCell In rng
    MakeSQL = MakeSQL & ", '" & oCell.Value & "'"
  Next oCell

Now, when the Mid function lops of the first character it will remove that comma. Make sense?
 
Upvote 0
supdawg said:
Is it the mid function?

I was able to get it to this:

"IN ('677722691', '693205564')"

But still get SQL error.
Click to expand...

Correct. It was the Mid function. :)

Post the code you're using now and we'll have a look at it...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Application.Inputbox to Excel SQL Statement
Replies
2
Views
1K
Devas
D
S
  • Question Question
Type Mismatch Error using SQL statement w/ IN Operator inside VBA?
Replies
7
Views
3K
supdawg
S
S
  • Question Question
Need code to iterate to 1000 rows at a time..
Replies
2
Views
623
supdawg
S

Forum statistics

Threads
1,223,277
Messages
6,171,147
Members
452,382
Latest member
RonChand

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top