Excel Error "Value does not fall within the expected range"

[jemmons90]

Hello all,

Has anyone else received this error? I have narrowed down the line that causes the error (bold in red below in second sub) but can't seem to fix it. I recently got a new computer using 64 bit Excel 2016. PLEASE HELLLPP!!!!

Function AddConnections() As Integer'Adds data connection(s)
'On Error GoTo 0:


Dim Sheet As Worksheet
Dim ws1 As Worksheet
Dim Pivot As PivotTable
Dim dd2 As DropDown
Dim sConnString As String
Dim sSql As String
Dim BoxNumber As String
Dim LocNumber As String
Dim StorerNumber As String
Dim ItemNumber As String
Dim StartDate As Long
Dim EndDate As Long
    
    'Set Objects
    Set ws1 = Sheets("Dashboard")
    
    Call GetItemList
    'Variables needed
    
    BoxNumber = UCase(ws1.Range("S3").Value)
    LocNumber = UCase(ws1.Range("S6").Value)
    StorerNumber = GatherStorers(ws1.Range("S9"))
    ItemNumber = ws1.Range("W4").Value


'    BoxNumber = Userform1.ComboBox1
'    LocNumber = Userform1.TextBox1
'    StorerNumber = Userform1.TextBox2
    StartDate = Userform1.TextBox3
    EndDate = Userform1.TextBox4
    
    
    'Connection String
    sConnString = "ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=" & BoxNumber & ";DBQ=QGPL;" _
    & "DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;SSL=;SIGNON=;"
    
    'Ordered Items Query
    
      sSql = "SELECT TITX.STRNBR, TITX.DOCNBR, TITX.ITMNBR, WITM.ITMDSC, RITM.RFORCS, RITM.RFPKCS, OBDTL.ODCRFR, OBDTL.ODSCDT, OBDTL.ODSCTM, WITM.CASPLT, WITM.CASTER, WITM.DFTBLD, WITM.DFTSCN, WITM.DFTISL, WITM.DFTROW, WITM.DFTLVL, WITM.DFTPOS " _
        & "FROM DSCBASDTA.OBDTL OBDTL, DSCBASDTA.RITM RITM, DSCBASDTA.TITX TITX, DSCBASDTA.WITM WITM " _
        & "WHERE RITM.DOCNBR = TITX.DOCNBR AND RITM.DOCSEQ = TITX.DOCSEQ AND RITM.ITMNBR = TITX.ITMNBR AND RITM.LOTNBR = TITX.LOTNBR AND RITM.STRNBR = TITX.STRNBR AND OBDTL.ODCNBR = TITX.DOCNBR AND WITM.ITMNBR = RITM.ITMNBR AND WITM.ITMNBR = TITX.ITMNBR AND WITM.STRNBR = RITM.STRNBR AND WITM.STRNBR = TITX.STRNBR AND WITM.LOCNBR = OBDTL.LOCNBR AND TITX.STRNBR = " & StorerNumber & " AND OBDTL.ODSCDT Between " & StartDate & " and " & EndDate & " " _
        & "ORDER BY OBDTL.ODSCDT, OBDTL.ODSCTM, TITX.ITMNBR "
    
    RunQuery Sheets("Ordered Items"), sConnString, sSql, "Table_Query_from_DSC07_1"
    
    With Sheets("Ordered Items")
        .ListObjects(1).Name = "OI_Table"
    End With
    
  
    Call setup
    
    AddConnections = 1
    
    Set Sheet = Nothing
    Set ws1 = Nothing
    Set Pivot = Nothing
    Set dd2 = Nothing
    Exit Function
    


    
End Function




Function RunQuery(ws As Worksheet, sConnString As String, sSql As String, DisplayName As String)
'Clears worksheet, runs sql query, and terminates the connection.
    ws.Cells.Clear
    
[B][I][COLOR=#ff0000]    With ws.ListObjects.Add(SourceType:=0, Source:=sConnString, _[/COLOR][/I][/B]
[B][I][COLOR=#ff0000]    Destination:=ws.Range("A1")).QueryTable[/COLOR][/I][/B]
        .CommandText = sSql
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        '.ListObject.DisplayName = DisplayName
        .Refresh BackgroundQuery:=False
    End With
    
    ActiveWorkbook.Connections("Connection").Delete
End Function

 

[Norie]

What are you actually querying?

 

[jemmons90]

I am putting together a list of all items going out on outbounds in our system.

 

[Norie]

So is it an Access database you are querying then?

 

[RoryA]

Your connection string appears to be for 32bit.

 

[jemmons90]

No it's my works servers and tables within our AS400.

I thought that too so I changed it to 64 and no data pulled in. When I get the unexpected error from excel and cancel out of the message it still pulls the data. I can sent a picture of the error and code of the message. I was trying to see if anyone out there has incurred this problem too when querying a table.