Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
How can i find field "Item_Number" in Oracle Object if found need to get
Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = €śSELECT item_number, Item_Description FROM mtl_system_items_b€ť Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
Hi Sakol:
I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = €śSELECT item_number, Item_Description FROM mtl_system_items_b€ť Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
In additional if i have 3 fields Item_number, Item_Description, and Quantity
(see Example). The object from Oracle contain more than 1 records of Item_number, I need to fill-out Description, sum quantity to excel sheet1 that Item_Number find & match to the Oracle Object. Example:- Oracle Object: Item_Number Item_Description Quantity A1 A1_Description 100 A1 A1_Description 200 B1 B1_Description 300 B1 B1_Description 400 Excel Sheet1: Item_Number Description Quantity A1 ? ? B1 ? ? Thanks -- Sakol "Martin Fishlock" wrote: Hi Sakol: I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = €śSELECT item_number, Item_Description FROM mtl_system_items_b€ť Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
OK, Sakol, try this but you may have to play around with it for a bit.
You SQL needs to be like this: SQL_Text = €śSELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b€ť & _ " GROUP BY Item_number, Item_Description" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) on error resume next If oraDynaSet.RecordCount 0 Then oraDynaSet.RecordCount for i=1 to oraDynaSet.RecordCount ' not sure if you use ! or . worksheets("sheet1").range("a" & i) = _ oraDynaSet!Item_number worksheets("sheet1").range("b" & i) = _ oraDynaSet!Item_Description worksheets("sheet1").range("c" & i) = _ oraDynaSet!SumQty oraDynaSet.movenext next i ' now do the clean up and close connections. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: In additional if i have 3 fields Item_number, Item_Description, and Quantity (see Example). The object from Oracle contain more than 1 records of Item_number, I need to fill-out Description, sum quantity to excel sheet1 that Item_Number find & match to the Oracle Object. Example:- Oracle Object: Item_Number Item_Description Quantity A1 A1_Description 100 A1 A1_Description 200 B1 B1_Description 300 B1 B1_Description 400 Excel Sheet1: Item_Number Description Quantity A1 ? ? B1 ? ? Thanks -- Sakol "Martin Fishlock" wrote: Hi Sakol: I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = €śSELECT item_number, Item_Description FROM mtl_system_items_b€ť Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
Hi Martin,
Code is cool it's mean pull all data that i unexpect (C1,D1 ..Z..) from Oracle to fill-out in excel sheet1. but i need only A1, and B1 to fill-out description and quantity in the excel sheet1. That mean how can i use vba Find, Seek, or similar etc. and do loop to get all records of A1, and B1 in Oracle. -- Sakol "Martin Fishlock" wrote: OK, Sakol, try this but you may have to play around with it for a bit. You SQL needs to be like this: SQL_Text = €śSELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b€ť & _ " GROUP BY Item_number, Item_Description" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) on error resume next If oraDynaSet.RecordCount 0 Then oraDynaSet.RecordCount for i=1 to oraDynaSet.RecordCount ' not sure if you use ! or . worksheets("sheet1").range("a" & i) = _ oraDynaSet!Item_number worksheets("sheet1").range("b" & i) = _ oraDynaSet!Item_Description worksheets("sheet1").range("c" & i) = _ oraDynaSet!SumQty oraDynaSet.movenext next i ' now do the clean up and close connections. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: In additional if i have 3 fields Item_number, Item_Description, and Quantity (see Example). The object from Oracle contain more than 1 records of Item_number, I need to fill-out Description, sum quantity to excel sheet1 that Item_Number find & match to the Oracle Object. Example:- Oracle Object: Item_Number Item_Description Quantity A1 A1_Description 100 A1 A1_Description 200 B1 B1_Description 300 B1 B1_Description 400 Excel Sheet1: Item_Number Description Quantity A1 ? ? B1 ? ? Thanks -- Sakol "Martin Fishlock" wrote: Hi Sakol: I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = €śSELECT item_number, Item_Description FROM mtl_system_items_b€ť Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
You need to add the "where" clause back in to the SQL - as described in
Matin's earlier post. SQL_Text = "SELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = 'A1' " & _ " GROUP BY Item_number, Item_Description" Tim "Sakol" wrote in message ... Hi Martin, Code is cool it's mean pull all data that i unexpect (C1,D1 ..Z..) from Oracle to fill-out in excel sheet1. but i need only A1, and B1 to fill-out description and quantity in the excel sheet1. That mean how can i use vba Find, Seek, or similar etc. and do loop to get all records of A1, and B1 in Oracle. -- Sakol "Martin Fishlock" wrote: OK, Sakol, try this but you may have to play around with it for a bit. You SQL needs to be like this: SQL_Text = "SELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b" & _ " GROUP BY Item_number, Item_Description" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) on error resume next If oraDynaSet.RecordCount 0 Then oraDynaSet.RecordCount for i=1 to oraDynaSet.RecordCount ' not sure if you use ! or . worksheets("sheet1").range("a" & i) = _ oraDynaSet!Item_number worksheets("sheet1").range("b" & i) = _ oraDynaSet!Item_Description worksheets("sheet1").range("c" & i) = _ oraDynaSet!SumQty oraDynaSet.movenext next i ' now do the clean up and close connections. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: In additional if i have 3 fields Item_number, Item_Description, and Quantity (see Example). The object from Oracle contain more than 1 records of Item_number, I need to fill-out Description, sum quantity to excel sheet1 that Item_Number find & match to the Oracle Object. Example:- Oracle Object: Item_Number Item_Description Quantity A1 A1_Description 100 A1 A1_Description 200 B1 B1_Description 300 B1 B1_Description 400 Excel Sheet1: Item_Number Description Quantity A1 ? ? B1 ? ? Thanks -- Sakol "Martin Fishlock" wrote: Hi Sakol: I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = "SELECT item_number, Item_Description FROM mtl_system_items_b" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
Hi,
It just lost my reply. So use the 'in' statement in your sql as in : SQL_Text = €śSELECT Item_number, Item_Description," & _ " sum(quantity) as SumQty" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number in ('A1', 'B1')" & _ " GROUP BY Item_number, Item_Description" If that doesn't work try: " WHERE Item_number = 'A1' or Item_number = 'B1')" & _ otherwise do the test in the code: lrow=1 for i=1 to oraDynaSet.RecordCount ' not sure if you use ! or . if oraDynaSet!Item_number = "A1" or _ oraDynaSet!Item_number = "B1" then worksheets("sheet1").range("a" & i) = _ oraDynaSet!Item_number worksheets("sheet1").range("b" & i) = _ oraDynaSet!Item_Description worksheets("sheet1").range("c" & i) = _ oraDynaSet!SumQty lrow=lrow+1 end if oraDynaSet.movenext next i -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: Hi Martin, Code is cool it's mean pull all data that i unexpect (C1,D1 ..Z..) from Oracle to fill-out in excel sheet1. but i need only A1, and B1 to fill-out description and quantity in the excel sheet1. That mean how can i use vba Find, Seek, or similar etc. and do loop to get all records of A1, and B1 in Oracle. -- Sakol "Martin Fishlock" wrote: OK, Sakol, try this but you may have to play around with it for a bit. You SQL needs to be like this: SQL_Text = €śSELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b€ť & _ " GROUP BY Item_number, Item_Description" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) on error resume next If oraDynaSet.RecordCount 0 Then oraDynaSet.RecordCount for i=1 to oraDynaSet.RecordCount ' not sure if you use ! or . worksheets("sheet1").range("a" & i) = _ oraDynaSet!Item_number worksheets("sheet1").range("b" & i) = _ oraDynaSet!Item_Description worksheets("sheet1").range("c" & i) = _ oraDynaSet!SumQty oraDynaSet.movenext next i ' now do the clean up and close connections. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: In additional if i have 3 fields Item_number, Item_Description, and Quantity (see Example). The object from Oracle contain more than 1 records of Item_number, I need to fill-out Description, sum quantity to excel sheet1 that Item_Number find & match to the Oracle Object. Example:- Oracle Object: Item_Number Item_Description Quantity A1 A1_Description 100 A1 A1_Description 200 B1 B1_Description 300 B1 B1_Description 400 Excel Sheet1: Item_Number Description Quantity A1 ? ? B1 ? ? Thanks -- Sakol "Martin Fishlock" wrote: Hi Sakol: I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = €śSELECT Item_Description" & _ " FROM mtl_system_items_b€ť & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = €śSELECT item_number, Item_Description FROM mtl_system_items_b€ť Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
Hi,
If the excel sheet1 have item unique more than 10,000 item_numbers, SQL not serve i need. because of spend time to fill-WHERE condition more than 10,000 item_numbers. Could you please help to find the way that program can work like example below. Example :- 1st Loop: Excel sheet1 item_number = "A1" go to find data in Oracle object that item_number = "A1", if found sum quantity all record that contain "A1" in Oracle object and fill out back to Excel Sheet1 at line that contain "A1" movenext to 2nd line 2nd Loop: Excel sheet1 item_number = "1250-9999" 'at 2nd line of excel sheet1 go to find data in Oracle object that item_number = "1250-9999", if found sum quantity all record that contain "1250-9999" in Oracle object and fill out back to Excel Sheet1 at line that contain "1250-9999" movenext to 3rd line --do it until excel sheets end of record Your kind help would be appreciate very much. -- Sakol "Tim Williams" wrote: You need to add the "where" clause back in to the SQL - as described in Matin's earlier post. SQL_Text = "SELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = 'A1' " & _ " GROUP BY Item_number, Item_Description" Tim "Sakol" wrote in message ... Hi Martin, Code is cool it's mean pull all data that i unexpect (C1,D1 ..Z..) from Oracle to fill-out in excel sheet1. but i need only A1, and B1 to fill-out description and quantity in the excel sheet1. That mean how can i use vba Find, Seek, or similar etc. and do loop to get all records of A1, and B1 in Oracle. -- Sakol "Martin Fishlock" wrote: OK, Sakol, try this but you may have to play around with it for a bit. You SQL needs to be like this: SQL_Text = "SELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b" & _ " GROUP BY Item_number, Item_Description" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) on error resume next If oraDynaSet.RecordCount 0 Then oraDynaSet.RecordCount for i=1 to oraDynaSet.RecordCount ' not sure if you use ! or . worksheets("sheet1").range("a" & i) = _ oraDynaSet!Item_number worksheets("sheet1").range("b" & i) = _ oraDynaSet!Item_Description worksheets("sheet1").range("c" & i) = _ oraDynaSet!SumQty oraDynaSet.movenext next i ' now do the clean up and close connections. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: In additional if i have 3 fields Item_number, Item_Description, and Quantity (see Example). The object from Oracle contain more than 1 records of Item_number, I need to fill-out Description, sum quantity to excel sheet1 that Item_Number find & match to the Oracle Object. Example:- Oracle Object: Item_Number Item_Description Quantity A1 A1_Description 100 A1 A1_Description 200 B1 B1_Description 300 B1 B1_Description 400 Excel Sheet1: Item_Number Description Quantity A1 ? ? B1 ? ? Thanks -- Sakol "Martin Fishlock" wrote: Hi Sakol: I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = "SELECT item_number, Item_Description FROM mtl_system_items_b" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
Sakol
You need to make a loop going through the excel worksheet and modify the sql statement run it and insert in in the current row then move down a row. Have a go. If you get stuck post a new question. But have a try. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: Hi, If the excel sheet1 have item unique more than 10,000 item_numbers, SQL not serve i need. because of spend time to fill-WHERE condition more than 10,000 item_numbers. Could you please help to find the way that program can work like example below. Example :- 1st Loop: Excel sheet1 item_number = "A1" go to find data in Oracle object that item_number = "A1", if found sum quantity all record that contain "A1" in Oracle object and fill out back to Excel Sheet1 at line that contain "A1" movenext to 2nd line 2nd Loop: Excel sheet1 item_number = "1250-9999" 'at 2nd line of excel sheet1 go to find data in Oracle object that item_number = "1250-9999", if found sum quantity all record that contain "1250-9999" in Oracle object and fill out back to Excel Sheet1 at line that contain "1250-9999" movenext to 3rd line --do it until excel sheets end of record Your kind help would be appreciate very much. -- Sakol "Tim Williams" wrote: You need to add the "where" clause back in to the SQL - as described in Matin's earlier post. SQL_Text = "SELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = 'A1' " & _ " GROUP BY Item_number, Item_Description" Tim "Sakol" wrote in message ... Hi Martin, Code is cool it's mean pull all data that i unexpect (C1,D1 ..Z..) from Oracle to fill-out in excel sheet1. but i need only A1, and B1 to fill-out description and quantity in the excel sheet1. That mean how can i use vba Find, Seek, or similar etc. and do loop to get all records of A1, and B1 in Oracle. -- Sakol "Martin Fishlock" wrote: OK, Sakol, try this but you may have to play around with it for a bit. You SQL needs to be like this: SQL_Text = "SELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b" & _ " GROUP BY Item_number, Item_Description" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) on error resume next If oraDynaSet.RecordCount 0 Then oraDynaSet.RecordCount for i=1 to oraDynaSet.RecordCount ' not sure if you use ! or . worksheets("sheet1").range("a" & i) = _ oraDynaSet!Item_number worksheets("sheet1").range("b" & i) = _ oraDynaSet!Item_Description worksheets("sheet1").range("c" & i) = _ oraDynaSet!SumQty oraDynaSet.movenext next i ' now do the clean up and close connections. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: In additional if i have 3 fields Item_number, Item_Description, and Quantity (see Example). The object from Oracle contain more than 1 records of Item_number, I need to fill-out Description, sum quantity to excel sheet1 that Item_Number find & match to the Oracle Object. Example:- Oracle Object: Item_Number Item_Description Quantity A1 A1_Description 100 A1 A1_Description 200 B1 B1_Description 300 B1 B1_Description 400 Excel Sheet1: Item_Number Description Quantity A1 ? ? B1 ? ? Thanks -- Sakol "Martin Fishlock" wrote: Hi Sakol: I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = "SELECT item_number, Item_Description FROM mtl_system_items_b" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in Oracle
Hello All.
I am new to VBA/Oracle and I've been task to write an application that will extract data from Oracle and then bring them into Excel which I guess similar to the problem of Sakol. I manage to extract the data from Oracle and saved them into Excel but I need to limit the output using a worksheet within the excel file. Can you please help me how can I use the worksheet called "Data" as part of the query and then write them to a different worksheet. Below are my codes which I found in the net. Sub OracleExcel() Dim Database_Name As String Dim User_ID As String Dim Password As String Dim SQLStr As String Dim OraDynaSet As Object Dim objSession As Object Dim objDataBase As Object Dim i As Integer 'Create a reference to my database Database_Name = "i021" ' Enter your database name here User_ID = "ops$access" ' enter your user ID here Password = "access" ' Enter your password here 'Create a reference to the OO4O dll Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDataBase = objSession.OpenDatabase(Database_Name, User_ID & "/" & Password, 0&) SQLStr = "SELECT BH_SUB_PROJ, BH_FAST_ACCESS, BH_DL_CD, BH_DOC_NO, BH_SHT_NO, BH_DOC_REV_NO FROM BOM_HDR" objDataBase.ExecuteSQL (SQLStr) 'Retrieve the results from Oracle Set OraDynaSet = objDataBase.DBCreateDynaset(SQLStr, 0&) If OraDynaSet.RecordCount 0 Then 'There were records retrieved OraDynaSet.MoveFirst 'Loop the recordset for returned rows For i = 2 To OraDynaSet.RecordCount 'Put the results in column A, B, D, E and F Sheets("Sample Data").Cells(i, 1) = OraDynaSet.Fields(0).Value Sheets("Sample Data").Cells(i, 2) = OraDynaSet.Fields(1).Value Sheets("Sample Data").Cells(i, 4) = OraDynaSet.Fields(3).Value Sheets("Sample Data").Cells(i, 5) = OraDynaSet.Fields(4).Value Sheets("Sample Data").Cells(i, 6) = OraDynaSet.Fields(5).Value 'End If Next i End If Set OraDynaSet = Nothing Set objSession = Nothing objDataBase.Close Set objDataBase = Nothing End Sub I have the following from worksheet data as an example. WBS Priority P_Date M_Grp Addr. MTO_LVL CA01 880 20090102 11 D01 I7 CA01 880 20090102 14 D01 I7 Need to filled worksheet Sample Data both from Oracle table and Worksheet Data. Sub Proj BH_FA WBS DWG_NO SHT Rev Priority P_Date M_Grp Addr. MTO_LVL Fields WBS, Priority, P_Date, M_Grp, Addr., MTO_LVL will come from Worksheet Data the rest from Oracle. Oracle field must by equal to Worksheet table field. [Data$]WBS = [BOM_HDR]BH_DL_CD [Data$]Addr. = [BOM_HDR]BH_ADDR_CD [Data$]MTO_LVL = [BOM_HDR]BH_MTO_LVL_CD Hope you guys can help me. Regards, John "Martin Fishlock" wrote: Sakol You need to make a loop going through the excel worksheet and modify the sql statement run it and insert in in the current row then move down a row. Have a go. If you get stuck post a new question. But have a try. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: Hi, If the excel sheet1 have item unique more than 10,000 item_numbers, SQL not serve i need. because of spend time to fill-WHERE condition more than 10,000 item_numbers. Could you please help to find the way that program can work like example below. Example :- 1st Loop: Excel sheet1 item_number = "A1" go to find data in Oracle object that item_number = "A1", if found sum quantity all record that contain "A1" in Oracle object and fill out back to Excel Sheet1 at line that contain "A1" movenext to 2nd line 2nd Loop: Excel sheet1 item_number = "1250-9999" 'at 2nd line of excel sheet1 go to find data in Oracle object that item_number = "1250-9999", if found sum quantity all record that contain "1250-9999" in Oracle object and fill out back to Excel Sheet1 at line that contain "1250-9999" movenext to 3rd line --do it until excel sheets end of record Your kind help would be appreciate very much. -- Sakol "Tim Williams" wrote: You need to add the "where" clause back in to the SQL - as described in Matin's earlier post. SQL_Text = "SELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = 'A1' " & _ " GROUP BY Item_number, Item_Description" Tim "Sakol" wrote in message ... Hi Martin, Code is cool it's mean pull all data that i unexpect (C1,D1 ..Z..) from Oracle to fill-out in excel sheet1. but i need only A1, and B1 to fill-out description and quantity in the excel sheet1. That mean how can i use vba Find, Seek, or similar etc. and do loop to get all records of A1, and B1 in Oracle. -- Sakol "Martin Fishlock" wrote: OK, Sakol, try this but you may have to play around with it for a bit. You SQL needs to be like this: SQL_Text = "SELECT Item_number, Item_Description, sum(quantity) as SumQty" & _ " FROM mtl_system_items_b" & _ " GROUP BY Item_number, Item_Description" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) on error resume next If oraDynaSet.RecordCount 0 Then oraDynaSet.RecordCount for i=1 to oraDynaSet.RecordCount ' not sure if you use ! or . worksheets("sheet1").range("a" & i) = _ oraDynaSet!Item_number worksheets("sheet1").range("b" & i) = _ oraDynaSet!Item_Description worksheets("sheet1").range("c" & i) = _ oraDynaSet!SumQty oraDynaSet.movenext next i ' now do the clean up and close connections. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: In additional if i have 3 fields Item_number, Item_Description, and Quantity (see Example). The object from Oracle contain more than 1 records of Item_number, I need to fill-out Description, sum quantity to excel sheet1 that Item_Number find & match to the Oracle Object. Example:- Oracle Object: Item_Number Item_Description Quantity A1 A1_Description 100 A1 A1_Description 200 B1 B1_Description 300 B1 B1_Description 400 Excel Sheet1: Item_Number Description Quantity A1 ? ? B1 ? ? Thanks -- Sakol "Martin Fishlock" wrote: Hi Sakol: I have not used the oracle model but have used the SQL. What you want from the query is the Item_descrption where it matches a certain Item_number so try this if item_number is a number: SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = " & 123 or if item_number is a string SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = '123'" You can use a variable if you have one defined as in sItemNo SQL_Text = "SELECT Item_Description" & _ " FROM mtl_system_items_b" & _ " WHERE Item_number = '" & sItemNo & "'" You should then beable to actually get the data fro mthe record without copying it to the clipboard but step through the code and have a look at the object and the data in the vba editor. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Sakol" wrote: How can i find field "Item_Number" in Oracle Object if found need to get Item_Description to fill-out another sheet. sub FindOCL() SQL_Text = "SELECT item_number, Item_Description FROM mtl_system_items_b" Set objSession = CreateObject("OracleInProcServer.XOraSession") Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0) Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0) If oraDynaSet.RecordCount 0 Then oraDynaSet.CopyToClipboard -1 oraDynaSet.MoveFirst End If End Sub -- Thanks Sakol |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL for Oracle | Excel Discussion (Misc queries) | |||
SQL for Oracle | Excel Discussion (Misc queries) | |||
Connect to Oracle using Microsoft ODBC for Oracle | Excel Programming | |||
don't find my message : connect to oracle db (4.2. 19:39) | Excel Programming | |||
Excel cannot 'FIND' data imported from oracle | Excel Programming |