Home

Data Collection Analysis and Management System

1. Induction Time Automati an yY J Evaluation sensitivity Tg MeasurementDate J 10909200 Surface weighted mean D132 to Volume weighted MeanD 43 957 0 0 0 0 0 00 0 00 0 0 0 0 ses aos 5 0 0 p Ae az 9009 1432105 1 MeasurementDate J 28 8 2010 9 59 MeasPons 0 a Shear points 0 0 0 0 j 3S1 __ T es 0038 MeasPoints 0 JJ 4J s shearPoints i s _ 3 e 267 Wiscosity Pas o O p80 10037 Pour ptlow t c viscosity teas Pour pt hight C E E viscosityipas cloud ptec Viscosity PaS Measurement Date 21 01 2011 02 09 2010 WorkNumber 10112 m 55 550 1002 ignition wat sso se pamer Jo0O0 DS s 4 eot 0 0 0 0 30 pan Z4 deo 0 ltijperiod so belay 0 o P 0 J Maininjperioh so o ITem 0
2. Figure 46 Main Search User Form The list of fuel Types are listed in the list box where the user can have the option to select one or more then based on the fuel types the program will search all the matching ID numbers and working numbers that will be in list box Private Sub CommandButton6_Click Application ScreenUpdating False diable screen update Function that opens the seven files else activate if it s already opened search every files based on the ID numbers or Working numbers ForMalvern DeleteUnValidValue delete all results that are not marked as valid Workbooks MainSearch 01 12 xlsm Activate go to result page of this file Worksheets RESULT Activate UserForm3 Hide hide the search form Application ScreenUpdating True End Sub Snippet 33 Get All valid result command button 76 Sub ForMalvern If UserForm3 CheckBox5 Value True And _ UserForm3 CheckBox4 Value True Then check if files are open then activate it else if closed then open them from IDM ChecklfOpenTit open titration database file and search based on given numbers CheckIfOpenBC open amp search from Bomb calorimeter ChecklfOpenMalvern open amp search from Malvern mastersizer database file CheckIfOpenRancimat open amp search from Rancimat CheckIfOpenCRU open amp search from CRU CheckIfOpenRheometer open amp search from Rheometer CheckIfOpenDSCI1 open amp search from DSC End If
3. Figure 48 Titration Import 80 IMPORT FILE 2 Import Get Data Acd Number Get Data For Iodine Value Get Data For Peroxide Value ID number Sample Name Operator Arrival Date Measurement Date Measurement Time ENDE g Strong Acid Number mg KOH g KOH IPA Acid Number mg KOH g 0 test consumption ml Notes KOH consumption ml Valid Fuel Type j Get Next Data Figure 49 Acid Numbers Properties Figure 49 shows what properties for the Acid number must import There are pages for Iodine Value and Peroxide Value too where they have their own properties 81 6 5 2 Search Titration s search function is slightly different from others In this search there are three main criteria which are the ID number measurements date and arrival date User may choose only one from this three unlike other search function that user select multiple combinations Additional option can be use to add in the search criteria by selecting the checkbox of Fuel types or valid measurements PAGE FOR SEARCHING AND FOR THE RESULTS SEARCH PAGE ID number 7 9002 Measurement Date 01 01 2009 09 09 2009 dd mm yyyy Arrival Date C 99 Additional Option IV Fuel Type biodiesel biomass blend condensate Only the Measurement that is marked as Valid Search this Database Search the External File Figure 50 Titration
4. Actions Start the Run Action occurs Immediately Switch the Gas to Nitrogen at 20 0 ml min Action occurs Immediately a gt Heat from 20 00 C to 320 00 c at 5 00 c min 15 DSC Temperature scan Time Unsubtracted Baseline Program sample ow Heat Flow Temperature Temperature 000000 20 6 415000 20 104000 5 9 0 i o 000000 22 430190 21 704756 9 154744 5 073843 20 105223 34 078967 0 180000 45 931617 _ 600 999 00000000 00000000000 8 88895 19 931000 Figure 35 DSC measured data in text file format User form for getting only the necessary parameters displays as in Figure 36 The user can now select either to get the data save it to database or cancel the whole process Get Data from Imported DSC Report File Ottana PO 20090821 27 04 2010 320 00 LR 27 4 2010 17 11 ES ee Figure 36 Get Data Form 61 Command button Get Data has code shown below Private Sub CommandButton2 Click Sheets DSCRawData Activate CopyData function that search properties and place the values in given range GetDataDSC function that place the property values into user form text boxes End Sub Snippet 23 Get Data Command Button There are two functions included in this command button which are the CopyData and the GetDataDSC Sub GetDataDSC place the properties into their corresponding textboxes Application Scr
5. Measurement Date and Net Heat are selected Elself UserForm4 CheckBoxl Value False And UserForm4 CheckBox2 Value True _ And UserForm4 CheckBox3 Value False And UserForm4 CheckBox4 Value True Then DateM UserForm4 TextBoxl Value DateM2 UserForm4 TextBox2 Value If Range J amp CStr BCRow Value gt Range BCStart AJ3 Value And Range J amp CStr BCRow Value lt Range BCStart AJ4 Value And _ Range B amp CStr BCRow Value gt CDate DateM And Range B CStr BCRow Value lt CDate DateM2 Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Only GHV is unselected 124 Elself UserForm4 CheckBoxl Value True And UserForm4 CheckBox2 Value True _ And UserForm4 CheckBox3 Value False And UserForm4 CheckBox4 Value True Then DateM UserForm4 TextBox1 Value DateM2 UserForm4 TextBox2 Value For i 0 To UserForm4 ListBox1 ListCount 1 If UserForm4 ListBox1 Selected i And _ Range A amp CStr BCRow Value CStr UserForm4 ListBox1 List i _ And Range J amp CStr BCRow Value gt Range BCStart AJ3 Value And _ Range J amp CStr BCRow Value lt Range BCStart AJ4 Value And Range B amp _ CStr BCRow Value gt CDate DateM And Range B amp CStr BCRow Value lt _ CDate DateM2 Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Next i ALL four citerias are selected 1234 Elself UserForm4 Ch
6. User has option to add this data with the four measuring points viscosity and shear rate This function will look for the last used row and using the offset 1 0 will select the row after which is the first empty row where the values will be added Formatting for the decimal point depends to the property assigned 73 6 4 MAIN SEARCH APPLICATION Main search file is the last file of this project that created This file has the most interaction with the IDM which access all the seven databases There is also one additional file that this application has access into which is the chemical archive file in MS Excel format This chemical archive file saved as kemikaalikortisto xls contains all the Sample Names the corresponding ID numbers and what fuel types they are When the user form initialize the program automatically opens the archive file In the textbox shown in figure below the user have to enter three digits of the year where the user wants to search from pelle Lama oo digits of year to identify which 010 sheet we would like to search from 009 2009 010 for 2010 011 for Salari Tune 2011 and so on Figure 42 Textbox Year In the Figure 42 there is 010 which will activate the worksheet of year 2010 such as the n ytteet2010 and ty numerot2010 located in Figure 43 Figure 43 Kemikaalikortisto Worksheet MAIN SEARCH OPEN DATABASE Figure 44 Main Search files command button The
7. False And UserForm4 CheckBox2 Value False _ And UserForm4 CheckBox3 Value True And UserForm4 CheckBox4 Value False _ And Range K amp CStr BCRow Value gt Range BCStart AH3 Value And Range K amp CStr BCRow Value lt Range BCStart AH4 Value Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False If only Net Heat is selected criteria unhide row from column Jmatch this is multi select list box Elself UserForm4 CheckBoxl Value False And UserForm4 CheckBox2 Value False _ And UserForm4 CheckBox3 Value False And UserForm4 CheckBox4 Value True _ And Range J amp CStr BCRow Value gt Range BCStart AJ3 Value And Range J amp CStr BCRow Value lt Range BCStart AJ4 Value Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False Measurement Date and Gross Heat are selected Elself UserForm4 CheckBox1 Value False And UserForm4 CheckBox2 Value True _ And UserForm4 CheckBox3 Value True And UserForm4 CheckBox4 Value False Then DateM UserForm4 TextBoxl Value DateM2 UserForm4 TextBox2 Value 49 If Range K amp CStr BCRow Value gt Range BCStart AH3 Value And Range K amp CStr BCRow Value lt Range BCStart AH4 Value _ And amp CStr BCRow Value gt CDate DateM And Range B CStr BCRow Value lt CDate DateM2 Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If
8. True Row B must not be emypty because its the measurement date Do While Len Range B amp CStr DSCStartRow Value lt gt 0 If Range A amp CStr DSCStartRow Value IDNumber Then Unhide the entire row of match ID from column A Rows CStr DSCStartRow amp amp CStr DSCStartRow EntireRow Hidden False End If DSCStartRow DSCStartRow 1 Loop Sheets Sheet2 Select Copy visible rows and paste into another location Range B2 B amp Range C65536 End xlUp Row SpecialCells xlCellTypeVisible Copy Sheets DSCStart Activate Range CA2 Select Selection PasteSpecial Paste xl Values Operation xlNone SkipBlanks False Transpose False There are formula in cells but this paste only values Worksheets Sheet2 Activate Rows 2 300 EntireRow Hidden False Sheets DSCStart Select Exit Sub if no found based ID number then prompt message to user unhide the rows which are _ hidden for searching purpose then go to the Start page of this DSC DSC Error MsgBox No DSC measurements found Worksheets Sheet2 Activate Rows 2 300 EntireRow Hidden False Worksheets DSCStart Activate Application ScreenUpdating True End Sub Snippet 18 Sort List of pdf file 56 Snippet 18 function sort list of files based on given ID number in the textbox Then the user needs to select from the list box what to open Private Sub CommandButton6_Click Application DisplayAlert
9. 25 5 3 2 Use Case for Rheometer Rheometer s main function is to import viscosity and cloud points Searching for viscosity has same four criteria s to select and the matching measurements can further get the viscosities by giving the four shear rates Adding data is done by adding into worksheet but there is code that reformats the inputted data into another worksheet Further details will be in the Rheometer part of this report Add Measurement d Transpose data Select from Criteria Search Measurement for Viscosity Select from Result List Give 4 Meas Points Get Viscosities Measurements for Cloud amp Pour Select from Criteria User Figure 16 Rheometer Use Case Diagram 26 5 3 3 Use Case for DSC Two main function import and search for the user Import needs to open the file in the local drive get necessary properties and add to raw data Search can be from the database file or from the IDM where the list of pdf files are stored Opent text csv files Get necessary properties Add to Check atahase duplicate User Search from Select from paw data iteria View data from M Connect to IDM Select from list of pdf Sort list of iles Figure 17 DSC Use Case Diagram 27 5 3 4 Use Case for Search Main Search connects to IDM and opens eight files including the kemikaalikortisto to get the fuel types User have to give the year then based on selected fuel types list
10. Runlist_BombCalorimeter AG12 Value Date TextBox4 Value Range Runlist_BombCalorimeter AG13 Value Time TextBox5 Value Range Runlist BombCalorimeter AF6 Value SpikeWeight TextBox6 Value Range Runlist BombCalorimeter AF17 Value InTemp TextBox7 Value Range Runlist BombCalorimeter AF10 Value l empRise TextBox8 Value Range Runlist_BombCalorimeter AF18 Value Gross Heat TextBox9 Value Range Runlist BombCalorimeter S2 Value Link TextBox10 Value Range Runlist_BombCalorimeter S1 Value File Name TextBox11 Value Range Runlist_BombCalorimeter AF 15 Value TextBox19 Value Range Runlist_BombCalorimeter AF 12 Value End Sub Snippet 10 GetDataBC GetDataBC function takes the properties into text boxes of the user form TextBox is set to have the value for the Sample Name TextBox2 for the Sample Weight and so on 40 LIST OF FILE FROM BOMB CALORIMETER DRIVE 10036 1 1 0 5604 23 07 2010 38 0 1108 29 9126 6 6831 ss ers ss http 10 162 26 8 cgi bin report cgi 10 Im Figure 24 Data Transfer to Database The user form looks like in Figure 24 above where values are shown with their corresponding properties Heat Value Calculation is an option for user to do 41 6 1 3 Heat Values Calculation There is the Additional Parameter section in this page wherein the user can calculate the Higher Heat Value HHV or the Lower Heat Values LHV be
11. VAASAN AMMATTIKORKEAKOULU 9 0 069 VASA YRKESHOGSKOLA 4 UNIVERSITY OF APPLIED SCIENCES Mercy Racasag Jaatinen Data Collection Analysis and Management System Technology and Communication 2011 ACKNOWLEDGEMENT This project wouldn t be possible without the help and assistant of many individuals who helped and give guidance in the process of doing this project First and foremost I would like to thank my husband and my little princess for the love sacrifice and understanding to finish my last year of studies I have been always away and busy but I m always looking forward to go home after a long day knowing that there are both of you waiting for me Thank you for sacrificing your career for this whole year to support my last year of studies and be with our daughter I can t thank you enough for that Thanks to my family to my mom my dad and siblings Studying far away from home country isn t easy decision but you all encourage me to come and pursue my dreams Special thanks to my aunt and my uncle who introduced me to this school and brought me here in Finland without the two of you I am not graduating from this school Thanks to the PPT Power Plant Technology team who give me the help that I need whenever I have questions you all are ready to answer and give advice To my boss and to my colleagues thank you to all of you To one person who introduced me in this company I thank you and your family very much I would like
12. _ And Range B amp CStr BCRow Value lt CDate DateM2 Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Criteria selected are ID number Sample Name and Measurement Date Elself UserForm4 CheckBoxl Value True And UserForm4 CheckBox2 Value True _ And UserForm4 CheckBox3 Value False And UserForm4 CheckBox4 Value False Then DateM UserForm4 TextBoxl Value DateM2 UserForm4 TextBox2 Value For i 0 To UserForm4 ListBox1 ListCount 1 If UserForm4 ListBox1 Selected i And _ Range A amp CStr BCRow Value CStr UserForm4 ListBox1 List i _ And Range B CStr BCRow Value gt CDate DateM And _ Range B amp CStr BCRow Value lt CDate DateM2 Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Next i If only ID number S N is selected unhide row from column A match this is multi select list box Elself UserForm4 CheckBox1 Value True And UserForm4 CheckBox2 Value False _ And UserForm4 CheckBox3 Value False And UserForm4 CheckBox4 Value False Then For i 0 To UserForm4 ListBox1 ListCount 1 If UserForm4 ListBox1 Selected i And _ Range A amp CStr BCRow Value CStr UserForm4 ListBox1 List i Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Next i If only Gross Heat is selected criteria unhide row from column K match this is multi select list box Elself UserForm4 CheckBoxl Value
13. e artem S 494 o 0 sa Imp S oos 439 eme E 0 0 0 0 0 0 0 0 349 pe 5 ss MCP a ase ooo 84 ts pme 0 e6 a Figure 47 Main Search Result Page 79 6 5 OTHER APPLICATIONS The rest of the devices have similar applications but different properties in each user form 6 5 1 Import Titration s import interface has an option from which sheet it needs to take the data The description for Titration device mentioned that the device measures the samples acid number iodine value and peroxide value Each of them has individual sheet and may contain similar sample name Program compares the database last added data and imports the next to it Figure 49 shows the properties of Acid Number that needed to be imported IMPORT FILE xi Import Get Data For Acid Number Get Data For Iodine Value Get Data For Peroxide Value Open the External File Where the IMPORT FILE Measurements has been directly added Select Sheet Where To Get TheData z AcidNumberRawData IodineValueRawData PeroxideValueRawData Data From Database Data From External file Sample Name Sample Name Measurement Date Measurement Date Measurement Time Measurement Time Next gt
14. xlByRows SearchDirection xlNext _ MatchCase False Activate Activate the cell End With If ActiveCell Value UserForm2 TextBox19 Value Then MsgBox Duplicate Give prompt message to user that its duplicate Else Ex BCToADDData else if no duplicate then Add data to raw data Exit Sub End If End Sub Snippet 12 Check Duplicate Entry Snippet 12 finds a matching value of the measurement date amp time from the user form within the specific range If found match then prompt a message to the user that it is a duplicate else add the data 44 6 1 5 Add to Database The Data will be added to the Worksheet name BCRawData from the text box of the form Sub BCToADDData Bring the textbox values into corresponding range of cells Sheets BCRawData Activate Range A7 EntireRow Insert Insert new row in row 7 Range BCRawData A7 Value TextBoxl Value Sample to a7 Range BCRawData D7 Value TextBox2 Value Sample weight to d7 Range BCRawData B7 Value TextBox3 Value Meas Date to b7 Range BCRawData C7 Value TextBox4 Value Range BCRawData G7 Value TextBox5 Value Range BCRawData E7 Value TextBox6 Value Range BCRawData F7 Value TextBox7 Value Added parameters Range BCRawData H7 Value TextBox12 Value Range BCRawData I7 Value TextBox13 Value Range BCRawData J7 Value TextBox14 Value Range BCRawData K7 Value TextBox8 Value Ran
15. 72 Range L65536 End xlUp Offset 1 0 Value Format Range L65536 End xlUp Offset 1 0 ZZ Ag d 3 set of measuring points with corresponding shear rate and viscosity values format decimal values Range M65536 End xlUp Offset 1 0 Value Range Sheet1 C4 Value Range M65536 End xlUp Offset 1 0 Value Format Range M65536 End xlUp Offset 1 0 Range N65536 End xlUp Offset 1 0 Value Range Sheet1 A4 Value Range N65536 End xlUp Offset 1 0 Value Format Range N65536 End xlUp Offset 1 0 Range 065536 End xlUp Offset 1 0 Value Range Sheet1 B4 Value Range 065536 End xlUp Offset 1 0 Value Format Range O65536 End xlUp Offset 1 0 4th set of measuring points with corresponding shear rate and viscosity values format decimal values Range P65536 End xlUp Offset 1 0 Value Range Sheet1 C5 Value Range P65536 End xlUp Offset 1 0 Value Format Range P65536 End xlUp Offset 1 0 Range Q65536 End xlUp Offset 1 0 Value Range Sheet1 A5 Value Range Q65536 End xlUp Offset 1 0 Value Format Range Q65536 End xlUp Offset 1 0 Range R65536 End xlUp Offset 1 0 Value Range Sheet1 B5 Value Range R65536 End xlUp Offset 1 0 Value Format Range R65536 End xlUp Offset 1 0 4 End Sub Snippet 31 Add to database function
16. AT cannes Merona 11 2 TECHNOLOGY OVERVIEW ea uad a ate ne e utpat ad a 12 ZU UNMBSCEXCBL edic M 12 22 VBA and IDE CD p ete Ed 12 23 NVISUATABASIC EDITOR s etiem unu pe ert elici De metas 12 24 USER dates tc UN A Ti ARR DU NU VU ed Gp ER Ud tO GO E ees 13 25 VPN CONNECTION oett eta ferratis bisce ites laf Guarentee 14 3 DEVICES nacre Give erae A NUR Ea UR ERAS EUR NEAR 15 SJ Bomb Caloftmeler Duel ojo e edipi alae teda sarda I5 3 2 Malyerm beo pidiendo etu sr era E 16 EXE inl Cm 16 3 4 ZEHEAUOILS o4 enu re M uu e s 17 3 5 Differential Scanning Calorimetry DSC sess 17 AR HC ULNA cat sear ie ccna te eat E E E cama 18 3 7 Combustion Research Unit 18 4 THE APPLICATION DESCRIPTION 19 4 1 Requirement Analysis coasts 19 5 DATA COLLECTION ANALYSIS amp MANAGEMENT SYSTEM 21 Dub PRCCESS Ol COMIPULETS oce tasche ipla pde 2l 25 2 FLOW CM ARE o doo oh p ebat ciu d cedi 22 2 9 1 Aetio E UE be ote ae pues tue 25 5 3 1 UseCasedor unco veo edt ette urba 25 313 2 Us C s for Rhe meter ua coi oci ian he E d opto ioa 26 5 3 3 Use Case TOP DSG a E E
17. End Sub Snippet 34 Open the files Snippet 34 is a combination of some functions which has been used already in the previous snippets in this report This function opens all databases in the IDM and search them one by one based on the selected ID number s and or working number s All the matching measurements are copied to their corresponding worksheet in this file There are huge numbers of measurements that contains same ID numbers but the final result will only show those that are marked as valid after the measurements is done This is marked as valid x in specific row of each database 77 Sub DeleteUnValidValue there are sheet names corresponds to the device name where results from searching based on id numbers and or working numbers are pasted clear the entire row if column for valid values is emty which must be marked as if the measurement is valied On Error GoTo Errx Sheets Titlodine Activate Valid values is located in column M for Iodine value Range M M SpecialCells xl Cell T ypeBlanks EntireRow ClearContents Sheets TitAcid Activate Valid values is located in column N for Acid number Range N N SpecialCells xlCellTypeBlanks EntireRow ClearContents Sheets TitPeroxide Activate Valid values is located in column M for Peroxide Range M M SpecialCells xlCellTypeBlanks EntireRow ClearContents Sheets RancimatResult Activate Valid values is located in column H for Ra
18. Search Marked as valid are those measurements that marked as x as symbol for valid in column named valid One sample is measured few times in one hour and all the measurements are saved but the laboratorian decides which among all the measurements is valid for the sample 82 Rancimats and Malvern Mastersizers search form are similar except that in Rancimat there is the valid option Searching Form D Number Number test koe 70 ERP T 45 10112009 4 koe 60 ERP T 45 DT 50 0 6 valm 15 1 You can filter the koe 60 ERP T 45 DT 50 0 6 valm 15 1C Sed dist by filing up the r162 number texthox above Koe 75 ERP T 45 vesi28 DT 50 1 v 4 Koe 75 ERP T 45 vesi28 DT 50 1 v 4 Koe 74 ERP T 45 vesi28 DT 50 0 6 150 Y Measurement Date v T Measurements Date Information Exit ji 11 11 2009 16 10 2009 23 02 2010 24 02 2010 List of Measurement Dates stored in the database 22 02 2010 If there might have some problems for e g the date 19 11 2009 you entered in the Measurement Dates above is 04 11 2009 included in the list of dates in this list check the 27 10 2009 REGIONAL SETTINGS in Control Panel Date separator as 09 09 2010 dot and short date in form of dd mm yyyy 04 11 2010 29 07 2010 27 08 2010 Figure 51 Malvern Mastersizer Search Form Malvern only search based on ID number and or measurements date There are also in the list all the measu
19. Value 1 activate the second page of the user form GetTheResultsToPage2 Put to textboxes all the main information GetTheMatchingReport Get the corres g viscosities based on meas points given End If End Sub Snippet 27 Command Button Get Data Snippet 27 is the code for the command button named Get Data Checking for the minimum value of the measurement point is set to 5 If more than 5 then next snippets are used Result Page escent son CC27 SN18033 d 0 mm Figure 41 Viscosity Shear Rate based on Meas Points Figure 41 shows the corresponding Shear Rate and Viscosity value of certain measuring points given while Snippet 28 shows the code that takes the values into the form 68 Sub GetTheResultsToPage2 Bring main properties to textbox in page 2 such as Sample ID fuel type UserForm2 TextBox9 Value ActiveCell Value measurement date Range Sheet1 D2 Value ActiveCell Value selected from list control source UserForm 2 TextBox6 Value ActiveCell Offset 7 0 Value UserForm2 TextBox5 Value ActiveCell Offset 6 0 Value UserForm 2 TextBox8 Value ActiveCell Offset 5 0 Value UserForm2 TextBox7 Value ActiveCell Offset 4 0 Value UserForm2 TextBox10 Value ActiveCell Offset 1 0 Value UserForm2 TextBox11 Value ActiveCell Offset 10 0 Value UserForm2 TextBox12 Value ActiveCell Offset 24 0 Value UserForm2 TextBox13 Value UserForm2 TextBox1
20. meas Points function for the rest of three are same Dim MPoint1 declaration On Error Resume Next MPointl UserForm2 TextBoxl Value If MPointl Then Exit Sub With ThisWorkbook Worksheets Sheet2 Cells Find What MPointl After ActiveCell LookIn xlValues LookAt _ zxIWhole SearchOrder xlByRows SearchDirection xlNext MatchCase False Select UserForm2 TextBox17 Value ActiveCell Offset 0 1 Value bring to textbox Range Sheet 1 A2 Value UserForm2 TextBox17 Value UserForm2 TextBox18 Value ActiveCell Offset 0 3 Value Range Sheet1 B2 Value UserForm2 TextBox16 Value If ActiveCell Value MPointl Then Exit Sub End With End Sub Snippet 30 GetTheMatchingReport Function 71 Snippet 30 function copy entire 7 columns of matching measurements and gets all corresponding four viscosity based on the given points and put them to text box of the form Sub AddToDatabase Sheets RawData Select activate the raw data of this file bring the values from textbox into range of cells properties which includes Meas Date ID number Fuel Type Temperature Range B65536 End xlUp Offset 1 0 Value UserForm2 TextBox9 Value Range A65536 End xlUp Offset 1 0 Value UserForm2 TextBox5 Value ID Range C65536 End xlUp Offset 1 0 Value UserForm2 TextBox6 Value Range D65536 End xlUp Offset 1 0 Value UserForm2 TextBox7 Value T Range F65536 End xl
21. of Id numbers and working numbers are listed where user have to select User can also display desired database separately Display separately the IDM Display USer s Manual Open kemikaalikortisto User Select fuel Types List all working numbers List all ID numbers only valid Search all rasulte database Connect z to IDM Figure 18 Main Search Use Case Diagram 28 6 THE IMPLEMENTATION amp RESULT In this particular part of report the result during implementation of the code is discussed 6 1 Bomb Calorimeter Application Bomb Calorimeter Application has the main page Worksheet named BCStart This Worksheet contains two Form Control Buttons import Files Search Data from this database Figure 19 Bomb Calorimeter Form Control Buttons These Form Control Buttons contains a macro shown in Snippet 1 which opens the specific user form user interface assigned to specific button Sub UserForm2Shw Displays the user form 2 object which is for import UserForm2 Show End Sub Sub UserFrm3Shw Displays the user form 4 object which is the form for searching this BC database UserForm4 Show End Sub Snippet 1 Control Buttons code User form named UserForm2 is assigned to import while UserForm4 is for searching Control buttons when pressed will display the forms 29 6 1 1 Import Files Clicking the button with name Import Files
22. s possible that they can create a VPN connection from home to work 5 Intranet users with appropriate user rights can establish a remote access VPN connection with VPN server The user can access the protected resources of the company s network and all communication is encrypted for data confidentiality purpose 2 YEN connection Secured ar hidden network Figure 3 Remote Access VPN 2 14 3 DEVICES USED There are seven different devices in the laboratory and each of them has different method of measurements Pictures of device was taken from the Fuel Laboratory site 31 Bomb Calorimeter Bomb Calorimeter Device is used to measure the samples Heat Values such as HHV or GHC and LHV or LHC This device has an internal hard drive that is accessible through Intranet Higher Heat value can also be known as the Gross Heat of Combustion which is the quantity of energy released when a unit mass of fuel is burned in a constant volume enclosure with the products being gaseous other than water that is condensed to the liquid state 6 Net Heat of Combustion or the Lower Heat Value is the quantity of energy released when a unit mass of fuel is burned at constant pressure all of the products including water that is being gaseous 6 Figure 4 BC device 15 3 2 Malvern Mastersizer Malvern Mastersizer enables the determination of size distribution of solid particles or liquid droplets in a sample Laser diffrac
23. this open it as web page Workbooks DSC xlsm Activate activate the workbook file DSC Application ScreenUpdating True Application DisplayAlerts False End Sub Snippet 21 Follow Hyperlink function Snippet 21 is the code that activates the hyperlink of the active cell that was just selected using the function in Snippet 20 Figure 33 gives us the view of the measured data we just selected In this case we opened the measurement named 10013_kala_20100603_2 pdf as web document 58 St bts vsti gt lt a 1 gt amp GEA li rate 10 Cimin from 90 to 60 Hold 10 min in 60 Heating rate 10 Cimin from 60 to 120 ine subtraction Unknown zone Figure 33 DSC Opened Measurement in IDM 6 2 2 DSC IMPORT DSC Import function can import a text files or comma separated values file csv Sub Import_Text_DSC function that will import files from local drive On Error GoTo Ex_DSC Dim ImportFileName As String Set CSheet ActiveCell ImportFileName Application GetOpenFilename filefilter _ Text Files txt txt amp CSV Files csv csv filter text or csv file formats open the file as new workbook file delimites by delimeter character which is Tab Application Workbooks OpenText Filename _ ImportFileName DataType xlDelimited Tab True ActiveSheet UsedRange Copy De
24. to say thank you also to my supervisor for helping me in the documentation and advice whenever needed Thanks to the head of department for helping me to clarify some issues regarding my project Thanks to my fellow classmates who became my dear friends and to all my teachers I will definitely miss my time in VAMK Mercy Racasag Jaatinen 31 05 2011 VAASAN AMMATTIKORKEAKOULU UNIVERSITY OF APPLIED SCIENCES Information Technology ABSTRACT Author Mercy Racasag Jaatinen Title Data Collection Analysis and Management System Year 2011 Language English Pages 8542 Appendices Name of Supervisor Moghadampour Ghodrat W rtsil Power Plant Technology Fuel Laboratory is the place where the PPT team tests their samples of different fuel types such as Heavy Fuel Oil Light Fuel Oil Crude Oil Straight Bio Fuel Oil among others These samples are measured to define their characteristics and properties using different devices Data Collection Analysis and Management System were developed to collect and analyse measured data from the laboratory devices Every device has own database in MS Excel format and Visual basic for application VBA is the programming language used to develop this project Main functions consist of searching and importing files Import function can import data from same excel file format xls text format txt comma separated variable csv or portable document format pdf Main Search File was done after th
25. xlUp Row i Range XFD2 End xlToLeft Select finds the last used column in row 2 For rw LastRow To 1 Step 1 xfd is the last column in excel 2007 If ActiveCell Offset 7 0 Value _ Sheets Simplified Data Range A65536 End xlUp Value Then check if meas date amp time MsgBox Same Measurement Date Time Please check has match then prompt message Else else accept the data be paste row by row ActiveCell Offset 10 3 Value 2 valid 64 Sheets Simplified Data Range B65536 End xlUp Offset 1 O Value _ ActiveCell Value Find the first empty row and place Fuel Type value Sheets Simplified Data Range C65556 End xlUp Offset 1 0 Value _ ActiveCell Offset 1 0 Value ID number Sheets Simplified Data Range D65536 End xlUp Offset 1 0 Value _ ActiveCell Offset 2 0 Value operator name Sheets Simplified Data Range E65536 End xlUp Offset 1 O Value _ ActiveCell Offset 3 0 Value temperature Sheets Simplified_Data Range J65536 End xlUp Offset 1 0 Value _ ActiveCell Offset 4 0 Value Sheets Simplified Data Range K65556 End xlUp Offset 1 0 Value _ ActiveCell Offset 5 0 Value Sheets Simplified Data Range L65536 End xlUp Offset 1 0 Value _ ActiveCell Offset 6 0 Value Sheets Simplified Data Range A65536 End xlUp Offset 1 O Value _ ActiveCell Offset 7 0 Value Find the first empty row for meas d
26. 0 Figure 51 Figure 52 Figure 53 Table 1 Snippet 1 Snippet 2 Snippet 3 Searching the BC Device User Form User Form for Searching DSC database Searching the IDM for DSC DSC Opened Measurement in IDM Open window DSC measured data in text file format Get Data Form Raw Data for Rheometer Simplified Raw Data for Rheometer Search Form for Viscosity Shear Points Get Data Viscosity Shear Rate based on Meas Points Textbox Year Kemikaalikortisto Worksheet Main Search File Command Button Open Database Form Main Search User Form Main Search Result Page Titration Import Acid Numbers Properties Titration Search Malvern Mastersizer Search Form Rancimat Search Form CRU Search Form Range Find Properties Control Buttons Code Command Button Connect Connect Device Code 552 53 wD 58 60 60 61 63 63 65 66 67 73 73 73 74 75 78 79 80 81 82 82 83 34 29 30 31 Snippet 4 Snippet 5 Snippet 6 Snippet 7 Snippet 8 Snippet 9 Snippet 10 Snippet 11 Snippet 12 Snippet 13 Snippet 14 Snippet 15 Snippet 16 Snippet 17 Snippet 18 Snippet 19 Snippet 20 Snippet 21 Snippet 22 Snippet 23 Snippet 24 Snippet 25 Snippet 26 Snippet 27 Snippet 28 Snippet 29 Snippet 30 Snippet 31 Code for Import Command Button Selected File Code Import File Code Ge
27. 13 99 19 6 Dy auto 5 15 4 18 7 0 448 02 14 5 22 8 Dy auto 18 8 7 22 1 0 526 O 0238 217 3 27 9 Dy auto 7 22 1 25 4 O 607 0 0239 29 7 32 3 Dy auto I 28 8 6 7 0 0239 22 3 26 5 Dy auto 28 8 10 32 1 0 766 O 0238 24 3 40 7 Dy auto 10 32 2 11 35 5 0 345 02 38 27 5 44 9 Dy auto 25 6 G S 27 02 20 1 49 2 Dy auto 12 38 9 gt a Sheets Copied Simplified Data gt Cloud Pour Sheet2 Figure 37 Raw Data for Rheometer Macro was created so that whenever added measurements to a Sheet e g 2011 it also automatically adds to a simplified format each measured data will be added to the row one measurement per row Figure 38 shows that each property is now in columns and measurements are in row The code for this function is shown in Snippet 26 B C E F 1 Measurement Date Time Name Fuel Type Sample ID Temperature 2 18 8 2010 9 59 vegetable oil 10040 JH sekoituksesta 35 min 50 3 4 10 2010 13 50 other 2 10038 JLE directly from thermos 50 4 18 10 2010 12 07 vegetable oil 1 10057 4l area 0 01 200 1 5 S0point 25 9 5 18 10 2010 12 48 vegetable oil 2 10057 HI area 0 01 50 1 s 90points 25 90 Figure 38 Simplifued Raw Data for Rheometer Sub PutToSimplifiedData Dim i rw LastRow NextRow As Long NextRow Range A65536 End xlUp Row 1 last used row in column A Cells NextRow 1 ActiveCell Offset 7 0 Value LastRow Range A65536 End
28. 3 Value And Range K amp CStr BCRow Value lt Range BCStart AH4 Value And _ Range J amp CStr BCRow Value gt Range BCStart AJ3 Value And Range J amp CStr BCRow Value lt Range BCStart AJ4 Value Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False ID number MDate and GHV Elself UserForm4 CheckBoxl Value True And UserForm4 CheckBox2 Value True _ And UserForm4 CheckBox3 Value True And UserForm4 CheckBox4 Value False Then DateM UserForm4 TextBox1 Value DateM2 UserForm4 TextBox2 Value For i 0 To UserForm4 ListBox1 ListCount 1 If UserForm4 ListBox1 Selected i And _ Range A amp CStr BCRow Value CStr UserForm4 ListBox1 List i And _ Range K CStr BCRow Value gt Range BCStart AH3 Value And _ Range K amp CStr BCRow Value lt Range BCStart AH4 Value _ And Range B amp CStr BCRow Value gt CDate DateM And _ Range B amp CStr BCRow Value lt CDate DateM2 Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Next i 48 Unhide row if measurement date from column are between the and max date given Elself UserForm4 CheckBoxl Value False And UserForm4 CheckBox2 Value True _ And UserForm4 CheckBox3 Value False And UserForm4 CheckBox4 Value False Then DateM UserForm4 TextBoxl Value DateM2 UserForm4 TextBox2 Value If Range B amp CStr BCRow Value gt CDate DateM
29. 53 Sub Clr If Range DSCResult B1 Value 0 Then if no values before search Exit Sub exit this function Else else if there are measurements then clear Sheets DSCResult Activate Range C3 FY28 ClearContents Clear the DSC Result Page End If End Sub Snippet 15 Clear Function This function for searching the DSC is similar to Snippet 14 for the Bomb Calorimeter except that there is additional function shown in Snippet 15 This function clears the Result Page for the previous search done by the user The reason is for the user to look at the real measurements and not overwriting from previous search Private Sub CommandButton8_Click Exit button in search form UserForml Hide Hide the search user form End Snippet 16 Exit Function Exit Command Button has a function shown in Snippet 16 which closes the user form Closing the user form is set to Hide and to open a form is set to Show 54 Searching Form for DSC Figure 30 Searching the IDM for DSC Figure 30 shows the user form to search the data from IDM these needs to connect to the IDM first list all the files and select file to view This list box accepts only single selection Sub CopyALLPDF This function filters all the pdf files and copy paste to another sheet destination Sheets Sheet2 Select Range B2 B600 ClearContents Sheets Destination frm IDM Select With Sheets Destination frm IDM AutoFilterMode False
30. Hydrogen Connect to BC device Select column of Date amp Time user or Mositure that no Content given value Message to user that Is device device ON must be ON Given NO YES YES Copy the list of files Moist Content Add Data to Raw Data Bring it to Listbox YES Calculate formula 1 Select report from list Open the report into Given worksheet Hydrogent Calculate formula 2 GET THE PROPERTIES Calculate Heat Value All3 parameters Activate worksheet with the report YES Find all the necessary Calculate formulas properties needed 12 amp 3 Bring to User Form Text Box Figure 12 Flowchart for Importing in Bomb Calorimeter 22 This flowchart is for Search function of Bomb Calorimeter search can be from Raw data or from Device Open the user form SEARCH THIS BC FILE Connect to BC Device Declare variables Is device Message to user that ON device must be Clear the Result Page ON Select Sheet amp Range Copy the list of file Hide the Rows Bring to listbox Get which checkbox is are selected Select from the list Get what in the listbox are selected Open report to worksheet Unhide Rows of matching data Copy Paste to Result Page End of Search Figure 13 Flowchart for Searching Bomb Calorimeter 23 CHECK DSC NO Kemikaalikortisto Check the DSC checkbox Open from IDM file open open Activate the file Get t
31. R AT E 27 5 3 4 Use Case for Main Searcb aec one oeste edic qe tec aci 28 6 THE IMPLEMENTATION amp RESULT uoto eee die 29 6 1 Bomb Calorimeter Application iet er ean idee Eee ei 29 6 1 1 File Soe detis 30 6 1 2 Getting Necessary Properties qu epe beaten vee Bae 38 6 1 3 Heat Values Calculation code to e oo a a 42 6 1 4 Check Duplicate before Adding 44 6 1 5 Addo Databases S ea d A es 45 6 1 6 searching the Bomb Calorimeter 46 62 DSC Applications s odd eee dte ien eee ca eed oats 53 6 2 1 Search i oa ae ceo en reden boue fedus ene 53 6 2 2 DSC IMPORT ie UR URS ER A RU LU I UARIIS ers 59 6 2 3 DSC Get Add Cancel ieu meg ll ee 61 6 3 RHEOMETER APPLICATION gt deve 63 6 3 1 Adding Measurement Data 63 6 3 2 Searching the eager 66 6 3 3 Additional Measuring Points Get Data seen 66 6 4 MAIN SEARCH APPLICATION is encuentra rbi ue i p RM 74 o COTHER APPLEICATIONS beiden En ONE VM ats bia eiu 80 6 5 1 80 6 5 2 82 T7 CONCLUSION V AREE SANA S FLU AAT MO TAS PER Sese Ugo eta uses 85 pod aestu us a M pi
32. Up Offset 1 0 Value UserForm2 TextBox10 Value MSystem Range E65536 End xlUp Offset 1 0 Value UserForm2 TextBox11 Value Range S65536 End xlUp Offset 1 0 Value UserForm2 TextBox25 Value Range T65536 End xlUp Offset 1 0 Value UserForm2 TextBox26 Value 1 set of measuring points with corresponding shear rate and viscosity values format decimal values Range G65536 End xlUp Offset 1 0 Value Range Sheet1 C2 Value Range G65536 End xlUp Offset 1 0 Value Format Range G65536 End xlUp Offset 1 0 Range H65536 End xlUp Offset 1 0 Value Range Sheet1 A2 Value Range H65536 End xlUp Offset 1 0 Value Format Range H65536 End xlUp Offset 1 0 Range I65536 End xlUp Offset 1 0 Value Range Sheet1 B2 Value Range I65536 End xlUp Offset 1 0 Value Format Range I65536 End xlUp Offset 1 0 4 2nd set of measuring points with corresponding shear rate and viscosity values format decimal values Range J65536 End xlUp Offset 1 0 Value Range Sheet1 C3 Value Range J65536 End xlUp Offset 1 0 Value Format Range J65536 End xlUp Offset 1 0 Range K65536 End xlUp Offset 1 0 Value Range Sheet1 A3 Value Range K65536 End xlUp Offset 1 0 Value Format Range K65536 End xlUp Offset 1 0 Range L65536 End xlUp Offset 1 0 Value Range Sheet1 B3 Value
33. Value UserForm2 TextBox14 Value UserForm2 TextBox2 Value UserForm2 TextBox15 Value UserForm2 TextBox3 Value UserForm2 TextBox16 Value UserForm2 TextBox4 Value End Sub Snippet 28 GetTheResultToPage2 function Snippet 29 in the next page finds for matching measurement from different worksheets except those that are mentioned in the code 69 Sub Find DataRheom this function finds the meas date from selected list of resultin user for This search all the worksheets except the given worksheet names Dim MeasDate declare variables Dim sheetNumber countSheet currentSheet As Integer On Error Resume Next currentSheet ActiveSheet Index MeasDate UserForm2 ListBox2 Value selected from the listbox sheetNumber Active Workbook Sheets Count search inside sheet in row 9 from first column to last column which is XFD With This Workbook Worksheets countSheet Range A9 XFD9 Select For countSheet 1 To sheetNumber first sheet to last sheet found if sheets name are one of this then skip the sheet If Sheets countSheet Name Cloud Pour _ Or Sheets countSheet Name StartSheet _ Sheets countSheet Name _ Or Sheets countSheet Name Sheet2 _ Or Sheets countSheet Name Simplified_Data _ Or Sheets countSheet Name _ Cloud_PourPoint _ Or Sheets countSheet Name _ Or Sheets countSheet Name Sheet3 _ Or Sheets countSheet Name Copied Then Exit Sub Else else ac
34. ate and place value Sheets Simplified Data Range H65536 End xlUp Offset 1 0 Value _ ActiveCell Offset 8 0 Value Find the first empty row for meas system and place value Sheets Simplified Data Range O65536 End xlUp Offset 1 0 Value _ ActiveCell Offset 9 0 Value Sheets Simplified Data Range I65536 End xlUp Offset 1 0 Value _ ActiveCell Offset 10 0 Value Sheets Simplified Data Range G65536 End xlUp Offset 1 0 Value _ ActiveCell Offset 36 0 Value Sheets Simplified_Data Range F65536 End xlUp Offset 1 0 Value _ ActiveCell Offset 31 0 Value Find the first empty row for meas points and place value End If Exit Sub Next rw do next End Sub Snippet 26 Simplifying added measurements This code simplifies or reformats the arrangement of the measurement added to the sheets Measurement date and time is checked for duplicate When the seventh Row of the new added data measurement date and time 15 similar to the last used row of column A of the sheet name Simplified Data then prompt a message to user of the duplicate If unique data then this function copy all the important properties to the Simplified Data using offset function It is set that the active cell be from the second row of the last column which is in MS Excel 2007 set to be column XFD 65 6 3 2 Searching Rheometer This searching function for Rheometer has also two options either to search the v
35. cial Paste xlAll Operation xlNone SkipBlanks _ False Transpose False paste as it is Sheets BCCopyingSheet Activate Application CutCopyMode False Workbooks report cgi Close False close report without changes Application ScreenUpdating True End Sub Snippet 7 GEtTHeReport Snippet 7 opens the report and copies it into the WorkSheet named BCCopyingSheet from the first row of column A The paste method is set in a way that copied data will be pasted as it is 36 Figure 22 Original Report of Selected File shows that the report is opened as web document and after running various macros the document is now opened in worksheet given in next page Parr 6400 Calorimeter Report Windows Internet Explorer P ni x y io http 10 162 26 8 cgi bin report cgi 100 File Edit Favorites Tools Help 9h Parr 6400 Calorimeter Report 6400 Calorimeter Report Sample ID 10036 1 1 Mode Determination Type Preliminary Date Time 07 23 10 13 38 59 Sample Weight 0 5604 Method Dynamic Spike Weight 0 1108 Bomb ID 1 Fuse 50 0000 EE Value 931 8425 Acid 8 0000 Sulfur 0 0000 Jacket Temperature 29 9519 Initial Temp 29 9126 Temperature Rise 6 6831 Gross Heat Run List Home OOo OSO anra 100 Figure 22 Original Report of Selected File 37 ro C4 6400 Calorimeter R
36. d le 86 APPENDICES LISTS OF PICTURES GRAPHS AND TABLES Figure 1 Figure 2 Figure 3 Figure 4 Figure 5 Figure 6 Figure 7 Figure 8 Figure 9 Figure 10 Figure 11 Figure 12 Figure 13 Figure 14 Figure 15 Figure 16 Figure 17 Figure 18 Figure 19 Figure 20 Figure 21 Figure 22 Figure 23 Figure 24 Figure 25 Figure 26 Figure 27 VBE User Form VPN BC device Malvern Mastersizer device Rheometer device Titration device DSC device Rancimat device CRU device Access of computers Flowchart for Importing in Bomb Calorimeter Flowchart for Searching Bomb Calorimeter Flowchart for Main Search Bomb Calorimeter Use Case Diagram Rheometer Use Case Diagram DSC Use Case Diagram Main Search Use Case Diagram Bomb Calorimeter Form Control Buttons BC Import File Connect BC List of files Original Report of Selected File Copying Worksheet Data Transfer to Database Heat Values Searching the BC Database User Form Bomb Calorimeters Result Page 13 13 14 15 10 10 17 17 18 18 221 22 23 24 25 26 27 28 29 30 32 37 38 41 42 46 51 Figure 28 Figure 29 Figure 30 Figure 33 Figure 34 Figure 35 Figure 36 Figure 37 Figure 38 Figure 39 Figure 40 Figure 41 Figure 42 Figure 43 Figure 44 Figure 45 Figure 46 Figure 47 Figure 48 Figure 49 Figure 5
37. e False _ And UserForm4 CheckBox3 Value False And UserForm4 CheckBox4 Value True Then For i 0 To UserForm4 ListBox1 ListCount 1 If UserForm4 ListBoxl Selected i And _ Range A amp CStr BCRow Value CStr UserForm4 ListBox1 List i _ And Range J amp CStr BCRow Value gt Range BCStart AJ3 Value And _ Range J amp CStr BCRow Value lt Range BCStart AJ4 Value Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Next i UserForm4 CheckBoxl Value True And UserForm4 CheckBox2 Value False _ And UserForm4 CheckBox3 Value True And UserForm4 CheckBox4 Value False Then For i 0 To UserForm4 ListBox1 ListCount 1 If UserForm4 ListBox1 Selected i And _ Range A amp CStr BCRow Value CStr UserForm4 ListBox1 List i _ And Range K amp CStr BCRow Value gt Range BCStart AH3 Value And _ Range K amp CStr BCRow Value lt Range BCStart AH4 Value Then Unhide row if match value from Row A amp K which are rows for Sample and GHV respectively Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Next i Unhide row if match value from Row J and Row which are rows LHV and GHV respectively Elself UserForm4 CheckBoxl Value False And UserForm4 CheckBox2 Value False _ And UserForm4 CheckBox3 Value True And UserForm4 CheckBox4 Value True _ And Range K amp CStr BCRow Value gt Range BCStart AH
38. e application for the entire device was ready This last file is used for searching the seven database files Results display the list of all properties of the sample from different devices into one worksheet Laboratorian can compare how the samples properties change depending on the time interval or see how one samples properties within different device This makes it easier for them to see if the sample has a very high or very low value in specific properties that may cause had caused problem to specific power plants The main target of this project is the fuel laboratory team but this project is saved in IDM so that can be accessed by everyone inside the company using a LAN connection or VPN connection Keywords MS Excel VBA IDM LAN User Form VPN ABBREVIATION VBA VBE VPN IDE PPT LAN IDM CGI PDF HTTP LHV NHC HHV GHC HTML API CRU DSC Visual Basic for Application Visual Basic Editor Virtual Private Network Integrated Development Environment Power Plant Technology Local Area Network Integrated Document Management Common Gateway Interface Portable Document Format HyperText Transfer Protocol Lower Heat Value Net Heat of Combustion Higher Heat Value Gross Heat of Combustion HyperText Markup Language Application Programmable Interface Combustion Research Unit Differential Scanning Calorimetry CONTENTS ACKNOWLEDGEMENT ABSTRACT ABBREVIATION L INTRODUCTION iens teoria rH
39. eckBoxl Value True And UserForm4 CheckBox2 Value True _ And UserForm4 CheckBox3 Value True And UserForm4 CheckBox4 Value True Then DateM UserForm4 TextBoxl Value Min Date DateM2 UserForm4 TextBox2 Value Date For i 0 To UserForm4 ListBox1 ListCount 1 Multiselect Sample Name List If UserForm4 ListBoxl Selected i And Range A amp CStr BCRow Value _ CStr UserForm4 ListBoxl List i And Range K amp CStr BCRow Value gt _ Range BCStart AH3 Value And Range K amp CStr BCRow Value lt _ Range BCStart AH4 Value And Range J amp CStr BCRow Value gt _ Range BCStart AJ3 Value And Range J amp CStr BCRow Value lt _ Range BCStart AJ4 Value And Range B CStr BCRow Value gt CDate DateM _ And Range B amp CStr BCRow Value lt CDate DateM2 Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Next i 50 End If BCRow BCRow 1 Loop Sheets BCRawData Select Rows 6 1000 SpecialCells xlCellTypeVisible Copy Copy only visible rows Sheets BCResult Activate Range C3 Select Selection PasteSpecial Paste xlValues Operation xlNone SkipBlanks _ False Transpose True Paste to result page worksheet Worksheets BCRawData Activate Rows 6 1000 EntireRow Hidden False show the entire row in Raw data Sheets BCResult Select MsgBox Complete Message to user when successful UserForm4 Hide Hide the fo
40. eenUpdating False TextBoxl Value Range TextCopy AB1 Value 5 Name from range to textbox TextBox2 Value Range TextCopy AB2 Value Meas Date from range to textbox TextBox3 Value Range TextCopy AB8 Value TextBox4 Value Range TextCopy AB4 Value initial temp TextBox5 Value Range TextCopy AB5 Value temp TextBox6 Value Range TextCopy AB6 Value Heating rate TextBox7 Value Range TextCopy AB7 Value TextBox8 Value Range TextCopy AB3 Value TextBox9 Value Range TextCopy AA2 Value Meas Date Time to textbox End Sub Snippet 24 GetDataDSC Get Data is the function to get the properties from the file that was just imported into the textbox of the user form 62 Sub CopyData function that that get the necessary properties Application ScreenUpdating False copySmID Find Sample Name ID number string go to next column to get the value copyMDte Find Meas Date string go to next column to get the value copySWeight Find Sample Weight string go to next column to get the value copyAtmosphereitrogen Find atmosphere copyAtmosphereO copyHeatfrm find heat End Sub Snippet 25 Copy Data Snippet 25 is the functions that get the necessary properties Add to database function is similar in Bomb Calorimeter Duplicate checking is also necessary before adding data to database which is done by checking the measurement date and time 6 3 RHEOMETER APPLICATION Rheomet
41. eight and copy corresponding value into range BCDate for Measurement Date BCSpikeWeight for Spike weight BCGHeat for Gross Heat HHV BCTempRise for Temperature Rise BCInTemp for Initial Temperature EEValue for EE Value Application ScreenUpdating False End Sub Sub EEValue Look for the EEValue and get the corresponding value on next column Sheets BCCopyingSheet Select Range A1 E50 Select Range where to find Selection Find What EE Value After ZActiveCell LookIn xlFormulas _ LookAt zxlPart SearchOrder xlByRows SearchDirection 2xlNext _ MatchCase False Activate activate when found ActiveCell Offset 0 1 Copy copy value from next column Sheets Runlist BombCalorimeter Activate Range AF15 Select Paste in this Sheet Selection PasteSpecial Paste xl Values Operation xlNone SkipBlanks _ False False Paste only values End Sub Sub BCSName Look for the Sample ID Sheets BCCopyingSheet Select Range A1 E50 Select Selection Find What Sample ID After ZActiveCell LookIn xlValues _ LookAt zxlPart SearchOrder xlByRows SearchDirection 2xlNext _ MatchCase False Activate ActiveCell Offset 0 1 Copy Sheets Runlist BombCalorimeter Activate Range AF3 Select Selection PasteSpecial Paste xl Values Operation xlNone SkipBlanks _ False Transpose False End Sub Sub BCSWeight Look for the Sample Weight Sheets BCCopyi
42. eport Sample ID 10036 1 1 Mode Determination Preliminary Date Time 23 07 2010 13 38 Sample VvVeight 0 5604 Method Dynamic Spike Weight 0 1108 Bomb 10 1 Fuse 50 EE Value 931 8456 Acid 8 Sulfur o Jacket Temperature 29 9519 Initial Temp 29 9126 Temperature Rise 6 6831 Gross Heat 42 3736 MJ kg Figure 23 Copying Worksheet Worksheet in where the measurement selected from the previous task is now opened This sheet will be used to get the necessary properties to be saved in this Bomb Calorimeter database 6 1 2 Getting the Necessary Properties Measurement from Figure 23 has too many properties and only specific properties discussed during the requirements analysis is needed to be saved into the Excel based database of this Bomb Calorimeter Command Button Next Step runs a macro that automatically activates the next page of this User Form and gets all the values of the properties that are needed Private Sub CommandButton4_Click Application ScreenUpdating False disable user to see what the macro is doing MultiPagel Value 1 Go to the user forms page 2 SelecttheDataFrmImport function to take necessary properties into range GetDataBC function bring properites into the user form textbox End Sub Snippet 8 Command Button Next Step 38 Sub SelecttheDataFrmImport BCSName function that search the Sample Name and copy corresponding value into range BCSWeight function that search the S W
43. er needs to have an automatic reformatting of added measurements Search functions consist of two search viscosity or search for cloud point pour point 6 3 1 Adding Measurement Data Measurements added to this database are copied exactly same format from the Rheometer PC 7 columns data which include a large amount of numbers or data points in each measurement Figure 37 The Sheets names are the year of measurements 63 A G H 1 Data Series Information i Data Series Informai Name vegetable oil Name Sample 10040 mmm Operator JH sekoituksesta 35 min Operator Remarks 50 Remarks Number of Intervals 3 Number of Intervals Application RHEOPLUS 32 V 3 40 21004284 33024 Application Device MCR301 5 80549192 FW 3 40D090210 Slo Device Measuring Date Time 18 8 2010 9 59 Measuring Date Tim Measuring System CC27 SN18033 d 0 mm Measuring System Accessories TU1I C PTD200 SNS8054739352 Accessories Interval 2 Interval Number of Data Points Number of Data Poir Measuring Profile Measuring Profile Shear Rate a gamma dat 2 1 s lin Shear Rate Meas Pts Shear Rate Shear Stre Viscosity Speed Torque Status Meas Pts Shear Rat 12 51 Pal Pa s 1 rnin t1 1 5 m 0 0475 0 0238 1 55 2 52 Dy auto 8 2 2 gt 5 13 0242 4 15 6 88 Dy auto 2 5 35 3 7 207 02 8 6 74 11 Dy auto 3 a a 2 02 2 34 15 3 Dy auto E 12 3 5 15 4 024
44. ew to Power Plant Technology PPT team and this project is challenging during the requirements analysis There were plenty of idea on what and how to do the application Important details such as what parameters are needed for adding to the database and in that case what needs to be excluded Since all parameters are very important for every measurements taking away one isn t an easy decision This project is important especially for fuel laboratory team to check and compare what kind of fuel samples they had measured the past few months years They now can see the trend of fuel types or some special power plants and easily can check if there is special problem that may occur occurred based on the samples measured The last part of this project is the testing and educating the laboratory team wherein we run and test the program together The requirements of this project were successfully achieved and the application is being used More measurements are added to the database all the time There are plenty of new terms new devices that is worth knowing during the process of this project to understand the whole laboratory environment The application is being used and more measurements are added every now and then The main target of this project is the fuel laboratory team but this application is saved in IDM where all internal workers can have an access depending to their user rights 85 8 REFFERENCE 1 ParitoLogic Inc 2011 F
45. filter the range where list of pdf files are located Range C14 C300 AutoFilter Field 1 Criterial pdf End With Range C15 C amp Range C65536 End xlUp Row SpecialCells xlCellType Visible Copy Sheets Sheet2 Activate copy from the last used all visible cells Range B2 PasteSpecial Paste xlValues Operation xlNone SkipBlanks _ False Transpose False paste values End Sub Snippet 17 Copy list of pdf files 55 The function for connect to is similar to Snippet 3 of Bomb Calorimeter except that the file format that is listed are pdf files shown in Snippet 17 There are empty rows with the use of Autofilter mode makes it easier to filter only the list that is in pdf format located in Column C Sub FilterPDF basedOnID This function filters the pdf files based on the given ID number in the userform textbox On Error GoTo DSC_Error Application ScreenUpdating False disable updating screen when macro runs Dim DSCStartRow As Integer Dim IDNumber As String DSCStartRow 2 IDNumber UserForm1 TextBox10 Value Textbox in userform where user put the ID number Pdf files consist of ID numbers Sheets Sheet2 Activate This is the sheet where list of files are temporarily located Column is list of ID numbers from the file name Column is real name listed from IDM column C are list of trimmed filename where spaces before the name are removed Range Sheet2 A2 C300 EntireRow Hidden
46. fore saving the measurements to the database Figure 25 Heat Values Calculation for the HHV or LHV needs the Moisture Content as analysis M4 Hydrogen Content 96 or Moisture Content that arrived Mar HHV dry Measured Value 100 100 M 1 LHV dry HHV 0 2122 H 2 LAV arrived dry 100 100 0 02441 Mar 3 42 Private Sub CommandButton 1 1 Click Message to user that there isn t any values if all three properties are missing If TextBox12 Value And TextBox13 Value And TextBox15 Value Then MsgBox There is t any value either for Hydrogen or Moisture Content Tf only moisture content is given then formula 1 is used Elself Not TextBox15 Value And TextBox12 Value Then TextBox16 Value TextBox8 Value 100 100 TextBox15 Value only HHV dry TextBox14 Value Cancel values of LHV dry LHV arr TextBox17 Value Label52 Caption Tf Hydrogen content given then formula 2 Elself Not TextBox12 Value And TextBox15 Value Then TextBox14 Value TextBox Value 0 2122 TextBox12 Value Label52 Caption lt water is neglictable additional label shows when this is used if both moistures are present then use all 3 formulas one after another Elself Not TextBox12 Value And Not TextBox15 Value And Not TextBox13 Value Then TextBox16 Value TextBox8 Value 100 100 TextBox15 Value TextBox14 Va
47. ge BCRawData L7 Value TextBox9 Value Range BCRawData M7 Value TextBox10 Value Range BCRawData N7 Value TextBox11 Value Range BCRawData O7 Value TextBox18 Value Range BCRawData P7 Value TextBox15 Value Added values Range BCRawData Q7 Value TextBox17 Value Range BCRawData R7 Value TextBox16 Value Range BCRawData S7 Value TextBox19 Value MsgBox Suceessfully added End Sub 45 Snippet 13 Add Data to Database 6 1 6 Searching the Bomb Calorimeter This Bomb Calorimeter database has two option for searching the measured samples one is to search the database excel file based on the given criteria s e g Sample Name Measurement Date Gross Heat Value and Net Heat Values The user can define any combination based on what checkbox are selected Second option is to connect to the Bomb Calorimeter Device and open the measurements with the reference to the filename saved and open it one at a time Searching Form Figure 26 Searching the BC Database User Form Figure 26 shows the Form for the first option Each criteria s have their own checkbox When the checkbox is selected for example with the Sample Name ID number criteria the list of sample Name or ID number will be listed for the user to select one or more 46 This function is combination of Do While Loop Statement for Next Statement and If Else statement Few possibilities were tested first used to filter the ma
48. he list of fuel types into YES ive the 3 last ber of Listo hep files located in DSC Open sheet based on selected year folder from IDM Select single multiple fuel types YES DSC files Select single file from the list Put into Listbox all ID numbers that match the fuel types from kemikaalikortisto Put into Listbox all Working numbers from given year Select ID number and or working numbers NO Are database Open from IDM files open ES Activate and search one by one based on selected numbers Get only the Valid Results Open Result Page OPEN file Figure 14 Flowchart for Main Search Main Search connects to IDM and opens eight files including the kemikaalikortisto to get the fuel types 24 5 3 Use Case Diagram This use case diagram is done with the software Borland Together 5 3 1 Use Case for Bomb Calorimeter Two main function import and search for the user There are other functions included to the main functionalities that cannot be discarded Import needs the connection to the device get necessary properties select the measurements to be imported calculate heat values and add to raw data Select file to Connect to import device properties Calculate Heat Values Importfile Add to Raw Check User Data Duplicate Search data from device Search Data En from Raw Data 4 Select Criteria Figure 15 Bomb Calorimeter Use Case Diagram
49. ile Extension CGI NCSA online accessed April 2011 available in www form URL http file extension paretologic com detail php File Extension cgi 2 Microsoft Technet 2003 Intranet based VPN Updated January 2005 online Accessed April 2011 Available in www form URL http technet microsoft com en us library cc784305 WS 10 aspx gt 3 Hoboken NJ USA 2008 Marmel Elaine Master Visually Excel 2007 e book Accessed April 2011 Available in www form lt URL http site ebrary com lib vamklibrary docDetail action docID 10296326 amp p00 excel gt 4 Boston Ma USA 2005 Birnbaum Duane Microsoft Excel VBA Programming for the Absolute Beginner e book Accessed April 2011 Available in www form lt URL http site ebrary com lib vamklibrary docDetail action docID 10082085 amp p00 visual 20basic 20application 20excel gt 5 Hoboken NJ USA 2005 Held Gilbert Virtual Private Networking A construction Operation and Utilization Guide e book Accessed April 2011 Available in www form lt URL http site ebrary com lib vamklibrary docDetail action docID 10113984 amp p00 virtua 1 20private 20networking gt 6 Suopanki Helena 2009 Laboratory equipment presentation PPT W rtsil Finland 7 W rtsil website accessed October 2010 Available in www form URL http wartsila com en about company management overview gt 8 Tech on the Net 2003 201 accessed June 2010 Available at www for
50. ing Criteria Select one measurement from the list before procceding to step below Measurement Date Time Type Operator era 18 8 2010 9 59 vegetable oil JH sekoituksesta 35 50 4 10 2010 13 50 other 2 10038 ILE directly from thermos 50 d gamma dt EXT x Min value must be 5 Give the number of Points to see what are the corresponding Viscosity NOTE Always select ONE measurement from the above list To get the Shear Points 2 80 70 90 minimum must be 5 and maximum must be looked at the above list in column MEASUREMENT POINTS Figure 40 Shear Points Get Data Message Box appears in the Figure 40 this is for the reason that minimum point given is 2 which is the limit of 5 Private Sub CommandButton1_Click If TextBoxl Value lt 5 Then Check for min meas points given MsgBox Min value must be 5 TextBox1 Value Else If min is gt 5 Range Sheet1 C2 Value UserForm2 TextBox1 Value control source for 1 point Range Sheet1 C3 Value UserForm2 TextBox2 Value control source for2nd point 67 Range Sheet1 C4 Value UserForm2 TextBox3 Value control source for 3 Range Sheet1 C5 Value UserForm2 TextBox4 Value control source ge point UserForm2 TextBox25 Value Range Sheet1 D1 Value UserForm2 TextBox26 Value Range Sheet1 E1 Value Find DataRheom Look for matching measurements from selected result MultiPagel
51. iscosity or the Cloud Point In Figure 39 we can see that there are almost similar parameters as the previous device search functions too but there are additional functions in this application Searching Form x Search the Raw Data Sample ID j Name Fuel Type Measurement Date Valid Measurement Start Date vegetable oil other 2 E 01 01 2009 vegetable oil 1 vegetable oil 2 liquide like solid 1 End Date vegetable oil 3 vegetable oil 4 10 10 2010 10109 vegetable oil 5 10106 vegetable oil 6 10107 emulsion 1 10108 emulsion 2 10114 emulsion 3 10113 emulsion 4 emulsion 5 Figure 39 Search Form for Viscosity 6 3 3 Additional Measuring Points Get Data After searching based on the combination of criteria such as ID numbers Fuel Types Measurement Date and or valid values another user form will display The user has to select single measurement from the results of the previous search function and give the measuring points values This additional function is done because there are plenty of measuring points in every measured data 66 Example in Figure 40 shows that in column 6 indicates that the measuring points for that particular sample is 90 and the program needs only to get the corresponding viscosity of only four measuring points Result Page E Parameters Result Searched_Data l r Match
52. istBoxl Selected i And _ Range A amp CStr BCRow Value CStr UserForm4 ListBox1 List i And _ Range K amp CStr BCRow Value gt Range BCStart AH3 Value And _ Range K amp CStr BCRow Value lt Range BCStart AHA Value _ And Range J amp CStr BCRow Value gt Range BCStart AJ3 Value And _ Range J amp CStr BCRow Value lt Range BCStart AJ4 Value Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Next i Checkboxes for Meas Date GHV LHV are set to true UserForm4 CheckBox1 Value False And UserForm4 CheckBox2 Value True _ And UserForm4 CheckBox3 Value True And UserForm4 CheckBox4 Value False Then DateM UserForm4 TextBoxl Value Set the min date DateM2 UserForm4 TextBox2 Value max date Row K is for Gross Heat B for Measurement Date J for LHV If Range K amp CStr BCRow Value gt Range BCStart AH3 Value And _ Range K amp CStr BCRow Value lt Range BCStart AH4 Value _ And Range B amp CStr BCRow Value gt CDate DateM And _ Range B amp CStr BCRow Value lt CDate DateM2 And _ Range J amp CStr BCRow Value gt Range BCStart AJ3 Value And _ 47 Range J CStr BCRow Value lt Range BCStart AJ4 Value Then Rows CStr BCRow amp amp CStr BCRow EntireRow Hidden False End If Sample Name and LHV are selected UserForm4 CheckBoxl Value True And UserForm4 CheckBox2 Valu
53. lected file is and look for the link Sub SelectV Dim CellV As String CellV Range Runlist BombCalorimeter S 1 Value control source for list box Sheets Runlist_BombCalorimeter Select Range A1 A65536 Select Selection Find What CellV After ActiveCell LookIn xlValues _ LookAt xlPart SearchOrder xlByRows SearchDirection xlNext _ MatchCase False Activate find matching file from selected in list ActiveCell Offset 0 1 Copy copy the link in next column Range Runlist_BombCalorimeter S2 Select Selection PasteSpecial Paste xlValues Operation xlNone SkipBlanks _ False Transpose False paste the link to another cell Application ScreenUpdating False End Sub Snippet 5 Selected File Code 33 The Range Find Method has syntax Expression Find What After LookIn LookAt SearchOrder SearchDirection MatchCase MatchByte SearchFormat Expression is the variable that represents the Range object which used as the selection Range 1 65536 in Sheets Runlist BombCalorimeter Properties are described in Table Range Find Properties which was taken from the excel help developer reference 9 Table 1 Range Find Properties NAME REQUIRED DATA TYPE DESCRIPTION OPTIONAL What Required Variant Data to search for After Optional Variant The cell after which you want the search to begin This corresponds to the position of the active cell when the sea
54. lue TextBox16 Value 0 2122 TextBox12 Value TextBox17 Value TextBox14 Value 100 TextBox13 Value 100 0 02441 TextBox13 Value Label52 Caption End If End Sub Snippet 11 Command Button Calculate The formula follows the process if given only the Moisture Content as analysis M then use formula 1 if only given Hydrogen Content H use formula 2 if both are given then use the formula 1 and whatever result got using 1 will be used to calculate the formula 2 While in the case that all three are given including the Moisture Content that arrived Max formula will use the step from 1 to 2 and the outcome will be then used for calculating the formula 3 43 6 1 4 Check Duplicate before Adding There are samples that are measured several times in period of time which makes multiple appearance of the sample name or ID number except for measurement date amp time Private Sub CommandButton5_Click Sheets BCRawData Activate CheckDateAndTime function to check duplicate measurement date and time End Sub Sub CheckDateAndTime On Error GoTo Ex Dim DTime As String declare the value of DTime to string Worksheets BCRawData Activate DTime UserForm2 TextBox19 Value Set DTime as the textbox value With Range S6 S65536 Select find in column S if there is match to DTime Selection Find What DTime After ActiveCell LookIn xlValues _ LookAt xlPart SearchOrder
55. m lt URL http www techonthenet com excel macros search_for_string php gt 9 Microsoft Excel 2007 Developer Reference accessed April 2011 Available in www form URL http msdn microsoft com en us library bb 178845 28v office 12 29 aspx gt 86 APPENDIX 1 Access to VPN Start gt SONAD gt Cisco System VPN Client 2 VPN Client status Disconnected VPN Client Version 4 8 00 0440 bana3 wartsila com bana4 wartsila com bana2 wartsila com bana2 wartsila com bana 1 wartsila com bana3 wartsila com Figure VPN2 User Authentication After giving the username and password the user can now have an access to the IDM where the projects are saved 87 APPENDIX 2 IDM IDM is the Integrated Document Management of W rtsil which is the documentation system used inside the company IDM 3 4 2 Windows Internet Explorer 10 jc hitps fim01 vnsd comkronodac project 25028currdr 35480288ro0t 28151938 ava 0 X D Filter applied All but expired Reset List Default Subdocs Custom Predefined 15 03 2011 04 01 2011 14 03 2011 01 02 2011 0 04 2011 20 042011 5 03 20 15 03 2011 15 03 2011 07 04 2011 Figure IDM 1 IDM list of Files 88
56. ncimat Range H H SpecialCells xlCellTypeBlanks EntireRow ClearContents Sheets MalvernResult Activate Valid values is located in column Z for Malvern Range Z Z SpecialCells xlCellTypeBlanks EntireRow ClearContents Sheets BombCResult Activate Valid values is located in column O for Bomb C Range O O SpecialCells xlCellTypeBlanks EntireRow ClearContents Errx Exit Sub End Sub Snippet 35 Delete unvalid measurements This function will delete all unwanted measurements which are not valid The worksheet named Result contains all seven devices and the corresponding necessary parameters chosen based on the meeting In Figure 47 result was found for all the devices except the Rheometers Cloud Point which is reasonable because during the search there isn t any added data for Cloud Point only Viscosity has all the measured data 78 Measurement Date 17 12 2010 lodine Value 52 35534423 Measurement Date o O O 15 12 2010 7 9 2010 lAcidNumber OO 4954370317 35 35 MessurementDate 040200 y Peroxide value 9849540708 Measurement Date 0602200 0908 2010 06 01 2010 lGrossHeatvalue __ ssas 42804 241858 0 j o van a o tavay Measurementoate _ 2922200 O Temperature 0 3
57. ngSheet Select Range A1 E50 Select Selection Find What Sample Weight After ZActiveCell LookIn xlValues _ LookAt xlPart SearchOrder xlByRows SearchDirection xlNext _ MatchCase False Activate ActiveCell Offset 0 1 Copy Sheets Runlist BombCalorimeter Activate Range AF5 Select Selection PasteSpecial Paste xlValues Operation xlNone SkipBlanks _ False Transpose False End Sub Sub BCDate Look for the Measurements Date Sheets BCCopyingSheet Select Range A1 E50 Select Selection Find What Date Time After ZActiveCell LookIn xlValues _ LookAt xlPart SearchOrder xlByRows SearchDirection xlNext _ MatchCase False Activate ActiveCell Offset 0 1 Copy Sheets Runlist_BombCalorimeter Activate 39 Range AF12 Select Selection PasteSpecial Paste xlValues Operation xlNone SkipBlanks _ False Transpose False paste only values and don t transpose End Sub Snippet 9 SelecttheDataFrmImport Snippet 9 is selects the properties of the measurement and put them in specific column to be used for bringing them into corresponding textbox of the user interface Sub GetDataBC Bring the values of properties into user forms texbox Application ScreenUpdating False unable user to see what macro does TextBoxl Value Range Runlist_BombCalorimeter AF3 Value Sample Name TextBox2 Value Range Runlist_BombCalorimeter AF5 Value SW TextBox3 Value Range
58. nks _ False Transpose Fals Paste all data no calculation done copy as it is Sheets Runlist_BombCalorimeter Activate Range A1 Select Application CutCopyMode Fals disable the cut mode Workbooks runlist cgi Close savechanges Fals lose without saving changes ListFile ConnectMsg if error occur inform the user and exit MsgBox Please check if the device is ON Exit Sub Application DisplayAlerts True Application Screenlpdating False End Sub Snippet 3 ConnectDevice code This function opens the link as a workbook which is runlist cgi that contains all the files listed in Column A of first sheet The range will be copied and pasted into BombCalorimeter workbook for the user form s listbox To close the workbook without saving then settings for save changes needs to be False Prompt message to the user will show if the device is close 31 10036 1 1 det plim csv 10036 1 2 det plim csv 10036 2 2 det plim csv 10036 2 3 det plim csv 10036 2 4 det plim csv 10111 KE1 det plim csv 10113 KE0 det plim csv 10113 KE1 det plim csv 10113 KE2 det plim csv 10113 KELA det plim csv 10117 KEO det plim csv 10117 KE2 det plim csv 10117 KE3 det plim csv 5 BENTSOEHAPPO det plim csv ACERRA S2 det finl csv Figure 21 BC List of files Open the specific file selected In Figure 21 BC List of files we can see that files are in csv extensions but this will be in CGI exte
59. nsion stands for Common Gateway Interface which is a based standard for synchronizing external application with information server that are found throughout the internet or Hypertext Transfer Control HTTP 1 Documents with CGI File format provides an API which receives the users input and produce the document in HTML form back to user Query to the server through an IP address is done to retrieve the measurement data from the database stored in the device In this case the feedback document uses the web browser to display data as HTML document as shown in Figure 22 but we have to get and save the necessary properties and value into the Excel Database File The code for Command Button named Import is shown in Snippet 4 Settings for application for both cut copy mode and the display alert as FALSE In this reason any prompts or alert messages while the macro is running will not show up 32 Private Sub Application CutCopyMode False Application DisplayAlerts False disable for prompt messages SelectV look for selected file and copy the link ImportFile follow link open file and import to worksheet Application CutCopyMode False Application DisplayAlerts True Sheets BCRawData Activate Activate the RawData End Sub Snippet 4 Code for Import Command Button This code consists of two separate codes such as SelectV and ImportFile SelectV is the function to know what the se
60. of ship power solutions including engines automation and power distribution systems as well as sealing solutions for the marine industry among others The company offer solutions for base load power generation grid stability amp peaking industrial self generation also for the oil and gas industry 7 W rtsil Power Plant Technology Fuel Laboratory is the place where PPT team test their samples of different fuel types such as Heavy Fuel Oil Light Fuel Oil Crude Oil Straight Bio Fuel Oil among others These samples are measured to define their characteristics and properties using different device Data Collection Analysis and Management System is an application that aims to collect measured data from the devices Every device has own database in MS Excel format and Visual basic for application VBA is the programming language used to develop this project Main functions consist of searching and importing files Import function can import data from same excel file format xls text format txt comma separated variable csv or portable document format pdf Main Search File is done after the application for the entire device ready This last file will search and list all the properties of the sample from different devices into one worksheet Laboratorian can compare how the samples properties change depending on the time interval or see how one samples properties within different device This makes it easier for them to see if sam
61. perties that must be present such as ID number Measurement Date amp Time Sample Weight Additional Properties are Spike Weight Initial Temp Temp Rise GHV EEV File Name Link for Titration Initial Temp End Temp Heat rate and Atmosphere for DSC For Titration must include Arrival date Operator Fuel Type Notes Valid 0 test consumption KOH consumption Then divided into three categories Acid Number and Strong Acid Number Jodine Value and Average Iodine Value Peroxide Value and Average peroxide Value 19 Main Search File Heat Values calculation for Bomb Calorimeter The application should have User Interface for search import Add to database function after import Check for duplicate when adding to database Additional search UI for Rheometer that can select from 4 meas points to get the viscosity values and shear rate Application is nice to have Access to user Manual Rheometer adding measurements automatic data reformatting Calculation formula for Acid Number Peroxide Value Iodine Value Exit Cancel function 20 5 DATA COLLECTION ANALYSIS amp MANAGEMENT SYSTEM The application needs to have the design to understand how the program should work This section will go through the design of the program 5 1 Access of computers These seven devices have their own database in excel file where the measurements are stored Malvern Mastersizer database Rancimat and CRU have
62. ple has a very high or very low value in specific properties that may had cause problem to specific power plants It is expected that the program will be used by different people inside the company based on the defined access of the user but the main target is the fuel laboratory team The requirements of this project were successfully achieved and saved in IDM to be access using a LAN connection or VPN connection 11 2 TECHNOLOGY OVERVIEW The tool used for this application is MS Excel and program in Visual basic for application or simply called as VBA Visual Basic Editor VBE is the editor used 2 1 MSEXCEL MS Excel is one of the world s most common spreadsheet programs in used In this program we can create worksheets database draw charts and make tasks and others related to numeric information easier 3 22 VBA and Visual Basic for Application VBA development software is included with each component of the MS Office suite programs such as Word and Excel Starting the VBA development software places in the VBA programming environment Integrated Development Environment IDE which provides numbers of tools for use in the development of the project 4 2 5 VISUAL BASIC EDITOR On top of the window like in most applications there is Menu bar Standard Toolbar is also available which gives the user an easy access to common tools that are available within the application Project Explorer Window lists all projects that a
63. r previous function IMPORT Here is the User Form for this particular search Searching Form 10036 1 1 det plim csv 10036 1 2 det plim csv 10036 2 2 det plim csv 10036 2 3 det plim csv 10036 2 4 det plim csv 10111 KE1 det plim csv 10113 KE0 det plim csv 10113 KE1 det plim csv 10113 KE2 det plim csv 10117 KE3 det plim csv 5 det plim csv ACERRA S2 det finl csv Figure 28 Searching the BC Device User Form Snippet 3 5 6 and 7 are used in this function as of Import This consist of exactly same process which connect to Bomb Calorimeter device open the link as workbook and copy all the list of files that will be placed in the list box too The user can select one file then open the selected file in worksheet 52 6 2 DSC Application DSC has two main functionalities like the Bomb Calorimeter but the import for external file 15 taken from the user s local drive while the search is connecting to IDM to get the list of PDF files stored in there after measuring from DSC device 6 2 1 DSC Search Function This DSC search form has two options too similar to the Bomb Calorimeter except this search directly from IDM wherein all the measured data were directly saved All the measured data in IDM are in PDF format In Figure 29 below shows the user form for searching the DSC database where measurements that weren t in PDF files are saved Figure 29 User Form for Searching DSC database
64. rch is done from the user interface LookIn Optional Variant The type of Information Variant LookAt Optional Can be one of the following xILookAt constants xIWhole or 1 SearchOrder Optional Variant Can be one of the following xlSearchOrder constants or xIByColumns SearchDirection Optional xlSearchDrire Can be one of the following xlSearchDrirection ction constants xlNext or xlPrevious MatchCase Optional Variant True to make the case sensitive default is False SearchFormat Optional Variant The search format 34 Sub ImportFile Dim LinkOfFile As String LinkOfFile Range Runlist BombCalorimeter S2 Value Application Workbooks Open LinkOfFile open the link as workbook GEtTHeReport transfer Application CutCopyMode False Application ScreenUpdating False End Sub Snippet 6 ImportFile Code This function opens the link of the selected file from the list box which has the control source located in Range Runlist_BombCalorimeter S2 Value which is string data type 35 Sub GEtTHeReport Application ScreenUpdating False Application DisplayAlerts False Workbooks report cgi Sheets 1 Select select Sheet 1 of the report that was opened Cells Select from link Range A1 Copy copy used cells from Al Workbooks BombCalorimeter xlsm Activate Sheets BCCopyingSheet Select Range al Paste paste to sheet of the bomb calorimeter file Selection PasteSpe
65. re are two main command button in this file Main Search opens the form shown in Figure 46 while Open database opens the Figure 45 74 OPEN SPECIFIC DATABASE Figure 45 Open database form The figure above will open display the specific database that the user selects This will open the file directly from the IDM where all the database files are saved Viscosity Cloud amp Pour Point button 15 for Rheometer database Main Search command button opens the form in Figure 46 when this form shows there is an automatic function initializes which opens the Kemikaalikortisto file for the ID numbers Private Sub UserForm Initialize run macro that checks if kemikalikortisto is open when user form is initialized loaded CheckBox2 Value True CheckIfOpenKemikaalikortisto End Sub Snippet 32 Main Search Form Initialize function 75 Search Criteria Main Additional Steps for DSC Fuel Type NOTE Put only in textbox first 3 v ow digits of year to identify which 2 sheet we would like to search from 009 2009 010 for 2010 011 for Select Fuel Type 2011 and so ID Numbers Arrival Date 10001 14 01 2010 10003 06 04 2010 10004 Get Matching 10005 ID number 10006 12 04 2010 based on Fuel 10008 16 04 2010 Types 10010 16 04 2010 10013 10014 10016 10020 pyrolysis oil vegetable oil 9029 2 9041 3 1 10019 4 10019 5 10042 1 1 10042 3 3 x EXIT
66. re currently open including those projects opened by excel during start up 4 Window where the code is written is the object code window List of attributes or properties of the currently selected object of the project explorer window are displayed in properties window These properties are used to manipulate the appearance and behaviour of the object 12 4 Microsoft Visual Basic 1 Module1 Code MENU BAR 8 File Edit View Insert Format Debug Run Tools Add ins Window Help STANDARD TOOLBAR OBJECT CODE WINDOW PROPERTIES WINDOW Figure 1 VBE 2 4 USER FORM User forms are similar to other VBA objects which have methods properties and events that are use to control the appearance and behaviour of interface window Forms allow programmers to build custom interface with office applications in VBA 4 1 Eile Edit View Insert Format Debug Run Tools Add Ins Window Help Type a question for help Bl 4800000128 10 fmBorderStyleNone _ Figure 2 User Form 13 2 5 VPN CONNECTION Virtual Private Network is a temporary physical route formed over structured topology The Virtual Private networking represents the process of transmitting data over a VPN The category of the VPN use in accessing this project is the remote access VPN which enables both fixed location and mobile workers to communicate with a central location In this case if a network includes a connection to the internet it
67. rements date saved in the raw data so user can check what min date and max date to give that surely has a measurement Form for Searching within the Database of the Rancimat palm oil storage605 in use 041124 sample041126 Rypsi ljy 4 6 09 pun 3 36 Palmoil 25 10 6 09 pun 3 38 Palmoil 22 10 6 09 pun 3 32 Palmoil 22 10 6 09 pun 3 33 Palmoil 22 10 6 09 pun 3 379 0 12g vett Rypsi ljy 4 6 09 pun 3 45 Rypsi ljy 4 6 09 pun 3 41 x ID number ID number Sample Name can be filter typing from the textbox after clicking the checkbox NOTE Always select something from the list if your criteria is ID number Measurement Date penu Temperature 2 100 zn Only Valid Measurements Search this Database Figure 52 Rancimat Search Form 83 Searching Form for CRU Figure 53 CRU Search Form CRU is similar to Rheometers first search form interface which consist of criterias ID number Fuel Types Measurement Date Ignition type either stated as for yes or n for no is additional property for the CRU 84 7 CONCLUSION Data Collection Analysis and Management System is an application that aims to collect and analyze measured data from the devices MS Excel is a common tool for everyone but this project gives a deeper knowledge on what kind of application this tool can make with the use of VBA Fuel laboratory is very n
68. rm Sheets BCResult Select Activate the Reult page for user to view the result of search Exit Sub Err_Execute MsgBox No Match Found try again Message if no match found Worksheets BCRawData Activate Rows 6 1000 EntireRow Hidden False Worksheets BCStart Activate activate Start page End Sub Snippet 14 Search Function from Raw Data 8 This multiple pages Snippet 14 is the code for the search which give the combination of the four criteria The user can select any combination or select just one The result will be in the worksheet named BCResult where all the matching measurements are copied after search Moisture Content as arrived w Moisture Content analysis w SAMPLE NAME 10007 10038 10038 MEASUREMENT DATE 08 06 2010 SAMPLE WEIGHT g 0 5111 0 5512 0 5586 INITIALTEMP t 29 8456 30 0025 29 6117 O TEMP RISE 5 8443 5 4088 5 4845 SPIKE WEIGHT 0 0000 0 0000 0 0000 Hydrogen H EHE HENRI RENE jon LHV dry MJ kg GROSS HEAT MJ kg 44 1395 37 8436 37 8775 LINK http 10 162 26 8 cgi bin report cgi Whttp 10 162 26 8 cgi bin report cgi l http 10 162 26 8 cgi bin report tt FILE NAME KONDENSA2 det plim csv 1D10038 det plim csv 1D10038 2 det plim csv EE Value 931 9288 Valid x pe HHV dry Figure 27 Bomb Calorimeters Result Page 51 The second option for user is to search device itself which is similar to ou
69. s False dont prompt user for any alert messages On Error GoTo OpenningError LookFrmKronodoc search the given range for the value similar to what is selected from the list box then activate the matching cell FollowHyperlink follow the link and open the file OpenningError Exit Sub Application DisplayAlerts 2 True End Sub Snippet 19 Command Button to Open selection Snippet 19 is the code for the command button named Open selected file from the user form interface Sub LookFrmKronodoc Dim LinkToselected As String LinkToselected Range DSCStart CE2 Value Workbooks kronodoc Activate file from IDM Range A1 H300 Select find match from what is selected in listbox in this range Selection Find What LinkToselected After ActiveCell LookIn xlFormulas _ LookAt xlPart SearchOrder xlByRows SearchDirection xlNext _ MatchCase False Activate activate matching file name End Sub Snippet 20 Look from Kronodoc file 57 Snippet 20 shows code that search kronodoc file which is temporary workbook opened after opening the IDM This function search the given range for the value similar to what is selected from the list box then activate the matching cell Sub FollowHyperlink Application ScreenUpdating False disable user for seing how the macros run Application DisplayAlerts False disbale prompt messages such as alerts ActiveCell Hyperlinks 1 Follow follow the link
70. stination CSheet Active Workbook Close Ex DSC Err DSCCommand End Sub 59 Sub DSCImportBtn Sheets TextCopy Activate Range A1 Select Import Text DSC import text or csv file Application ScreenUpdating False DSCGetData Show show the user form for importing necessary properties End Sub Snippet 22 DSC code for Import files Snippet 22 shows the code for import function which displays the Open window that user can select file of text or csv format and copy the content of the file into worksheet named TextCopy OPen Lookin dsc 9 4 2 My Recent Documents Desktop Documents My Computer My Network Places Filename Files of type Text Files txt Figure 34 Open window The open file window shows in Figure 34 where user can select files from local drive After selecting the desired file the program will copy to a certain worksheet 60 6 2 3 DSC Get Add Cancel Data Figure 35 shows the original view of the measured data in a text file format ottana po 20090821 txt Notepad C Documents and settings xwa Documents ottana po 20090821 cdcd JLE Ottana PO 20090821 Sulatettu mitattu 20090914 sulatus 20100427 537N6120610 27 4 2010 17 11 24 11 770 mg 11 770 NO Date alibration Information C Program Files Pyris calibrations 7 5 08 dcc ad d steps
71. t This device is used as the analyzer of only bio fuels Rancimat tests the effectiveness of the antioxidants additives and unwanted impurities in specific temperature 6 Figure 9 Rancimat device 3 7 Combustion Research Unit CRU Examination of ignition sensitivity and combustion periods of liquid fuels is done using CRU device The fuel that will be measured in this CRU needs to be sufficiently low 6 Figure 10 device 18 4 THE APPLICATION DESCRIPTION The main functionality of this application is to search measured data and import data for specific device but to make this function works there are other functions that forms these main functionalities 4 4 Requirement Analysis Requirements analysis consist of the applications must have should have and nice to have The application must have Own database file for all devices MS Excel form All files must have search function where all consist of criteria s ID numbers Measurement Dates amp additional criteria are and NHV for Bomb Calorimeter Initial temp End Temp and Atmosphere for DSC Fuel Types and valid measurements for Rheometer Arrival date fuel type and valid for Titration Temperature for Rancimat Fuel Type and Ignition condition for CRU MM only consist of ID numbers amp Meas date Import function for Titration Bomb Calorimeter DSC These three consist of common pro
72. t the Report Command Button Next Step Select the Data From Import Get Data BC Command Button Calculate Check Duplicate Entry Add data to Database Search Function From Raw Data Clear Function Exit Function Copy List of pdf Files Sort List of pdf Files Command Button to Open selection Look from Kronodoc File Follow HyperLink Function DSC Code for Import File Get Data Command Button Get Data DSC Copy Data Simplifying Added Measurements Command Button Get Data Get the Result to Page 2 function DataRheom Function Get The Matching Report Function Add to Database Function 33 33 35 36 38 40 40 43 44 45 51 54 54 56 195 57 57 58 59 61 62 62 64 67 68 69 70 72 Snippet 32 Snippet 33 Snippet 34 Snippet 35 Main Search Form Initialize Function Get valid Result command button Open the Files Delete unvalid Measurements p 74 75 p 77 LIST OF APPENDICES APPENDIX 1 VPN Connection APPENDIX 2 IDM p 83 p 84 10 1 INTRODUCTION W rtsil was established in year 1834 and company is global leader in complete lifecycle power solutions for the marine and energy markets They have operations in 160 locations in 70 countries worldwide The company is the leading supplier of power plant for power generation which is also a provider
73. tching criteria but then using different combinations and multi select list box decided to use the Hide Unhide Row The idea was referred from a previous project 8 This program hides all the rows and when a match is found the row unhide until the last row is reached When the search is successful the entire visible row except the header will be copied to the result page of this database otherwise prompt message to user will show if no match found Sub OtherOptionForSearchng This function hide used range unhide matching rows based on the combinations of selected criteriassuch as Sample weight Measurement Date Gross Heat Value and Net Heat Value Listbox for Sample Name can have mutiselection Application ScreenUpdating False Sheets BCResult Range C3 FY28 ClearContents Clear the sheet from previous search Dim BCRow As Integer declare the variables Dim DateM DateM2 As Date Dim i As Long Sheets BCRawData Select Activate Raw data On Error GoTo Err Execute BCRow 6 Range BCRawData A6 R1000 EntireRow Hidden True Hide entirerow of used range Do While Len Range A amp CStr BCRow Value 0 Sample Name Gross Heat and Net Heat Value get the selected list from muti selection listbox If UserForm4 CheckBox1 Value True And UserForm4 Che ckBox2 Value False _ And UserForm4 CheckBox3 Value True And UserForm4 CheckBox4 Value True Then For i 0 To UserForm4 ListBox1 ListCount 1 If UserForm4 L
74. the same applications three devices aren t SONAD computers which mean that these computers are not able to connect to the W rtsil s intranet compass The only way to add data s to the database when measurements are done is to copy paste it to the SONAD computer that can access the IDM The remaining databases have the function that includes import function which can access straight from the devices Malvern Mastersizer RS 232 Rheometer OleiniTec RS 232 csv 16 Kb csv 16 Kb txt csv database xls database Database Visual Basic Excel Gonnectign Differential Scanning Calorimeter txt ASCII 50 1500 Kb database senad Pdf 50 Kb database Bomb Calorimeter IP addr 10 162 26 8 Anton Parr Internal HD accessed via Ethernet csv Direct from device HD to database Office Connection Rancimat 2551 xIs databas CRU FuelTech ee xIs database Figure 11 Access of computers 21 5 2 Flowchart This flowchart will focus only to Bomb Calorimeter and Search Importing data for the Bomb Calorimeter consist of different functions IMPORT FILE FROM DEVICE CALCULATE HEAT VALUES ADD TO DATABASE Open the User Form Activate the Raw Data Sheet Messag eto Given
75. tion is the technique used in this device 6 Figure 5 Malvern Mastersizer device 6 3 3 Rheometer Rheometer device can be used to characterize liquid flow behaviour as well as transforms in semi solid materials both in rotational or oscillatory mode in the temperature range 40 20 200 C the range depends on measuring geometry This device can mainly used to check the viscosity cloud point and pour point of the sample 6 The complexity of the fuel heating and treatment system is determined by viscosity value The temperature at which a cloud or a haze of wax crystals appears at the bottom of the samples is called Cloud Point while the temperature below which the fuel does not flow is the Pour Point 6 M Figure 6 Rheometer device 6 16 34 Titration Titration device enables a wide range of potentiometric titration and is mainly used to reveal bio oils acid number iodine value and peroxide value Acid number is the amount of KOH needed to neutralize free fatty acids while iodine value is the measure of hydrocarbon chain a mass of iodine that is consumed by 100 grams of bio oils sample 6 Figure 7 Titration device 3 5 Differential Scanning Calorimetry DSC Measuring the difference in the amount of heat required to increase the temperature of a sample and reference when measured as a function of temperature is done using DSC device 6 Figure 8 DSC device 6 17 3 6 Rancima
76. tivate the sheet and search for the measurement date and activate when when found Sheets countSheet Activate Cells Find What MeasDate After ActiveCell _ LookIn xlFormulas LookAt xlWhole SearchOrder xIByRows _ SearchDirection xlNext MatchCase False Activate If ActiveCell Value MeasDate Then Exit Sub End If Next countSheet End With End Sub Snippet 29 Find DataRheom Function When match of measurement date and time is found from the sheet then Snippet 30 will follow 70 Sub GetTheMatchingReport Application ScreenUpdating False disable screen upate Application DisplayAlerts False disable alerts Sheets Sheet2 Range A1 G65536 ClearContents Range ActiveCell Offset 0 3 EntireColumn ActiveCell Offset 0 3 EntireColumn Copy copy entire 7 columns of the matching measurments Sheets Sheet2 Activate Range A1 Select Selection PasteSpecial Paste xlValues Operation xlNone SkipBlanks _ False Transpose False paste in sheet name Sheet2 Application ScreenUpdating True Application DisplayAlerts True Sheets Sheet2 Activate This sheet will be used for searching the measuring points Range A1 Select First Meas Points function to find first meas points Secnd Meas Points function to find second meas points Third Meas Points function to find third meas points Fourth Points function to find fourth meas points End Sub Sub First Meas Points Get the first
77. will open the User Form shown in Figure 15 BC Import File Connect In this UI the steps are marked in numbers for user to know what the process is LIST OF FILE FROM BOMB CALORIMETER DRIVE 10036 1 1 det Dlim CSV 10036 1 2 det plim csv 10036 2 2 det plim csv 10036 2 3 det plim csv 10036 2 4 det plim csv 10111 KE1 det plim csv 10113 KE0 det plim csv 10113 KE1 det plim csv 10117 KE2 det plim csv 10117 KE3 det plim csv 5 BENTSOEHAPPO det plim csv ACERRA S2 det finl csv Figure 20 BC Import File Connect Private Sub CommandButton3_Click ConnectDvce To connect to the device End Sub Snippet 2 Command Button Connect Shown in the Snippet 2 is the code for the Connect command button that will run the function ConnectDvice This function is describe in the next page of this report 30 Sub ConnectDvce On Error GoTo ConnectMsg declare what to do if error occur Application ScreenUpdating Falsedisable screen updates Open the linkas workbook Application Workbooks Open http 10 162 26 8 cghin runlist cgi Application DisplayAlerts Fals disable alerts Workbooks runlist cgi Sheets 1 Selectactivate the first sheet of the workbook opened Cells Select Range A1 Activate Selection Copy copy the listlocated in column A Windows BombCalorimeter xlsm Activate Sheets BCOpenDrive Select worksheet to be pasted Range Al Paste Selection PasteSpeial Paste xlAll Operation xlNone SkipBla

Data Collection Analysis and Management System

Contents

Download Pdf Manuals

Related Search

Related Contents