Cut and Paste Excel Macro (NCA Version)

Declare Sub keybd_event Lib "user32" (ByVal bVk As Integer, ByVal bScan As Integer, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Declare Function MapVirtualKey Lib "user32" Alias "MapVirtualKeyA" (ByVal wCode As Long, ByVal wMapType As Long) As Long

'Declare keyboard constant.

Const KEYEVENTF_KEYUP = &H2
 

'Call this subroutine to start copying keystrokes from Excel to NCA.
'This sub activates the NCA form to be loaded and sends appropriate
'keystokes from those selected in the Excel worksheet.

Sub Send_rows()

    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

'-- **************************************************************************
'-- End of SendExtKey sub.
'-- **************************************************************************

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.