Micro Coding To Get Live.Data in Excell Sheet in Order by

Question

Firstly i am taking some live xml data in to sheet1 , sheet2 , sheet3 and fixed refreshing time period is 3 mints ...

/ROOT
/WIN/@DATE	/WIN/@updateDate	/WIN/@updateTime	/WIN/@VENUE	/WIN/RACE/@FINAL_COLLATE	/WIN/RACE/@ID	/WIN/RACE/@NUM	/WIN/RACE/@PID	/WIN/RACE/@TIME	/WIN/RACE/OUT	/WIN/RACE/OUT/@BIG_DROP
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	9.3	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	6.1	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	15	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	32	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	7.8	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	8.4	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	14	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	3.4	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	48	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	48	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	7.3	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	16	0

 

I wrote micro files(Alt=F11) for sheet1 ,sheet2 and sheet 3 as follows to Get the column "/WIN/RACE/OUT" at diffrent refreshing times.. with following code...

/ROOT
/WIN/@DATE	/WIN/@updateDate	/WIN/@updateTime	/WIN/@VENUE	/WIN/RACE/@FINAL_COLLATE	/WIN/RACE/@ID	/WIN/RACE/@NUM	/WIN/RACE/@PID	/WIN/RACE/@TIME	/WIN/RACE/OUT	/WIN/RACE/OUT/@BIG_DROP
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	9.3	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	6.1	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	15	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	32	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	7.8	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	8.4	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	14	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	3.4	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	48	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	48	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	7.3	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	16	0
DATE	COURSE	refreseh 1
22-04-2015	HV	9.3
22-04-2015	HV	6.1
22-04-2015	HV	15
22-04-2015	HV	32
22-04-2015	HV	7.8
22-04-2015	HV	8.4
22-04-2015	HV	14
22-04-2015	HV	3.4
22-04-2015	HV	48
22-04-2015	HV	48
22-04-2015	HV	7.3
22-04-2015	HV	16								Code As::

 

 

Micro Code As::: 

 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

Dim val As String

i = 7

While (Cells(20, i) <> "")

i = i + 1

Wend

Dim k As Integer

If i = 1 Then

k = 1

Else

k = i - 1

End If

If (Cells(19, k) <> Cells(3, 3)) Then

Cells(19, i) = Cells(3, 3)

Cells(20, i) = Cells(3, 10)

Cells(21, i) = Cells(4, 10)

Cells(22, i) = Cells(5, 10)

Cells(23, i) = Cells(6, 10)

Cells(24, i) = Cells(7, 10)

Cells(25, i) = Cells(8, 10)

Cells(26, i) = Cells(9, 10)

Cells(27, i) = Cells(10, 10)

Cells(28, i) = Cells(11, 10)

Cells(29, i) = Cells(12, 10)

Cells(30, i) = Cells(13, 10)

Cells(31, i) = Cells(14, 10)

Cells(32, i) = Cells(15, 10)

Cells(33, i) = Cells(16, 10)

Cells(34, i) = Cells(17, 10)

End If

End Sub

Out Put At 1^st refreshing time intervel

/ROOT
/WIN/@DATE	/WIN/@updateDate	/WIN/@updateTime	/WIN/@VENUE	/WIN/RACE/@FINAL_COLLATE	/WIN/RACE/@ID	/WIN/RACE/@NUM	/WIN/RACE/@PID	/WIN/RACE/@TIME	/WIN/RACE/OUT
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	9.3
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	6.1
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	15
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	32
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	7.8
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	8.4
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	14
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	3.4
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	48
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	48
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	7.3
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	12:00:00 AM	16
DATE	COURSE	refreseh 1
22-04-2015	HV	9.3
22-04-2015	HV	6.1
22-04-2015	HV	15
22-04-2015	HV	32
22-04-2015	HV	7.8
22-04-2015	HV	8.4
22-04-2015	HV	14
22-04-2015	HV	3.4
22-04-2015	HV	48
22-04-2015	HV	48
22-04-2015	HV	7.3
22-04-2015	HV	16

but if missing any live xml rows (or) adding any New Rows in live data in sheet then change the order like as below..at 2^nd refresh time interval with 2 rows missed in live data.

/ROOT
/WIN/@DATE	/WIN/@updateDate	/WIN/@updateTime	/WIN/@VENUE	/WIN/RACE/@FINAL_COLLATE	/WIN/RACE/@ID	/WIN/RACE/@NUM	/WIN/RACE/@PID	/WIN/RACE/@TIME	/WIN/RACE/OUT	/WIN/RACE/OUT/@BIG_DROP
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	9.3	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	6.1	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	15	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	32	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	7.8	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	8.4	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	14	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	3.4	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	48	0
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	48	0
			refreseh 2
			9.3
DATE	COURSE	refreseh 1	6.1
22-04-2015	HV	9.3	15
22-04-2015	HV	6.1	32
22-04-2015	HV	15	7.8
22-04-2015	HV	32	8.4
22-04-2015	HV	7.8	14
22-04-2015	HV	8.4	3.4
22-04-2015	HV	14	48
22-04-2015	HV	3.4	48
22-04-2015	HV	48
22-04-2015	HV	48
22-04-2015	HV	7.3
22-04-2015	HV	16

and output at 3rd refreshing time intervel3 with 4 rows missed in live data.

 

/ROOT
/WIN/@DATE	/WIN/@updateDate	/WIN/@updateTime	/WIN/@VENUE	/WIN/RACE/@FINAL_COLLATE	/WIN/RACE/@ID	/WIN/RACE/@NUM	/WIN/RACE/@PID	/WIN/RACE/@TIME	/WIN/RACE/OUT
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	9.3
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	6.1
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	15
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	32
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	7.8
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	8.4
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552	14
				refreseh 3
				9.3
				6.1
				15
			refreseh 2	32
			9.3	7.8
DATE	COURSE	refreseh 1	6.1	8.4
22-04-2015	HV	9.3	15	14
22-04-2015	HV	6.1	32	3.4
22-04-2015	HV	15	7.8
22-04-2015	HV	32	8.4
22-04-2015	HV	7.8	14
22-04-2015	HV	8.4	3.4
22-04-2015	HV	14	48
22-04-2015	HV	3.4	48
22-04-2015	HV	48
22-04-2015	HV	48
22-04-2015	HV	7.3
22-04-2015	HV	16

 

If online live data changed my below sheet table changed by cells, Can I retrieve the data in single table followed by Date,Coruse Columns like refresh 1,referesh 2,refresh 3 …like this..

can I Any one Help me in Macro Coding To Get Live Data in single Row formate either adding any live data or removing any live data..?...like as fallows..

/ROOT
/WIN/@DATE	/WIN/@updateDate	/WIN/@updateTime	/WIN/@VENUE	/WIN/RACE/@FINAL_COLLATE	/WIN/RACE/@ID	/WIN/RACE/@NUM	/WIN/RACE/@PID	/WIN/RACE/@TIME
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
22-04-2015	22-04-2015	19:15:52	HV	1	3563	1	0	191552
DATE	COURSE	refreseh 1	refreseh 2	refreseh 3
22-04-2015	HV	9.3	9.3	9.3
22-04-2015	HV	6.1	6.1	6.1
22-04-2015	HV	15	15	15
22-04-2015	HV	32	32	32
22-04-2015	HV	7.8	7.8	7.8
22-04-2015	HV	8.4	8.4	8.4
22-04-2015	HV	14	14	14
22-04-2015	HV	3.4	3.4	3.4
22-04-2015	HV	48	48
22-04-2015	HV	48	48
22-04-2015	HV	7.3
22-04-2015	HV	16

 

Thankq in Advance....