Parameter Queries and For Each looping

[dave3944]

I have been working on this problem for a week, creating or copying code from the message board. I know I'm close to the solution, but I just can't seem to get things in the right sequence to get it done.

Here's what I'm trying to do:
I have a list of Part #'s (eg. 855205-405-00, so not technically an integer) on "Sheet 1", beginning in Cell B2. The list length is variable. I use the "number" on Sheet 1, Cell B2 and in a parameter query and place the retrieved data on "Sheet 2", Cell B4, then go back to "Sheet 1, retrieve the "number" in Cell B3 and place the data on Sheet 2, Cell B5. I use a For Each loop to repeat for however many Part #'s are on Sheet 1.

I am attempting to avoid adding a QueryTable for every Part # by creating one Query, name it for the first Part #, then refresh for each Part # in the For Each loop. Adding a query for every Part # also creates a Named range which must then be deleted.

Here's what I have so far:

Public Sub GetLevel1DataTestVer3()

' Goes to MAPICS, retrieves Bill of Materials data - Level 1
' and places it on Worksheet "Step 2"

Dim MyPN, sqlstring, connstring As String
Dim TargRng As range
Dim PartNum As range
Dim NextRow As Long

With Worksheets("Step 2")
.Activate
With range("A3:D100")
.NumberFormat = "General"
End With
End With

' Define range on Sheet "Step 1" which has the Part #
For Each PartNum In Worksheets("Step 1").Columns("B:B").SpecialCells(xlCellTypeConstants, 3)

'Define variable to insert into the sql statement
MyPN = "'" & PartNum.Value & "'"
Debug.Print MyPN

sqlstring = "SELECT PSTRUC.PINBR, PSTRUC.CINBR, ITEMASA.ITDSC, PSTRUC.QTYPR FROM S10B0361.AMFLIB6.ITEMASA ITEMASA, S10B0361.AMFLIB6.PSTRUC PSTRUC WHERE ITEMASA.ITNBR = PSTRUC.CINBR AND ((PSTRUC.PINBR= " & MyPN & ") AND (PSTRUC.QTYPR>0) AND (ITEMASA.ITTYP<'3')AND (ITEMASA.ITDSC Not Like '%IFE%'))"
connstring = "ODBC;DSN=ddt;Database=amflib6"

'Add the first Query and name it
With Worksheets("Step 2")
NextRow = .Cells(65536, 1).End(xlUp).Row + 1
If NextRow < 4 Then NextRow = 4
Set TargRng = .range("A" & NextRow)
.QueryTables.Add(Connection:=connstring, Destination:=TargRng, Sql:=sqlstring) _
.Refresh BackgroundQuery:=False
With ActiveSheet.QueryTables(1)
.Name = "Step1"
.FieldNames = False
End With
End With

Next PartNum

'Refresh existing Query
ActiveSheet.QueryTables(1).Refresh

End Sub

This works (as far as the looping goes). All Part #s are brought back with the appropriate information from the database, but here are my problems:

1) Each time the For Each statement loops, it brings back the Field Names from the database. I know that one can turn off the Field Names property in the Query Table, but I can't get the command in the right syntax in the right place.

2) In the first For Each statement, the SpecialCells method is used, identifying the data on Sheet "Step 1" as "(xlCellTypeConstant, 3)". From what I can gather from the Help screen, this means numeric data. but the Part # is not technically an Integer, so I think it is also looping through the Part # column heading (in Cell B1) and plugging it into the sql statement, returning the Field Names (since there is no data for a column heading in the database). I want only the Part #'s without the Column Heading.

3) Since I haven't figured out where and how to place the refresh statement, each time the macro is run Names (named ranges) are added to the Worksheet ad infinitum.

I need help!! This is making my brain hurt!!

 

[Norie]

Dave

Could you stick to the original thread, or at least provide a link to it?

Where are you getting the data from?

Do you know if the source supports the IN operator or something similar?

eg

SELECT *
FROM Orders
WHERE ShipRegion In (‘Avon’,’Glos’,’Som’)

What I'm thinking is rather than looping through each part number and doing a query, loop through them and create a string for the In operator.

Then you should only need 1 query.

