Well handing over during shift change using Excel Graphics
The changes made to a well like changing the bean size or bypassing the hi-lo valve or even closing the well are generally recorded in the shift register and verbally communicated to the taking-over shift. A small excel application can do this graphically thereby providing color coded representation of the various activities carried out on the well.
salient features:-
- Database driven, what ever changes are done to a well, are captured in a database which can be queried. MS access database is used as backend.
- The excel graphics are driven from the database, so that each time the excel file is opened, it automatically picks the latest changes from the database and same is displayed in the graphics.
- Single snapshot view of all the wells with a excel sheet dedicated to one area.
- As it is database driven, the database can be web-enabled so that the real time data can be seen at base-office/SAM's end.
Process:-
The person doing the updation does using a form like this:-
The excel file containing the graphics looks like this:-
This is the legend page for the graphics:-
To try it out:-
- Download the access database here:- https://myspace.ongc.co.in/files/form/anonymous/api/library/720a78d9-3962-4d25-bc90-102c820740bc/document/1cf91514-7a1a-4c74-a2d0-237b64fe30df/version/49f42619-67a2-4f73-bac4-75ccaccb725f/media/well_details.mdb
- Then, save it to a convenient location like say C:\test. Avoid locations like mydocuments or desktop.
- Open control panel, administrative tools, odbc data sources (32 bit), in system dsn tab, click add , select “driver do Microsoft access (*.mdb) “, click finish, takes you to next page called ODBC Microsoft access setup.
- In Data source name , give well_proj. No other name. Click on select and browse for your access .mdb file in the directory you have copied say (c:\test). Click ok to accept.
- Now your DSN (data source name) is ready.
- Next, download the excel file from here:- https://myspace.ongc.co.in/files/form/anonymous/api/library/720a78d9-3962-4d25-bc90-102c820740bc/document/c35191f4-c356-4f8a-a668-7db9a69386ba/version/c757eeb9-1f33-4136-aeed-dc2c7f0a72ed/media/test_ado.xlsm
- Copy it to c:\test. Open the file and enable the macros if it asks you to. Now you can see the graphics. To change a well status, press ctrl +a to launch a form, click on any well, and change its status. Click ok
- Now click on another sheet, say LEGEND and click back to the sheet you were viewing, the changes you made to the form would have been updated to the graphics.
Learning:-
- Updating and manipulating Excel Graphics elements using data stored in Database. The graphic elements like shapes can be manipulated, their text changed, their background colors and orientation changed using code.