stevehorton
09-17-2004, 02:36 AM
hi guys and gals,
Im a vb guy at heart but have chosen to take on the world wide web.
I currently use macros to data load to oracle and was wondering if this was possible from using asp or html.
My question really is, is that possible and if so how do i go about finding the coding.
I have the code below, what it does at present is follow instructions that I have programmed through vb and then follow these in an oracle form.
I.e Tab and then enter 123456 would be TAB in a1 and b1 123456 etc(little more complex that!)
The code is as follows hope someone can point me in the right direction
Sub LocalUse_Button7_Click()
Message = "Enter the name of the NCA Form to be driven." + Chr(10) + "Ensure this form is loaded and isn't minimised."
Title = "Excel to Oracle Export"
Form = InputBox(Message, Title)
If Form = "" Then 'Cancel pressed or no input entered.
End
Else
AppActivate Form ' Activate the specified Oracle Applications form.
DoEvents ' Passes control to operating system until all events in queue have been processed
Sleep 250
End If
For Each C In Selection ' For all cells selected
Select Case C.Value ' First 3 characters are the command, to allow for parameters.
Case "TAB" 'Tab
SendKeys "{TAB}", True ' Send to Oracle Applications, wait for processing
DoEvents ' Passes control to operating system
Case "ENT" 'Enter
SendKeys "{ENTER}", True
DoEvents
Case "*UP" 'Up Arrow
SendKeys "{UP}", True
DoEvents
Case "*DN" 'Down Arrow
SendKeys "{DOWN}", True
DoEvents
Case "*LT" 'Left Arrow
SendKeys "{LEFT}", True
DoEvents
Case "*RT" 'Right Arrow
SendKeys "{RIGHT}", True
DoEvents
Case "*FE" 'Field Editor
SendExtKey "CTL", "E"
Case "*SP" 'Save & Proceed.
SendExtKey "ALT", "A"
Sleep 250 'Pause while menu is drawn.
SendKeys ("{DOWN 4}{ENTER}")
DoEvents
Sleep 200
Case "*SAVE" 'Save
SendExtKey "CTL", "S"
Sleep 200
Case "*NB" ' Next Block
SendExtKey "SHF", "{PGDN}"
Case "*PB" ' Previous Block
SendExtKey "SHF", "{PGUP}"
Case "*NF" ' Next Field
SendKeys "{TAB}", True
DoEvents
Case "*PF" ' Previous Field
SendExtKey "SHF", "{TAB}"
Case "*NR" ' Next Record
SendKeys "{DOWN}", True
DoEvents
Case "*PR" ' Previous Record
SendKeys "{UP}", True
DoEvents
Case "*FR" ' First Record
SendExtKey "ALT", "G"
Sleep 250
SendKeys ("{DOWN 5}{ENTER}")
DoEvents
Case "*LR" ' Last Record
SendExtKey "ALT", "G"
DoEvents
Sleep 250
SendKeys ("{DOWN 6}{ENTER}")
DoEvents
Case "*ER" ' Erase Record
SendKeys "{F6}", True
DoEvents
Case "*DR" ' Delete Record
SendExtKey "CTL", "{UP}"
Case "*SB" ' Space
SendKeys " ", True
DoEvents
Case "*ST" 'Select text.
SendExtKey "SHF", "{END}"
Sleep 250
Case "*AA" ' Alt + A
SendExtKey "ALT", "A"
Case "*AB" ' Alt + B
SendExtKey "ALT", "B"
Case "*AC" ' Alt + C
SendExtKey "ALT", "C"
Case "*AD" ' Alt + D
SendExtKey "ALT", "D"
Case "*AE" ' Alt + E
SendExtKey "ALT", "E"
Case "*AF" ' Alt + F
SendExtKey "ALT", "F"
Case "*AG" ' Alt + G
SendExtKey "ALT", "G"
Case "*AH" ' Alt + H
SendExtKey "ALT", "H"
Case "*AI" ' Alt + I
SendExtKey "ALT", "I"
Case "*AJ" ' Alt + J
SendExtKey "ALT", "J"
Case "*AK" ' Alt + K
SendExtKey "ALT", "K"
Case "*AL" ' Alt + L
SendExtKey "ALT", "L"
Case "*AM" ' Alt + M
SendExtKey "ALT", "M"
Case "*AN" ' Alt + N
SendExtKey "ALT", "N"
Case "*AO" ' Alt + O
SendExtKey "ALT", "O"
Case "*AP" ' Alt + P
SendExtKey "ALT", "P"
Case "*AQ" ' Alt + Q
SendExtKey "ALT", "Q"
Case "*AR" ' Alt + R
SendExtKey "ALT", "R"
Case "*AS" ' Alt + S
SendExtKey "ALT", "S"
Case "*AT" ' Alt + T
SendExtKey "ALT", "T"
Case "*AU" ' Alt + U
SendExtKey "ALT", "U"
Case "*AV" ' Alt + V
SendExtKey "ALT", "V"
Case "*AW" ' Alt + W
SendExtKey "ALT", "W"
Case "*AX" ' Alt + X
SendExtKey "ALT", "X"
Case "*AY" ' Alt + Y
SendExtKey "ALT", "Y"
Case "*AZ" ' Alt + Z
SendExtKey "ALT", "Z"
Case Else
If Left(C.Value, 3) = "*SL" Then ' Sleep for a given number of seconds.
Sleep CInt(Right(C.Value, Len(C.Value) - 3)) * 1000
Else
SendKeys C.Value, True ' Text to be inserted
DoEvents
End If
End Select
Sleep 200
Next
End Sub
'-- **************************************************************************
'-- End of send_rows sub.
'-- **************************************************************************
'This subroutine sends keystrokes to NCA while a specified system key is depresed.
'The sendkeys function fails to send the special keys to NCA hence this sub is required.
'Similarly, this sub appears to not work with standard Windows programs.
'This subroutine takes two string parameters.
'The first parameter defines the system key to be depressed - ALT|CTL|SHF.
'The second parameter contains the string to be sent while the system key is depressed.
'The string keys are sent to the active application using the SendKeys command.
'All keys supported by sendkeys are supported in this sub.
Sub SendExtKey(ByVal extkey As String, ByVal letter As String)
Dim extscan%
Select Case extkey 'Determine what system key to use & set value accordingly.
Case "ALT"
VK_EXT = &H12
Case "CTL"
VK_EXT = &H11
Case "SHF"
VK_EXT = &H10
End Select
extscan% = MapVirtualKey(VK_EXT, 0) 'Get the key's hardware scan code.
keybd_event VK_EXT, extscan, 0, 0 'Depress the system key required.
Sleep 50
SendKeys letter, True 'Send keystrokes.
keybd_event VK_EXT, extscan, KEYEVENTF_KEYUP, 0 'Release system key.
End Sub
Im a vb guy at heart but have chosen to take on the world wide web.
I currently use macros to data load to oracle and was wondering if this was possible from using asp or html.
My question really is, is that possible and if so how do i go about finding the coding.
I have the code below, what it does at present is follow instructions that I have programmed through vb and then follow these in an oracle form.
I.e Tab and then enter 123456 would be TAB in a1 and b1 123456 etc(little more complex that!)
The code is as follows hope someone can point me in the right direction
Sub LocalUse_Button7_Click()
Message = "Enter the name of the NCA Form to be driven." + Chr(10) + "Ensure this form is loaded and isn't minimised."
Title = "Excel to Oracle Export"
Form = InputBox(Message, Title)
If Form = "" Then 'Cancel pressed or no input entered.
End
Else
AppActivate Form ' Activate the specified Oracle Applications form.
DoEvents ' Passes control to operating system until all events in queue have been processed
Sleep 250
End If
For Each C In Selection ' For all cells selected
Select Case C.Value ' First 3 characters are the command, to allow for parameters.
Case "TAB" 'Tab
SendKeys "{TAB}", True ' Send to Oracle Applications, wait for processing
DoEvents ' Passes control to operating system
Case "ENT" 'Enter
SendKeys "{ENTER}", True
DoEvents
Case "*UP" 'Up Arrow
SendKeys "{UP}", True
DoEvents
Case "*DN" 'Down Arrow
SendKeys "{DOWN}", True
DoEvents
Case "*LT" 'Left Arrow
SendKeys "{LEFT}", True
DoEvents
Case "*RT" 'Right Arrow
SendKeys "{RIGHT}", True
DoEvents
Case "*FE" 'Field Editor
SendExtKey "CTL", "E"
Case "*SP" 'Save & Proceed.
SendExtKey "ALT", "A"
Sleep 250 'Pause while menu is drawn.
SendKeys ("{DOWN 4}{ENTER}")
DoEvents
Sleep 200
Case "*SAVE" 'Save
SendExtKey "CTL", "S"
Sleep 200
Case "*NB" ' Next Block
SendExtKey "SHF", "{PGDN}"
Case "*PB" ' Previous Block
SendExtKey "SHF", "{PGUP}"
Case "*NF" ' Next Field
SendKeys "{TAB}", True
DoEvents
Case "*PF" ' Previous Field
SendExtKey "SHF", "{TAB}"
Case "*NR" ' Next Record
SendKeys "{DOWN}", True
DoEvents
Case "*PR" ' Previous Record
SendKeys "{UP}", True
DoEvents
Case "*FR" ' First Record
SendExtKey "ALT", "G"
Sleep 250
SendKeys ("{DOWN 5}{ENTER}")
DoEvents
Case "*LR" ' Last Record
SendExtKey "ALT", "G"
DoEvents
Sleep 250
SendKeys ("{DOWN 6}{ENTER}")
DoEvents
Case "*ER" ' Erase Record
SendKeys "{F6}", True
DoEvents
Case "*DR" ' Delete Record
SendExtKey "CTL", "{UP}"
Case "*SB" ' Space
SendKeys " ", True
DoEvents
Case "*ST" 'Select text.
SendExtKey "SHF", "{END}"
Sleep 250
Case "*AA" ' Alt + A
SendExtKey "ALT", "A"
Case "*AB" ' Alt + B
SendExtKey "ALT", "B"
Case "*AC" ' Alt + C
SendExtKey "ALT", "C"
Case "*AD" ' Alt + D
SendExtKey "ALT", "D"
Case "*AE" ' Alt + E
SendExtKey "ALT", "E"
Case "*AF" ' Alt + F
SendExtKey "ALT", "F"
Case "*AG" ' Alt + G
SendExtKey "ALT", "G"
Case "*AH" ' Alt + H
SendExtKey "ALT", "H"
Case "*AI" ' Alt + I
SendExtKey "ALT", "I"
Case "*AJ" ' Alt + J
SendExtKey "ALT", "J"
Case "*AK" ' Alt + K
SendExtKey "ALT", "K"
Case "*AL" ' Alt + L
SendExtKey "ALT", "L"
Case "*AM" ' Alt + M
SendExtKey "ALT", "M"
Case "*AN" ' Alt + N
SendExtKey "ALT", "N"
Case "*AO" ' Alt + O
SendExtKey "ALT", "O"
Case "*AP" ' Alt + P
SendExtKey "ALT", "P"
Case "*AQ" ' Alt + Q
SendExtKey "ALT", "Q"
Case "*AR" ' Alt + R
SendExtKey "ALT", "R"
Case "*AS" ' Alt + S
SendExtKey "ALT", "S"
Case "*AT" ' Alt + T
SendExtKey "ALT", "T"
Case "*AU" ' Alt + U
SendExtKey "ALT", "U"
Case "*AV" ' Alt + V
SendExtKey "ALT", "V"
Case "*AW" ' Alt + W
SendExtKey "ALT", "W"
Case "*AX" ' Alt + X
SendExtKey "ALT", "X"
Case "*AY" ' Alt + Y
SendExtKey "ALT", "Y"
Case "*AZ" ' Alt + Z
SendExtKey "ALT", "Z"
Case Else
If Left(C.Value, 3) = "*SL" Then ' Sleep for a given number of seconds.
Sleep CInt(Right(C.Value, Len(C.Value) - 3)) * 1000
Else
SendKeys C.Value, True ' Text to be inserted
DoEvents
End If
End Select
Sleep 200
Next
End Sub
'-- **************************************************************************
'-- End of send_rows sub.
'-- **************************************************************************
'This subroutine sends keystrokes to NCA while a specified system key is depresed.
'The sendkeys function fails to send the special keys to NCA hence this sub is required.
'Similarly, this sub appears to not work with standard Windows programs.
'This subroutine takes two string parameters.
'The first parameter defines the system key to be depressed - ALT|CTL|SHF.
'The second parameter contains the string to be sent while the system key is depressed.
'The string keys are sent to the active application using the SendKeys command.
'All keys supported by sendkeys are supported in this sub.
Sub SendExtKey(ByVal extkey As String, ByVal letter As String)
Dim extscan%
Select Case extkey 'Determine what system key to use & set value accordingly.
Case "ALT"
VK_EXT = &H12
Case "CTL"
VK_EXT = &H11
Case "SHF"
VK_EXT = &H10
End Select
extscan% = MapVirtualKey(VK_EXT, 0) 'Get the key's hardware scan code.
keybd_event VK_EXT, extscan, 0, 0 'Depress the system key required.
Sleep 50
SendKeys letter, True 'Send keystrokes.
keybd_event VK_EXT, extscan, KEYEVENTF_KEYUP, 0 'Release system key.
End Sub