I think you're right about the header, but you didn't actually mention a header.
Try this for the loop.

LastRow = Worksheets("Step 1").Range("B65536").End(xlUp).Row
For Each PartNum In Worksheets("Step 1").Range("B2:B"&  LastRow)

 

[dave3944]

I apologize for my error. I thought it was a different enough problem to warrant a new thread. I try not to dump too much on the Forum at one. I truly want to learn the steps and only come to the Forum when I am stumped. I will be more careful in the future.

I appreciate your help. I am using an IBM ODBC driver. I checked, and at least in the Help file, I should be able to use the IN operator. If I understand you correctly, that would be the ideal solution. But I didn't know if it could be done.

Your LastRow solution worked. The header is not retrieved.

 

[Norie]

Dave

You could use something like this code to construct the string for the IN operator.

LastRow = Worksheets("Step 1").Range("B65536").End(xlUp).Row 
For Each PartNum In Worksheets("Step 1").Range("B2:B"&  LastRow) 
     strPartNums = strPartNums & Chr(39) & Partnum.Text & Chr(39) 
Next PartNum

You could then try SQL like this.

sqlstring = "SELECT PSTRUC.PINBR, PSTRUC.CINBR, ITEMASA.ITDSC, PSTRUC.QTYPR FROM S10B0361.AMFLIB6.ITEMASA ITEMASA, S10B0361.AMFLIB6.PSTRUC PSTRUC WHERE ITEMASA.ITNBR = PSTRUC.CINBR AND ((PSTRUC.PINBR IN ( " & strPartNums  & ")) AND (PSTRUC.QTYPR>0) AND (ITEMASA.ITTYP<'3')AND (ITEMASA.ITDSC Not Like '%IFE%'))"

I've based the syntax for IN on what I know from Access, you may be have to change it for your data source.

 

[dave3944]

Kudos. One step closer. The modified sql statement was accepted. However, no data was returned. There were no errors, but no data.

A question. Would I still want to search for MyPN in the "IN" group, or comment that part out?

 

[Norie]

Dave

What do you mean?

Can you post the SQL you tried?

 

[dave3944]

I don't really know how do describe what I'm asking. Here's the sql statement.

sqlstring = "SELECT PSTRUC.PINBR, PSTRUC.CINBR, ITEMASA.ITDSC, PSTRUC.QTYPR FROM S10B0361.AMFLIB6.ITEMASA ITEMASA, S10B0361.AMFLIB6.PSTRUC PSTRUC WHERE ITEMASA.ITNBR = PSTRUC.CINBR AND ((PSTRUC.PINBR IN ( " & strPartNums & ")) AND (PSTRUC.QTYPR>0) AND (ITEMASA.ITTYP<'3')AND (ITEMASA.ITDSC Not Like '%IFE%'))"

What it looks like is that I have assembled a group to look in (which is what I wanted), but I'm not asking it to find a particular member of that group? When I included the strPartNums qualifier, I commented out the MyPN variable.

 

[Norie]

Dave

Like I said I based the syntax for IN on what I know of using it in Access.

Perhaps you need different syntax for your data source?

Is there anyway you can test the SQL?

You can output it to the immediate window (CTRL+G), including the IN part, using this

Debug.Print sqlstring

 

[dave3944]

I think I figured out what the problem is, but I don't know how to fix it.

The first problem is that the IN string is too long. When I tried to edit it in MS Query, I received that error. Is there a way we could split it up and insert it into the sql string as an OR statement?

The second problem is that in my ODBC driver, the string must be in the format of '855205-405-00','855205-406-00' (with a comma inbetween).

When I manually split the string into two groups and put in the commas, it fired off and returned the correct values. Now how to do this for the next time.

 

[Norie]

dave

This is partly my fault, I totally forgot about the comma., which is the syntax for Access as well.

Does this work?

LastRow = Worksheets("Step 1").Range("B65536").End(xlUp).Row 
For Each PartNum In Worksheets("Step 1").Range("B2:B"&  LastRow) 
     strPartNums = strPartNums & Chr(39) & Partnum.Text & Chr(39) & ","
Next PartNum  

PartNum= Left(PartNum, Len(PartNum)-1)