sending data from oracle form to other application

we have one application "oracle database"  , usually , when we have alot of data in details
we post the data in excell sheet , then we have macro to send the rows to the form of the application

i also designed one code in MS ACCESS , to do the same , and here is the code
Private Sub Command30_Click()
On Error GoTo Err_Com

Dim SE, Re As DAO.Recordset
Dim K As Integer
Dim Ga As String

AppActivate "General Ledger"
'DoCmd.SetWarnings False

'Ga = "SELECT AccrudeEntry.* INTO ForApha FROM AccrudeEntry;"
'DoCmd.RunSQL Ga

DoCmd.SetWarnings True

Set SE = CurrentDb.OpenRecordset("UploadAcrAlpha")

If SE.BOF = True Then
MsgBox "There Is No Operational Accounts For Bills This Month"
Exit Sub
End If

SE.MoveFirst
Do Until SE.EOF

DoEvents
SendKeys Chr(9)
SendKeys SE![AccountNo]
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys SE![AlphaCode]
SendKeys Chr(9)
SendKeys Round(SE![Amount], 3)
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys "Accrude Amount"
SendKeys Chr(9)
SendKeys "{ENTER}", True
DoEvents
SE.MoveNext
Loop
SE.Close

Exit Sub
Err_Com:
    
    MsgBox Err.Description
    
End Sub

Open in new window


the code above sending data from recordset in required field , and sending key SHIFT , or TAB when required

now i want to do the same from one oracle form , i want to send data to the application
in oracle we have cursor instead of recordset in MS ACESS
same scenario , it should loop  a cursor , but i don't know  2 things :
1- how to get the open application name ?  in ms access it is
AppActivate "General Ledger"
2 - how to use sending key function , like this in ms access

DoEvents
SendKeys Chr(9)
SendKeys SE![AccountNo]
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys SE![AlphaCode]
SendKeys Chr(9)
SendKeys Round(SE![Amount], 3)
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys "Accrude Amount"
SendKeys Chr(9)
SendKeys "{ENTER}", True
DoEvents
NiceMan331Asked:
Who is Participating?
 
flow01Commented:
Since your are running local.
You could try building a a script using text_io and execute it with the forms host command


step1 test the execution of a script by hostcommand
create a testscript in your editor "C:\Users\username\Documents\expert_exchange\test.vbs"
change username to your own username (multiple times)

