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

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

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

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

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

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

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