Now it works, now it doesn't

[dave3944]

I have a macro that works on Worksheet Step 2, but gives me a runtime error 1004 Application-defined or Object-defined error. Here's the code:

Option Explicit
Public Sub GetDataStep3()

' Goes to MAPICS, retrieves data and places it on Worksheet "Step 3"
Dim sqlstring As String, connstring As String, strPartNums As String
Dim PartNum As Range, LastNum As Range
Dim NextRow As Long, LastRow As Long
Dim qt As Name

' Delete any previous queries and their named ranges
    With Worksheets("Step 3")
        .Activate
        .Range("A:F").Delete
        For Each qt In .Names
            qt.Delete
        Next qt
    End With
    
' Assemble the Part #'s to send to MAPICS query
    LastRow = Worksheets("Step 2").Range("B65536").End(xlUp).Row

        For Each PartNum In Worksheets("Step 2").Range("B2:B" & LastRow)
            strPartNums = strPartNums & Chr(39) & PartNum.Text & Chr(39) & ","
        Next PartNum
        
    strPartNums = Left(strPartNums, Len(strPartNums) - 1)
    
' MAPICS query connection parameters
    sqlstring = "SELECT PSTRUC.PINBR, PSTRUC.CINBR, ITEMASA.ITDSC, PSTRUC.QTYPR, ITEMASA.ITTYP FROM S10B0361.AMFLIB6.ITEMASA ITEMASA, S10B0361.AMFLIB6.PSTRUC PSTRUC WHERE ITEMASA.ITNBR = PSTRUC.CINBR AND ((PSTRUC.PINBR IN ( " & strPartNums & ")) AND (ITEMASA.ITDSC NOT LIKE '%TOOL%') AND (ITEMASA.ITDSC NOT LIKE 'MO%')) ORDER BY PSTRUC.PINBR"
    connstring = "ODBC;DSN=ddt;Database=amflib6"

' Add the Query results to Worksheet(Step 3)
    With Worksheets("Step 3").QueryTables.Add(Connection:=connstring, _
        Destination:=Worksheets("Step 3").Range("A1"), Sql:=sqlstring)
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

All I did was change Worksheet("Step 1") to Worksheet("Step 2"), (this is where I get the data from) and Worksheet("Step 2") to Worksheet("Step 3"), (this is where I put the data). All the Worksheets exist and are named correctly.

Any clues?

 

[Norie]

Where do you actually get the error?

 

[dave3944]

Sorry, Norie. Where are my manners?

    With Worksheets("Step 3").QueryTables.Add(Connection:=connstring, _
        Destination:=Worksheets("Step 3").Range("A1"), Sql:=sqlstring)

It errors out on this line.

 

[spammy]

Try getting rid of the underscore and make it all on ONE line - sometimes that fixes it

With Worksheets("Step 3").QueryTables.Add(Connection:=connstring, Destination:=Worksheets("Step 3").Range("A1"), Sql:=sqlstring)

 

[Richard Schollar]

ADVERTISEMENT

Replace:

With Worksheets("Step 3").QueryTables.Add(Connection:=connstring, _ 
        Destination:=Worksheets("Step 3").Range("A1"), Sql:=sqlstring) 
        .Refresh BackgroundQuery:=False 
    End With

with:

  With Worksheets("Step 3") .QueryTables
        .Add(Connection:=connstring, _ 
         Destination:=Worksheets("Step 3").Range("A1"), Sql:=sqlstring) 
        .Refresh BackgroundQuery:=False 
    End With

Regards

Richard

EDIT: Cut & pasted in the wrong place, now corrected.

 

[dave3944]

I was hoping that would work, but it didn't. Same error.

I'm trying to figure out what is different between the Worksheet("Step 2") and Worksheet("Step 3"). It works fine when retrieving data from Worksheet("Step 1") and placing it on Worksheet("Step 2").

 

[Richard Schollar]

ADVERTISEMENT

Dave

Apologies: I should have realised my suggestion was bogus since what you had was perfectly valid.

Have you been able to figure it out? Does it work if you return the sheetnames and the code back to Step1/Step2? It doesn't seem at all logical that it now doesn't work - I would be very interested to hear if you figure it out!

Thanks and good luck!

Richard

 

[dave3944]

I'm wondering if it has something to do with the length of the sql string I'm sending. It is definitely long. I found the following link which deals with that problem, but it's a bit confusing.

http://support.microsoft.com/default.aspx?scid=kb;en-us;818808

I remember searching for an answer to this problem in the past month. There was a solution by one of the MVP's, but it didn't wasn't the answer I needed at the time.

I'll reply with the results. I really appreciate your help.

 

[Richard Schollar]

Dave

I'm not sure that should give you any trouble here (it seemed to only apply where the strings were held in an array) but out of interest how long is your returned string? Presumably, it could be quite large depending on how many PartNum values there are.

Regards

Richard

 

[dave3944]

I did a Debug.Print (on Worksheet("Step 3") of the sql string (which included an extensive list of Part #'s), and copied it to a cell and used Len() to find out that there are 33,116 characters in the string. The Macro, when used on Worksheet("Step 2") produced a string with 13,990 characters. Maybe that could be the difference?