Posts by stevehorton09

  • VB script to copy values to word

    Re: VB script to copy values to word


    This is what Im having a go with, but it is not copying the cell.
    So this now copies the cell and opens a new word doc but does not copy it!


    Any ideas?


    Are attach the updated spreadsheet




    [code]
    Option Explicit

    Sub Excel_to_Word()
    Dim appWord As Word.Application

    Set appWord = New Word.Application

    appWord.Visible = True

    Range("A1:A20").Copy

    appWord.Documents.Add.Content.Paste

    End Sub [code]

  • Exchange rate fluctuations

    Re: Exchange rate fluctuations


    Unfortunately not,


    The figures are correlated in that they the are same figures just the usd sheet is in dollars. Therefore they have been changed from euros to dollars using a different rate for each year ('05 1.28 and '06 1.18).


    I.e. on the original currency sheet the attachment is a 100 for each. When the rate of exchange is applied on usd.xls the following happends '05 =128 and '06 =118. This causes the attachments factor to change and hence the large jump in percentage.



    This applies to other figures and is a concern when the factor changes as this will have an effect on th epercentage that is found.


    In instances where the factor changes and the percentage is increased greatly we have to do this original currency spreadsheet, find out the percentage and then doc the usd.xls in the subjectivity table.


    By doing this we are using trial and error and as you can see the 749 works but is plucked without logic (except t&e).


    Therefore the question is how can I work out this 749 other than trial an error.


    I think that should answer your question :)

  • Exchange rate fluctuations

    Re: Exchange rate fluctuations


    Hi as you have found by changing the factors for either attachment or market cap will change the value but you have to keep these in correct place i.e if its 100m then it should be in that bracket (i.e. 100-124).


    The issue is if the exchange rate changes as in the usd example(where the issue is) the attachment is pushed into another factor. We then have to put a trial an error figure (in this case 749) into the subjectivity table to get the correct outcome.


    The question is rather than using trial an error how can I work outthe 749 with some logic! :)

  • Exchange rate fluctuations

    Re: Exchange rate fluctuations...a challenge


    hi, thanks for your reply.


    If you take the 749 out of the subjectivity field in the usd sheet you will see the rate jump! I would attach it but it makes more sense if yyou take it out yourself and see the change.


    The 749 has been entered by trial and error so im wondering if i could calculate this figure rather than trial and error!!


    Thanks again

  • Exchange rate fluctuations

    Hi all,


    Here is an interesting one. I have inherited a work sheet that works out effective rate changes over two static periods (i.e. 2004/2005)but it has been an issue for me due to exchange rate changes each year.


    The worksheet is supposed to be completed in USD but Iam finding that the exchange rates are making the effective rate changes way too high i.e. -48%.


    Knowing this is incorrect I created the sheet in the original currency and when you compare the years, the change is only -16.2%, which is correct.


    To get the correct answer(-16.2%) using the correct worksheet(usd) Im having to trial an error a figure into the Subjective section which comes to 749 in my example attached.


    My question (after all the above :)) is how can I understand what this figure is or means (749). It simple to say to my boss oh it works but Im having to do two worksheets rather than the USD one! If anyone knows what this 749 is based on or how I can understand this I will be enternally grateful


    Yours,


    depressed but hopeful steve

  • IF Statement

    Hi all,


    Having a mental block at the moment...


    Its an IF statement with figure that falls into one of the attachment points (below). From this the factor is given and the cell below should show the relevant factor


    I.e in a1 = 112 and therefore attachment of 100-124, which means in cell a2=2.778
    Attachment Factor
    >150 1.000
    125 - 149 1.667
    100 - 124 2.778
    75 - 99 4.630
    50 -74 7.716
    25 - 49 12.860


    I have attached a worksheet with just the table in and a1 filled in.


    If you can have a look that would be great, im not so great with between figures!

  • Number Rounding issue

    Hi all,


    I have attached a spreadsheet which is a drummed down version of what im using but the relevant field and figures are shown. Quite simply Im have a rounding issue.


    I use an if statement to confirm that my totals are correct but the if statement considers the totals to more decimal placements that i want i.e 630,229.1614 and 630,229.1618. This should be considered the same i.e down to two decimal places.


    Even after I change the format it still screws up!! I have put the formula that matter next to the cells.


    Any help would be great

    Steve

  • loading values from xcel to a web page

    Hi all,


    I have a loader that can load from excel to oracle general ledger, which I did through help from you guys/gals.


    WHat Im trying to do now is load values and tab between each field on a particular web page that is internal to my company.


    The reason is i dont fancy writing out a 150 change requests which include so much information I will die of boredom. So im basically trying to create a loader.


    I have attached the code for my oracle loader below.. if it helps (hope i Posted it right, long time since I did this:)


  • Graph forecasting

    Hi all,


    I have a simple graph which shows graphically the current situation of various projects i.e if complete 1 and if not 0. This is divided by department and project. Therefore the intranet project for example is complete in all dept so I have assigned 1 and the graph shows (Project's at bottom of the graph) that for all depts project is complete.


    Right what i want to do is forecast the situation in a months time. I.e If i know a project will be complete in a month I can assign a value to this and it will show in a different colour (like a stacked graph) so say red equals done,blue equals forecast compete in a month.


    I can send the excel sheet, I expect this is so simple that Ive made it complicated. God help me :)

  • putting a database to disk

    Hi everyone, long tiem no speak!


    I was just wondering...


    I have an sql database that runs through and intranet site. It is a system of approval and i was wondering is it possible to put the databases on disk and install the whole program from disk i.e


    Press install, it installs program and inserts database........


    Think im barking up the wrong tree and would need to look at programming c++ or something?


    Any comment would be much appreciated:)

  • custom spreadsheet

    Hi people,


    I was thinking about create a program which would need a spreadsheet (of sorts wihin it)


    Does anyone know if i have to use excel or if it possible to make up my own custom spreadsheet??


    I was thinking this must be possible as i have seen non excel sheets before and wondered how difficult this would be to do?
    I would expect (hope:)) someone knows of this sort of thing?


    Cheers steve

  • Possibly a challenge or maybe a bore!

    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.


    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 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

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

  • 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