Set FSO = CreateObject("Scripting.FileSystemObject")
Set File = FSO.OpenTextFile("C:\Users\username\Documents\expert_exchange\" &"\test.txt", 2, True)
File.Write "testing"
File.Close
and execute (for example in a when button  pressed) using the host-build in
host('cscript C:\Users\username\Documents\expert_exchange\test.vbs');

If you succeeded in creating the file test,txt with content testing step 1 is done.

step 2 test if you can reach "General Ledger" by editing a GL.vbs file:
something like
AppActivate "General Ledger"
DoEvents
SendKeys Chr(9)
SendKeys 123
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys aaa
SendKeys Chr(9)
SendKeys 4000
SendKeys Chr(9)
SendKeys Chr(9)
SendKeys "Accrude Amount"
SendKeys Chr(9)
SendKeys "{ENTER}", True
DoEvents

If it does what you want

step3
in your triggger use build text_io build-ins to create  a vbs-scripts with a uniq-name (for example using session_id and timestamp)  based on the results of the query : hardcoding the sendkey values  and execute the script

step4
call your hostcommand with the no_screen option
and execute another one to erase the vbs-script

I did never use it this way but this would be my approach. (i did use text_io and the host-command)
0
 
NiceMan331Author Commented:
thanx for your post , but i'm sorry , as a begginer in oracle , let me do it step by step

create a testscript in your editor "C:\Users\username\Documents\expert_exchange\test.vbs"

i searched a lot on google to find how to create that script , i found a large amount of information about it , it is mixed now for my small knowledge
how to create that script ?
0
 
flow01Commented:
open notepad, copy the text

Set FSO = CreateObject("Scripting.FileSystemObject")
Set File = FSO.OpenTextFile("C:\Users\username\Documents\expert_exchange\" &"\test.txt", 2, True)
File.Write "testing"
File.Close

change the path in FSO.OpenTextFile to an existing directory on your system
save as test,vbs i a directory you want

open that directory in windows explorer ,  double-click test.vbs
and check wether test.txt is written with 1 line 'testing'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NiceMan331Author Commented:
ok , done
testing was written already to test.txt
now i have to create gl.vbs  and put the same codes i wrote in ms access ?
0
 
NiceMan331Author Commented:
i created GL.VBS , i put those code inside

AppActivate "General Ledger"
DoEvents
SendKeys 15121

but nothing happened
0
 
flow01Commented:
Sorry, VB-scripting is also unknown terrain for me.
Ik was mixing vba and vbs-scripting

You wil need modifications to translate vba to vbs
Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate("General Ledger")
' Give General Ledger time to activate
WScript.Sleep 500
objShell.SendKeys 15121


You did have  your "General Ledger"-application already open ?

Since I don't have your general ledger a tested simular action  with notepad

I used (not starting it from forms though)

notepad.vbs  to start notepad
and notepadactivate.vbs
pay attention to the objShell.AppActivate("Kladblok") : 'Kladblok' is the dutch name of notepad : you will probably need the 'Notepad'  name. You can use windows taskmanager or the title of your ledger-app to find the name you need

notepad.vbs

Set objShell = WScript.CreateObject("WScript.Shell")
objShell.Run ("%windir%\notepad")
' Give Notepad time to load
WScript.Sleep 500
objShell.SendKeys "Hello World!"

notepadactivate.vbs
Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate("Kladblok")
' Give Notepad time to activate
WScript.Sleep 500
objShell.SendKeys "Activated"
0
 
NiceMan331Author Commented:
step 2 done
the gl.vbs script successfully reached my app , and filled by the sanded keys values
now could u please explain your step 3
step3
in your triggger use build text_io build-ins to create  a vbs-scripts with a uniq-name (for example using session_id and timestamp)  based on the results of the query : hardcoding the sendkey values  and execute the script
for how do i fill the values  from a table ( using cursor for example ) to be written in the notepad (followed send keys )
0
 
flow01Commented:
should be something like

DECLARE
OUTFILE TEXT_IO.FILE_TYPE;
filename  varchar2(100);

BEGIN

SELECT 'c:yourpath\' || 'GL' || sys_context('USERENV', 'SESSIONID') || '_' || to_char(sysdate,'yyyymmddhh24miss') || .vbs' into filename FROM DUAL;

OUTFILE := TEXT_IO.FOPEN(filename,'W');

TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || 'Set objShell = CreateObject("WScript.Shell")');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || 'objShell.AppActivate("General Ledger")');

TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || ''' Give Notepad time to load');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || 'WScript.Sleep 500');

FOR SE IN (select * from UploadAcrAlpha) LOOP

TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"' || SE.AccountNo || '"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"' || SE.AlphaCode || '"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"' || Round(SE.Amount, 3) || '"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"Accrude Amount"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{ENTER}"';  -- i don't know what the , True means in VBA

END LOOP;

TEXT_IO.FCLOSE(OUTFILE);
END;
0
 
NiceMan331Author Commented:
after some adjustment to the top of the code

TEXT_IO.PUT_LINE (OUTFILE,'Set objShell = CreateObject("WScript.Shell")');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.AppActivate("General Ledger")');

TEXT_IO.PUT_LINE (OUTFILE, ' Give General Ledger time to activate');
TEXT_IO.PUT_LINE (OUTFILE,'WScript.Sleep 500');

Open in new window


TEXT_IO.PUT_LINE (OUTFILE, ' Give General Ledger time to activate');
not yet adjusted , because its output should be
 ' Give General Ledger time to activate
i think it need additional '
anyhow , the output of the code is

Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate("General Ledger")
' Give General Ledger time to activate
WScript.Sleep 500
objShell.SendKeys "{TAB}"
objShell.SendKeys 43513
objShell.SendKeys "{TAB}"
objShell.SendKeys "{TAB}"
objShell.SendKeys 3010
objShell.SendKeys "{TAB}"
objShell.SendKeys 4000
objShell.SendKeys "{TAB}"
objShell.SendKeys Accrude Amount
objShell.SendKeys "{TAB}"
objShell.SendKeys "{TAB}"

i try it many times , it not send any thing to the application
while , i manually adjust the old gl.vbs , rename it to be gl1.vbs , its out put like this

Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate("General Ledger")
' Give General Ledger time to activate
WScript.Sleep 500
objShell.SendKeys "{TAB}"
objShell.SendKeys 43513
objShell.SendKeys "{TAB}"
objShell.SendKeys "{TAB}"
objShell.SendKeys 3010
objShell.SendKeys "{TAB}"
objShell.SendKeys 4000
objShell.SendKeys "{TAB}"
objShell.SendKeys "Accrude"
objShell.SendKeys "{TAB}"
objShell.SendKeys "{TAB}"

this works perfectly , i spent alot of times to check what is the difference between 2 files
i didn;t see any difference
in conlicdion , gl.vbs not works
gl1.vbs works
0
 
flow01Commented:
The  difference is in  ''  surrounding the accrude text .
But I would have expected that
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"Accrude Amount"');
would have made that line
single quote,  double quote ,Accrude Amount, double quote , single_quote
0
 
NiceMan331Author Commented:
great
it is wonderful now , i used also host in the command , it works also fine
finally , i have 3 points

1-is this line necessary :
TEXT_IO.PUT_LINE (OUTFILE, 'Give General Ledger time to activate');

i removed it , and works

2- look at this
FOR SE IN ( SELECT * FROM GEN_LEDGERS WHERE DOC_DATE = '20-03-2014' and tran_no = 1521) LOOP
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.acc_no || '');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || nvl(SE.cost_center,'"{TAB}"') || '');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.l_debit || '');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.l_credit || '');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"' || SE.DESCRIBT || '"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
END LOOP;

here as a program built , each record should have one value only , eaighter debit or credit
and the typing will be like this
of user type field debit , the cursor automatically skip the next field credit to the next
describt , like this
debit , TAB , descript
shall we use decode in the statement , or i will use the loop 2 times
1 time for debit records , then for credit reports ,
3- could we use same scanrio to write data to excell sheet ?
0
 
flow01Commented:
1.  Yes you can leave it out : in VBS-script the ' starts a comment line

2. You did not mention the wanted behaviour if it;s a credit field:
?
TAB, credit, descript
?

And i don't know if the debit field contains 0 or null if  there is a credit field
decide for yourself and adjust if necessary
IF SE.l_debit IS NOT NULL THEN
  TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.l_debit || '');
  TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
ELSE
  TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '"{TAB}"');
  TEXT_IO.PUT_LINE (OUTFILE,'objShell.SendKeys ' || '' || SE.l_credit || '');
END IF;

so don't loop twice

 
3.  Yes , you can. But:
     Try opening an excel-sheet in notepad or wordpad:  
     that's the way your text-file should look alike to create a "real" excel-sheet.
     If you look carefully you can see the structure and make a coding effort to create it.
     
     More easy, but less friendly is to create a comma seperated file.
     You can open it in excel, but in my experience you have to inform excel in what way the data are stored.

You can try it your self
create a file   toexcel.txt
"A",1,"B",,4
"D",3,"",5,4

start excel , open toexcel.txt  and answer the questions in the wizard
However, depending on the way you want to use it you will meet more to solve: what is your date format,  what your decimal sign, what language do you use (and even more : are that setting on the system and excell the same for all the user of your file),
0
 
NiceMan331Author Commented:
sorry for delay , but i lost the internet connection in my office
i will do same code of above to write in the txt file
0
 
NiceMan331Author Commented:
yes , i tried it , it is ok , it sent to text file , then i opened it in excell
thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.