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!!

 

[dave3944]

I tried the new code but received a runtime error 91 "Object variable or With block variable not set". The error occurred on the line establishing the Length of PartNum. When I pass the cursor over PartNum it tells me that PartNum = Nothing.

 

[Norie]

Dave

Again my fault.

The line should look like this.

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

Please remember the code I'm posting is generally untested. Though it is, usually, syntactically correct and will compile.

 

[dave3944]

That did the trick. It's doing exactly as I want.

Thank you for your help.

This is my first foray into VBA programming. Actually, my first complete application. I can now take what I have learned and apply it to many other situations. This was a key component in being able to do that.

 

[Norie]

Hooray!

At last!

 

[dave3944]

Well. . . one more thing. Whenever the query runs creates a named range. Those keep stacking up. I'd like to clear the names before I run the query again. What's the best way to do that? I'm experimenting with different things right now, but any clues would be helpful.

 

[Norie]

Dave

How often are you running the code?

Do you need to rerun the code?

 

[dave3944]

The code will be run more than 100 times by at least 5 people. The named ranges would stack up pretty quickly. Maybe it's just a personal obsession. I want to leave the Workbook the way I found it, except for the new data.

I did some experimentation and came up with the following:

Dim qt As Name

For Each qt In .Names
qt.Delete
Next qt

I ran the macro 5 times and checked the Named ranges and there was only one. So, for now, I'm ready to move to the next component.

Thanks for the help. I couldn't have done it (well, maybe in a month) without your help.

 

[Norie]

This may be a stupid question but why will it be run so many times?

Are the part numbers changing regularly?

 

[dave3944]

It is absolutely conceivable. We have over 750,000 Part #'s in our system. Some go into one assembly, some go into many assemblies. Plus, Engineering is creating new Part #'s every day.

That's why this programming technology was so important. Thanks again for the help.