Extract data from an Excel file and put into another

[EugeneBennett]

Hi

I have an application where I need to manipulate data in a Dbf file(File1), search a second file (File2) for a tagname and scale the value before populating a third file (File3) with the result.

This is how I think it should work, but how do I do it, I'm not very good with VB!


Open DbfFile (File1)

Click on macro Button

Open Eventlogged Tags File (File2)

Concatenate Addr and Rtu ie 41R24
Concatenate Date/time Columns 26/06/2007 12:35:00:000

Search Eventlogged tags file (File2) for register Number (ItemName)

If Found

Find Tagname
Find Min Raw Scale
Find Max Raw Scale
Find Min EU Scale
Find Max EU Scale
Scale Raw Value from Log File
Create String

File 3 Shows the format required. The first 3 Rows are simply a header and the rest being the data and a few extra fields.

Write to File3
repeat until EOF
Save file3 as filename= datetime.logs
Close All
Exit Excel

Example File 1

DATE TIME LABEL PRI RTU ADDR VALUE USER
05/06/07 12:41:20.03 Label 0 10 #R24 126 1
05/06/07 12:41:10.02 Label 0 10 #R24 116 1
05/06/07 12:41:00.01 Label 0 10 #R24 106 1

File 2 Example

:IOReal Comment EngUnits MinEU MaxEU MinRaw MaxRaw ItemName
RES032_CL01_Residual Waipu CL2 Residual g/m³ 0 5 0 32767 32R26
RES036_CL01_Residual Parua Bay CL2 Residual g/m³ 0 5 0 32767 36R27
RES036_PH01_PH Parua Bay PH Level pH 0 14 0 32767 36R24
RES040_CL01_Residual Fairway Drive Res Final CL2 g/m³ 0 5 0 32767 40R27
RES040_PH01_PH Fairway Drive Res Final PH pH 0 14 0 32767 40R28
RES041_CL01_Residual Hikurangi CL2 Residual g/m³ 0 5 0 32767 41R27
RES041_PH01_PH Hikurangi PH Level pH 0 14 0 32767 41R24

Example File 3

ASCII
,
wwadmin,1,New Zealand Daylight Time,10,1
ENV_TP4109,1,2006/11/17,15:44:40.026,1,40,192
ENV_TP4109,1,2006/11/17,15:44:50.048,1,50,192
ENV_TP4109,1,2006/11/17,15:45:00.052,1,0,192


Cheers

Eugene

[eklavyavats]

Hi

I have an application where I need to manipulate data in a Dbf file(File1), search a second file (File2) for a tagname and scale the value before populating a third file (File3) with the result.

This is how I think it should work, but how do I do it, I'm not very good with VB!


Open DbfFile (File1)

Click on macro Button

Open Eventlogged Tags File (File2)

Concatenate Addr and Rtu ie 41R24
Concatenate Date/time Columns 26/06/2007 12:35:00:000

Search Eventlogged tags file (File2) for register Number (ItemName)

If Found

Find Tagname
Find Min Raw Scale
Find Max Raw Scale
Find Min EU Scale
Find Max EU Scale
Scale Raw Value from Log File
Create String

File 3 Shows the format required. The first 3 Rows are simply a header and the rest being the data and a few extra fields.

Write to File3
repeat until EOF
Save file3 as filename= datetime.logs
Close All
Exit Excel

Example File 1

DATE TIME LABEL PRI RTU ADDR VALUE USER
05/06/07 12:41:20.03 Label 0 10 #R24 126 1
05/06/07 12:41:10.02 Label 0 10 #R24 116 1
05/06/07 12:41:00.01 Label 0 10 #R24 106 1

File 2 Example

:IOReal Comment EngUnits MinEU MaxEU MinRaw MaxRaw ItemName
RES032_CL01_Residual Waipu CL2 Residual g/m³ 0 5 0 32767 32R26
RES036_CL01_Residual Parua Bay CL2 Residual g/m³ 0 5 0 32767 36R27
RES036_PH01_PH Parua Bay PH Level pH 0 14 0 32767 36R24
RES040_CL01_Residual Fairway Drive Res Final CL2 g/m³ 0 5 0 32767 40R27
RES040_PH01_PH Fairway Drive Res Final PH pH 0 14 0 32767 40R28
RES041_CL01_Residual Hikurangi CL2 Residual g/m³ 0 5 0 32767 41R27
RES041_PH01_PH Hikurangi PH Level pH 0 14 0 32767 41R24

Example File 3

ASCII
,
wwadmin,1,New Zealand Daylight Time,10,1
ENV_TP4109,1,2006/11/17,15:44:40.026,1,40,192
ENV_TP4109,1,2006/11/17,15:44:50.048,1,50,192
ENV_TP4109,1,2006/11/17,15:45:00.052,1,0,192


Cheers

Eugene

you can create a macro in excel and then by visual basic you can call/activate that macro