Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004: Application-Defined or Object-Defined Error
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=DBMS S" ==Here is where the error is happening: Set oQt = sh.QueryTables.Add(Connection:=sConn, _ Destination:=sh.Range("A8"), _ SQL:=sSql) Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004: Application-Defined or Object-Defined Error
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) -- urkec |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004: Application-Defined or Object-Defined Error
On Apr 13, 11:38 am, urkec wrote:
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) -- urkec 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004: Application-Defined or Object-Defined Error
On Apr 13, 12:11 pm, "Diana" wrote:
On Apr 13, 11:38 am, urkec wrote: 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) -- urkec 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 he With ws Set oQt = .QueryTables.Add(Connection:=sConn, _ Destination:=.Range("A8"), _ SQL:=sSql) End With Next |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004: Application-Defined or Object-Defined Error
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" wrote in message ps.com... On Apr 13, 12:11 pm, "Diana" wrote: On Apr 13, 11:38 am, urkec wrote: 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) -- urkec 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 he With ws Set oQt = .QueryTables.Add(Connection:=sConn, _ Destination:=.Range("A8"), _ SQL:=sSql) End With Next |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004: Application-Defined or Object-Defined Error
On Apr 16, 2:05 pm, "George Nicholson"
wrote: 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" wrote in message ps.com... On Apr 13, 12:11 pm, "Diana" wrote: On Apr 13, 11:38 am, urkec wrote: 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) -- urkec 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 he With ws Set oQt = .QueryTables.Add(Connection:=sConn, _ Destination:=.Range("A8"), _ SQL:=sSql) End With Next- Hide quoted text - - 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 =) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 1004: Application-Defined or Object-Defined Error
On Apr 16, 3:11 pm, "Diana" wrote:
On Apr 16, 2:05 pm, "George Nicholson" wrote: 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" wrote in message ups.com... On Apr 13, 12:11 pm, "Diana" wrote: On Apr 13, 11:38 am, urkec wrote: 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) -- urkec 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 he With ws Set oQt = .QueryTables.Add(Connection:=sConn, _ Destination:=.Range("A8"), _ SQL:=sSql) End With Next- Hide quoted text - - 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 =)- Hide quoted text - - Show quoted text - Any other suggestions? I'm about to give up on this thing, har har... Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run Time Error 1004 - Application-defined or object-defined error | Excel Programming | |||
Run-time Error 1004: Application-defined or Object-defined Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming |