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
Re: Advanced SumIf?
Following Batmans advise I managed to solve the problem Tahnks everyone for your support
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, 
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
It works in a new workbook but not on the current one.
Thanks Pesky
Im using Win XP/XL 2000
What could be my problem?
Ray
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"),
'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"),
'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
