Posts by Raydreamk

    Re: Charts


    Thanks Andy how careless of me Id managed to figure it out earlier.
    But I would also like to know if you know how to create the kind of chart fro scratch, I just copied it from somewhere and modified it. I tried creating one by myself from scratch but failed at the point of adding the trend line. Just give it a try by creating a new one fro scratch and you might see waht i mean.
    Otherwise thanks for your help.


    Ray

    Re: Charts


    Thanks guys but what Im trying to achive is.
    1. To have chart 2 and 3 look like chart 1 i.e with bottom gray band falling btwn values 48 and 52. The white band should be streaching btwn Values 56 and 64. In short the values axis should be aligned to the colored bands as in chart 1. For example you will notice that instead of the white band increasing its the gray bands that are increasing, whereby the increase should be the opposite with the white band inreasing while the gray bands decreas accordingly.
    2. The scale should remain the same with 60 as the target and the deviations as shown.
    xlite, Ive tried all those things but it wont work.


    If someone can demonstrate this for me on the same workbook and attach it back Il be greatfull.

    I have data in rows which are then grouped as sub categories I would like to store this data in the data base and retrieve it while stil maintaining the format of sub categories


    Exaple bellow


    Improve xxxxxx 1 Form Emergency reliability team to
    sub1
    sub2
    sub3
    sub4
    sub5
    sub6
    PIP 2004 2 Draw up Maintenance action Improve plant reliability 3 Replace ID fans on both Boillers
    plant reliability 4 Review meeting and enter sub1
    sub2
    sub3

    Records 1 and 4 are grouped
    I want to trore this 2 dimensional info in an access database then retrive it to the same wsheet while maintaining this categories and sub categories.

    Re: VBA: Detect excel edit mode in VBA


    "If, inside my COM event driven VBA subroutine"


    Is it that you want is to detect edit mode in a workbook from an external source code or application? or you want to do it while in the same workbook.

    Re: Monitoring Oracle SQL queries in VBA


    Hi Cheesy I like the way you simplify this extraction from ORACLE database
    which is the data variable and how do you 'Process the data and put on a spreadsheet as you put it here


    Set OraDynaset = objdatabase.DBCreateDynaset(strSQL, 0&)


    Do While OraDynaset.EOF = False
    'Process the data and put on a spreadsheet
    'Status bar messages can be used here without problems
    OraDynaset.MoveNext
    x = x + 1 'counts the records
    Loop



    Ray

    Thanks alot guys for keeping the forum alive I think we need a section for testimonials Ozgrid has made me. Period!
    I have Five colunms of data as bellow


    Name Shift Cover Offs Rests
    Ray Morni Day 1 1
    James Off Night 2 0
    Jacob Off Night 1 1
    Jackie Rest Afternoon
    Junior Rest Day
    Jeremy Morning Night
    Ray Off Day
    James Off Night
    Jacob Rest Night
    Jackie Rest Afternoon
    Junior Off Day
    Jeremy Night Day


    I would like to put a formula in columns Offs and Rests to count the number of offs and rests a person acumulates over a period of time going down the rows. Any help will be appreciated.

    Thanks bnix
    Here is the full code but this time Im using an INSERT INTO satement


    Public wksSpace As Workspace, cnxMyOracleDB As Connection,rstTemp As Recordset
    Public cnxMyOracleDBConnected As Boolean ' True when MyOracleDB connection is established, false otherwise

    Set rstTemp = cnxMyOracleDB.OpenRecordset(sRequest, dbOpenSnapshot)
    rstTemp.Close
    p = p + 1
    If i = "43" Then
    Exit Sub
    End If
    Exit Sub
    Sub WritetoMyOracleDB() '--- Write data (x) into "MEASURES_D" table of MyOracleDB.
    On Error GoTo error_writing
    If Not cnxMyOracleDBConnected Then

    ''Initialise Connection to Oracle Database
    '[-------------------------------------------------------------------
    On Error GoTo errcode
    If Not cnxMyOracleDBConnected Then
    Set wksSpace = CreateWorkspace("ODBC space", "user", "", dbUseODBC)
    wksSpace.DefaultCursorDriver = dbUseODBCCursor


    Set cnxMyOracleDB = wksSpace.OpenConnection("ODBC Connection", , , "ODBC;DSN=DSN_MyOracleDB;UID=calc;PWD=calc;LOGINTIMEOUT=0;")
    cnxMyOracleDBConnected = True
    Call MsgBox("Youve been connected to MyOracleDB Database, ", vbOKOnly, "Connected..")
    End If
    Exit Sub
    errcode:
    Call MsgBox("There was an error connection with MyOracleDB Database, " & _
    "please inform your Database administrator." _
    , vbOKOnly, "Error in connection")
    End
    '[-------------------------------------------------------------------
    End If
    '[--------------------------------------------------------------------
    Dim CurrentEntity_id
    Dim CurrentPeriod_id
    Dim CurrentProduct_id
    Dim CurrentIndicator_id
    Dim CurrentEntry_type_id
    Dim CurrentUnit_id
    Dim Measure_Date
    Dim Measure_id
    Dim realised_value
    Dim adjusted_value1
    Dim adjusted_value2
    Dim adjusted_value3
    Dim relevant_value
    Dim comment_m
    Dim modif_date
    Dim modif_program

    CurrentEntity_id = Range("MyIndic").Offset(0, 1)
    CurrentPeriod_id = Range("MyIndic").Offset(0, 2)
    CurrentProduct_id = Range("MyIndic").Offset(0, 3)
    CurrentIndicator_id = Range("MyIndic").Offset(0, 4)
    CurrentEntry_type_id = Range("MyIndic").Offset(0, 5)
    CurrentUnit_id = Range("MyIndic").Offset(0, 6)
    Measure_Date = Range("MyIndic").Offset(0, 7)
    Measure_id = Range("MyIndic").Offset(0, 8)
    realised_value = Range("MyIndic").Offset(0, 9)
    adjusted_value1 = Range("MyIndic").Offset(0, 10)
    adjusted_value2 = Range("MyIndic").Offset(0, 11)
    adjusted_value3 = Range("MyIndic").Offset(0, 12)
    relevant_value = Range("MyIndic").Offset(0, 13)
    comment_m = Range("MyIndic").Offset(0, 14)
    modif_date = "SYSDATE"
    modif_program = Range("MyIndic").Offset(0, 16)


    ']----------------------------------------------------------------------------
    WritingIntoMyOracleDB = True
    sRequest = "INSERT INTO MEASURES_D VALUES" & _
    "(" & _
    CurrentEntity_id & "," & _
    CurrentPeriod_id & "," & _
    CurrentProduct_id & "," & _
    CurrentIndicator_id & "," & _
    CurrentEntry_type_id & "," & _
    CurrentUnit_id & "," & _
    "TO_DATE('" & Measure_Date & "','DD/MM/YYYY')," & _
    Measure_id & "," & _
    realised_value & "," & _
    adjusted_value1 & "," & _
    adjusted_value2 & "," & _
    adjusted_value3 & ",'" & _
    relevant_value & "'," & _
    comment_m & "," & _
    modif_date & ",'" & _
    modif_program & _
    "')"

    Set rstTemp = cnxMyOracleDB.OpenRecordset(sRequest, dbOpenSnapshot)
    rstTemp.Close
    error_writing:
    MsgBox Error
    End Sub



    This could not work
    'cnxMyOracleDB.Execute StrSql, , adExecuteNoRecords

    Im trying to update an oracle datbase but Imgetting an error
    3146 ODBC -- CALL FAILED I dont understand where this problem is comming from.
    Cuold someone look through my code maybe Im overlooking something.



    Sub UpdateComments()
    Dim StrSql As String


    InitConnection

    StrSql = "Update MEASURES_D" _
    & "SET COMMENT_M='My test comments'" _
    & " " & "WHERE (MEASURE_DATE={ts '2000-07-26 00:00:00'})" _
    & " " & "AND ENTITY_ID=64" _
    & " " & "AND INDICATOR_ID=514" _
    & " " & "AND UNIT_ID=45" _
    & " " & "AND PERIOD_ID=3"

    'Error occures here
    Set rstTemp = cnxPRODIS.OpenRecordset(StrSql, dbOpenSnapshot)



    End Sub


    Function InitConnection()
    On Error GoTo errcode
    'Cells.Group
    If Not cnxConnected Then
    'Call MsgBox("Click OK to open connection with DB, " & _
    ' "the operation can take a while." _
    ' , vbOKOnly, "InitConnection() message")


    Set wksSpace = CreateWorkspace("ODBC space", "user", "", dbUseODBC)
    wksSpace.DefaultCursorDriver = dbUseODBCCursor


    Set cnxPROD = wksSpace.OpenConnection("ODBC Connection", , , "ODBC;DSN=DSN_PRODIS;UID=ca;PWD=ca;LOGINTIMEOUT=0;")
    cnxConnected = True
    Call MsgBox("Youve been connected to Database, ", vbOKOnly, "Connected..")
    End If
    Exit Function
    errcode:
    Call MsgBox("There was an error connection with DB Database, " & _
    "please inform your Database administrator." _
    , vbOKOnly, "Error in connection")
    End
    Exit Function
    End Function

    Hi Dave


    Lets say the problem lies here


    the actual line was
    'numbers(i) = PRODIS_GetMeasure("D", "A", "BAMqy", "crushed7", "BP008", "t", CDate(StartDate))
    but since the CDate(StartDate) is not constant and has to change incrementally and since the formulas also change with every ccell i decided to pick up the formula from each cell and assign it to a variable then try to change the CDate(StartDate) in the variable as i run the fuction PRODIS_GetMeasure from the variable CST_FORMULAE


    I will include the full procedure bellow for U to take a look at maybe you can pick it up from there.


    Function CST_MonthToDate()
    On Error GoTo errorcode
    oldStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True
    Application.StatusBar = " http://www.cstkenya.com -- Please be patient... calculating month to date"
    Dim StartDate As String
    Dim SelectedDate As String
    Dim obcell As Object
    Dim CST_FORMULAE
    Dim PauseTime, Start, Finish, TotalTime, j As Integer


    'Start = timer ' Set start time
    For Each obcell In Range("test")
    CST_FORMULAE = Right(obcell.Formula, Len(obcell.Formula) - 1)
    CST_FORMULAE = Left(CST_FORMULAE, Len(CST_FORMULAE) - 5)

    StartDate = "01-" & Right(Format(Date, "dd-mmm-yyyy"), 8)
    'StartDate = Left(StartDate, 2)
    SelectedDate = Format(ActiveSheet.ComboBoxFrom.Value, "dd-mmm-yyyy")

    Dim i As Integer
    Dim numbers(1 To 31)
    For i = CInt(Left(StartDate, 2)) To CInt(Left(SelectedDate, 2))
    '" & CST_FORMULAE & "'"
    numbers(i) = " & " & CST_FORMULAE & " & CDate(StartDate)" & ""
    'numbers(i) = PRODIS_GetMeasure("D", "A", "BAMqy", "crushed7", "BP008", "t", CDate(StartDate))
    StartDate = Val(Left(StartDate, 2)) + 1 & Right(StartDate, 9)
    Next i
    'x = Sum(numbers)
    obcell.Value = Round(numbers(1) + numbers(2) + numbers(3) + numbers(4) + numbers(5) _
    + numbers(6) + numbers(7) + numbers(8) + numbers(9) + numbers(10) _
    + numbers(11) + numbers(12) + numbers(13) + numbers(14) + numbers(15) _
    + numbers(16) + numbers(17) + numbers(18) + numbers(19) + numbers(20) _
    + numbers(21) + numbers(22) + numbers(23) + numbers(24) + numbers(25) _
    + numbers(26) + numbers(27) + numbers(28) + numbers(29) + numbers(30) _
    + numbers(31), 0)

    Next
    'Finish = timer ' Set end time.
    TotalTime = Finish - Start ' Calculate total time.
    MsgBox "This event took " & TotalTime & " seconds"


    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    Exit Function
    errorcode:
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    MsgBox "Error No: " & Err & ": " & Error, vbOKOnly, "error"
    End Function

    HI


    as you can see fro the example bellow I want to be able to change the date in each loop that means capturing the formular then inserting the cumulative dates as the loops progress the problem is the function then does not work since numbers(i) is then returned as a string


    please help
    Ray


    For Each obcell In Range("test")
    CST_FORMULAE = Right(obcell.Formula, Len(obcell.Formula) - 1)
    CST_FORMULAE = Left(CST_FORMULAE, Len(CST_FORMULAE) - 5)

    StartDate = "01-" & Right(Format(Date, "dd-mmm-yyyy"), 8)
    'StartDate = Left(StartDate, 2)
    SelectedDate = Format(ActiveSheet.ComboBoxFrom.Value, "dd-mmm-yyyy")

    Dim i As Integer
    j = CInt(Left(SelectedDate, 2))
    Dim numbers(1 To 31)
    For i = CInt(Left(StartDate, 2)) To CInt(Left(SelectedDate, 2))
    '" & CST_FORMULAE & "'"
    numbers(i) = " & CST_FORMULAE & " & " & CDate(StartDate) & "
    'numbers(i) = PRODIS_GetMeasure("D", "A", "BAMqy", "crushed7", "BP008", "t", CDate(StartDate))
    StartDate = Val(Left(StartDate, 2)) + 1 & Right(StartDate, 9)
    Next i
    'x = Sum(numbers)

    Hi Everyone
    Im tring to capture the time used to run aprocedure but it gives me a compile error: Expected fanction or variable on the timer what could I be posibly missing?
    bellow is a sample code.


    Dim PauseTime, Start, Finish, TotalTime
    If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
    PauseTime = 5 ' Set duration.
    Start = Timer ' Set start time.
    Do While Timer < Start + PauseTime
    DoEvents ' Yield to other processes.
    Loop
    Finish = Timer ' Set end time.
    TotalTime = Finish - Start ' Calculate total time.
    MsgBox "Paused for " & TotalTime & " seconds"
    Else
    End
    End If

    hi Derk
    this PRODIS_GetMeasure is a function that accesses an oracle databse and these ("D", "A", "BAMqy", "crushed7", "BP008", "t", CDate(StartDate)) are the fields in the database I belive they all are very neccessary as you can see fro below.


    Function PRODIS_GetMeasure(sPeriodCode As String, _
    sEntryTypeCode As String, _
    sEntityCode As String, _
    sProductCode As String, _
    sIndicatorCode As String, _
    SUnitCode As String, _
    dDate As Date) As Single
    '*******************************************************************************
    ' Returns the measure_value for the measure referenced
    ' by cPeriodCode : Code of the period of the measure
    ' cEntryTypeCode : Code of the entry_type of the measure
    ' sEntityCode : Code of the entity the measure is associated with
    ' sProductCode : Code of the product the measure is associated with
    ' sIndicatorCode : Code of the indicator measured
    ' sUnitCode : Code of the unit measured
    ' dDate : Date of the measure
    '*******************************************************************************
    Dim sQuery As String
    Dim sQuery1 As String
    Dim sQuery2 As String
    Dim sMeasuresTable As String
    Dim sMask As String
    Dim sDate As String
    Dim rstTemp As Recordset
    if I can show you a part of it
    Ray

    Hi everyone


    I have come up with an array as bellow and i would like to sum up all the values that are being returned by the array numbers, to something like x=sumNumbers)
    I dont know how possible this is instead of the long way ive put it.
    2. I would also like to get sudgestions on the availability of alternative faster code to compute this array thing as this one almost take like 5 minutes to run.


    Dim i As Integer
    j = CInt(Left(SelectedDate, 2))
    Dim numbers(1 To 31)
    For i = CInt(Left(StartDate, 2)) To CInt(Left(SelectedDate, 2))
    numbers(i) = PRODIS_GetMeasure("D", "A", "BAMqy", "crushed7", "BP008", "t", CDate(StartDate))
    StartDate = Val(Left(StartDate, 2)) + 1 & Right(StartDate, 9)
    Next i


    'x = Sum(numbers) ??


    obcell.Value = Round(numbers(1) + numbers(2) + numbers(3) + numbers(4) + numbers(5) _
    + numbers(6) + numbers(7) + numbers(8) + numbers(9) + numbers(10) _
    + numbers(11) + numbers(12) + numbers(13) + numbers(14) + numbers(15) _
    + numbers(16) + numbers(17) + numbers(18) + numbers(19) + numbers(20) _
    + numbers(21) + numbers(22) + numbers(23) + numbers(24) + numbers(25) _
    + numbers(26) + numbers(27) + numbers(28) + numbers(29) + numbers(30) _
    + numbers(31), 0)



    Ray

    Hi Everyone?


    I would like to open an application called Business Objects, it lies in the path “C:\Program Files\Business Objects\BusinessObjects 5.0\BUSOBJ.EXE” and it supports VBA I want to open the program from Excel and run a macro that has been recorded in it. I’ve tried the old traditional way of opening the app then I assign the macro name contained therein but it doesn’t run, I would also like to include a validation check to verify that the app is actually installed in the default path (above) before attempting to launch it. Does anyone have a better way of doing this?


    Ray.

    Hi All


    Thanks again for keeping the forum alive.
    What is it with VBA when it brings up the compile error:
    Can;t find project or library


    and what is highlighted as the object is an obvious object that ive been using in the same project for along time eg
    Chr(13) with Chr being highlighted or
    Format(Now, "yyyy-mm-dd hh:mm:ss")
    with format being highlighted.
    Then the procedure completly refuses to work.
    I realy dont understand why anyone come across this problem before?
    and what is the solution to this.
    Ray

    Hi


    Does anyone know how to create those small collapsible buttons that when clicked open to reveal or hide predefined rows? They work more or like tree view object because you can have certain child levels.
    Funny thing is Ive a workbook which I did this kind of thing a long time ago but I seem not to remember at all how I did it, maybe Im not alon happens many times.


    Ray