Possibly a challenge or maybe a bore!

  • Hi all,

    I currently use a data loader from excel that i created, to load values into oracle.

    I was thinking wouldnt this be cool if i could do it through the web i.e i would not have to always have workbook with me i could just go my website and click a button and hey presto watch it load.

    The current loader has a set of parameters and carries these out i.e to tab i write tab, to go the menu i have coded it *AV etc etc.

    Is anyone Asp.html literate to know how to do this or fancy a challenge?

    If anyone is interested i will send them my current version as it is to large to put it on the forum

  • Steve - I am asp / asp.net / html etc literate (as is XL-Dennis)... but I am not quite sure I follow how you're doing this...

    Where are you getting the data from that you then load to Oracle (ADO I presume as per previous discussions?)

    You can use ADO in ASP or ADO.Net in ASP.Net.

    Feel free to mail me what you have... also suggest you pop up a more detailed explanation of exactly what you're doing currently and what you would like your asp page to do.

    WillR etc.. suggest moving this thread to SQL development forum?

  • hi lasw10,

    If you can send me your email address i can you the file.

    Basically at present in excel i enter certain commands and then click a macro button which then upload it front end to oracle.

    Basically you can watch it load rather than straight to the database this way.

    The code is below but send me you email address and i can send it to you.
    Also do you want a couple gmail accounts?

    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.


    '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 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.
    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
    Case "*UP" 'Up Arrow
    SendKeys "{UP}", True
    Case "*DN" 'Down Arrow
    SendKeys "{DOWN}", True
    Case "*LT" 'Left Arrow
    SendKeys "{LEFT}", True
    Case "*RT" 'Right Arrow
    SendKeys "{RIGHT}", True
    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}")
    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
    Case "*PF" ' Previous Field
    SendExtKey "SHF", "{TAB}"
    Case "*NR" ' Next Record
    SendKeys "{DOWN}", True
    Case "*PR" ' Previous Record
    SendKeys "{UP}", True
    Case "*FR" ' First Record
    SendExtKey "ALT", "G"
    Sleep 250
    SendKeys ("{DOWN 5}{ENTER}")
    Case "*LR" ' Last Record
    SendExtKey "ALT", "G"
    Sleep 250
    SendKeys ("{DOWN 6}{ENTER}")
    Case "*ER" ' Erase Record
    SendKeys "{F6}", True
    Case "*DR" ' Delete Record
    SendExtKey "CTL", "{UP}"
    Case "*SB" ' Space
    SendKeys " ", True
    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
    SendKeys C.Value, True ' Text to be inserted
    End If
    End Select
    Sleep 200

    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.
    '-- **************************************************************************

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!