Run Time Error 1004: Application-Defined or Object-Defined Error

[Diana]

Hello again! Here is my beautiful code revisited, hoping to get a
touch of assistance from one of you wonderful Excel VBA experts! I'm
getting the error at the "Set oQt" line. Thanks in advance for any
help!

Sub ConnectTest()

Dim cellValue1 As String
Dim cellValue2 As String
Dim sh As Worksheet
Dim oQt As QueryTable
Dim sConn As String
Dim sSql As String

For Each sh In Sheets(Array("Inventory Analyzer", "Last 30 Days",
"Last 90 Days", "Last 180 Days", "Last 360 Days"))
cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

sSql = "SELECT DISTINCT ITEMS.ITEMNO, ITEMS.DESCRIPT, ITEMS.CATEGORY,
ITEMS.CUSTDATE1, QTYREC.QTY_REC1, InvoiceItemSum.Invoice_Sum,
X_STK_AREA.Q_STK, InvoiceSUM.ITEM_SUM, POSUM.ITEM_SUM2,
ITEMS.AVG_COST, ITEMS.AVG_SP "

sSql = "FROM X_STK_AREA LEFT OUTER JOIN ITEMS ON (X_STK_AREA.ITEM_NO =
ITEMS.ITEMNO) LEFT OUTER JOIN X_PO ON (X_PO.ITEM_CODE = ITEMS.ITEMNO)
LEFT OUTER JOIN X_INVOIC ON (X_INVOIC.ITEM_CODE = ITEMS.ITEMNO) LEFT
OUTER JOIN PO ON (PO.DOC_NO = X_PO.ORDER_NO) "

sSql = "LEFT OUTER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM0],
sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER JOIN
INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE
(INVOICES.STATUS = 8) AND (X_INVOIC.SHIPPED = 'T') AND
(INVOICES.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" & cellValue2
& "') GROUP BY X_INVOIC.ITEM_CODE) InvoiceItemSum ON ITEMS.ITEMNO =
InvoiceItemSum.ITEMSUM0 "

sSql = "LEFT OUTER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.ITEM_QTY) AS [ITEM_SUM] FROM X_INVOIC INNER JOIN INVOICES
ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE (X_INVOIC.STATUS =8)
AND (X_INVOIC.SHIPPED = 'F') AND (INVOICES.SHIPPED = 'F') AND
(INVOICES.PAID = 'F') AND (INVOICES.ORDER_DATE BETWEEN '" & cellValue1
& "' AND '" & cellValue2 & "') GROUP BY X_INVOIC.ITEM_CODE) InvoiceSUM
ON ITEMS.ITEMNO = InvoiceSUM.ITEMSUM "

sSql = "LEFT OUTER JOIN (SELECT X_PO.ITEM_CODE AS [ITEMSUM2],
sum(X_PO.ITEM_QTY) AS [ITEM_SUM2] FROM X_PO INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) WHERE (X_PO.STATUS =2) AND (X_PO.RECEIVED
= 'F') AND (PO.RECEIVED = 'F') AND (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "') GROUP BY X_PO.ITEM_CODE)
POSUM ON ITEMS.ITEMNO = POSUM.ITEMSUM2 "

sSql = "LEFT OUTER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI],
SUM(X_PO.QTY_REC) AS [QTY_REC1] FROM X_PO INNER JOIN PO ON (PO.DOC_NO
= X_PO.ORDER_NO) WHERE (X_PO.STATUS In (2,3)) AND (X_PO.RECEIVED =
'T') AND (PO.RECEIVED = 'T') AND (PO.ORDER_DATE BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "') GROUP BY X_PO.ITEM_CODE)
QTYREC ON ITEMS.ITEMNO = QTYREC.QTYRECI INNER JOIN INVOICES ON
(INVOICES.ORDER_NO = X_INVOIC.ORDER_NO) "

sSql = "WHERE ((ITEMS.ACTIVE='T') AND (ITEMS.INVENTORED='T') AND
(X_STK_AREA.AREA_CODE='MAIN') AND (X_INVOIC.STATUS='8') AND
(X_PO.STATUS In (2,3)) "

sSql = "AND (PO.ORDER_DATE BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "')) ORDER BY ITEMS.ITEMNO"

sConn = "ODBC;DSN=Everest;Description=Everest
data;UID=sa;PWD=SQL14mibank;"
sConn = "APP=Microsoft Office 2003;"
sConn = "WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network=DBMSS"

==Here is where the error is happening:

Set oQt = sh.QueryTables.Add(Connection:=sConn, _
Destination:=sh.Range("A8"), _
SQL:=sSql)

Next
End Sub

 

[Guest]

What values do you get if you

MsgBox sSql
MsgBox sConn

before

==Here is where the error is happening:

Set oQt = sh.QueryTables.Add(Connection:=sConn, _
Destination:=sh.Range("A8"), _
SQL:=sSql)

 

[Diana]

What values do you get if you

MsgBox sSql
MsgBox sConn

before

==Here is where the error is happening:

Set oQt = sh.QueryTables.Add(Connection:=sConn, _
Destination:=sh.Range("A8"), _
SQL:=sSql)

Hello again urkec!

The message boxes give me these values:

MsgBox sSql = And(PO.Order_Date Between '3/1/2006' and '4/13/2007'))
Order By Items.Itemno
MsgBox sConn = WSID=AndyHP5300;Database=Everest_VGI;Network=DBMS

Pretty cool, didn't know you could put a message box right in the
middle of things like that!

 

[Diana]

Hello again urkec!

The message boxes give me these values:

MsgBox sSql = And(PO.Order_Date Between '3/1/2006' and '4/13/2007'))
Order By Items.Itemno
MsgBox sConn = WSID=AndyHP5300;Database=Everest_VGI;Network=DBMS

Pretty cool, didn't know you could put a message box right in the
middle of things like that!

Okay so I've been working with the code, and have been able to get the
error to at least change...now I'm getting "Run-Time Error 438 Object
doesn't support this property or method" at the same line. Here is
what I added:

Set ws = Sheets(Array("Inventory Analyzer", "Last 30 Days", "Last 90
Days", "Last 180 Days", "Last 360 Days"))

For Each sh In ws
cellValue1 = sh.Range("B3").Value
cellValue2 = sh.Range("B4").Value

sSql..........
sConn.......

==Error is here:

With ws
Set oQt = .QueryTables.Add(Connection:=sConn, _
Destination:=.Range("A8"), _
SQL:=sSql)
End With
Next

 

[George Nicholson]

shouldn't "With ws" (the Sheet array) be "With sh" (the current member of
the array)?

It would certainly explain "Object doesn't support this property or method"
errors.

HTH,

 

[Diana]

shouldn't "With ws" (the Sheet array) be "With sh" (the current member of
the array)?

It would certainly explain "Object doesn't support this property or method"
errors.

HTH,










- Show quoted text -

Hehe...you just may be right...however, doing that gives me the 1004
error again! Whoopie!

Back to square four (not one because I"m further along than that)!

Thanks for the reply =)

 

[Diana]

Hehe...you just may be right...however, doing that gives me the 1004
error again! Whoopie!

Back to square four (not one because I"m further along than that)!

Thanks for the reply =)- Hide quoted text -

- Show quoted text -

Any other suggestions? I'm about to give up on this thing, har har...

Thanks!