How to Read data from Excel in AFL

AFL Programming
1

I have use cases where data are updated every 15mins into to excel sheet, from which I need to read it in AFL code and display it in Amibroker chart as text.
But i could able to figure it out how to read excel in afl . kindly help me out. Thanks in advance.

2
  1. Set path to Excel file in code below
  2. Set Excel cell to read from
  3. Open your Excel file
  4. Run Explorer (or click ParamTrigger of chart pane).
EnableScript ( "vbscript" );
<% 
Function GetXLSfile( file )
	Err.Clear
	On Error resume next
	Set Obj = GetObject( file ).Application
	If Err.Number <> 0 then
		GetXLSfile = 0
	Else
		Set GetXLSfile = Obj
	End If
End Function
%>
/// 1. Set path to Excel file below
/// 2. Set Excel cell to read from
/// 3. Open your Excel file
/// 4. Run Explorer (or click ParamTrigger of chart pane).
/// @link https://forum.amibroker.com/t/how-to-read-data-from-excel-in-afl/19229/2
xls_file = "C:\\Book1.xlsx";
xls_cell = "A1";
script = GetScriptObject();
excel = script.GetXLSfile(xls_file);
trigger = Status("action") == actionExplore OR 
		ParamTrigger("Store Excel Value", "CLICK HERE");
if ( trigger ) {	
	if ( excel ) {
		wb = excel.WorkBooks;    
		if ( wb.Count > 0 ) {    
			wb1 = wb.Item(1);
			ws = wb1.WorkSheets;
			sheet = ws.Item(1);		
			
			// Store value of cell A1
			rnge = sheet.Range( xls_cell );
			cell_value = rnge.value2; 
			StaticVarSet("Excel_Value", cell_value);  
		} else
			Error( "wb.Count is zero" );
	} else 
		Error( "No Excel object" );	
}

cell_value = StaticVarGet("Excel_Value");

Title = StrFormat("Excel value: %g", cell_value);

SetOption("NoDefaultColumns", 1);
Filter = Status( "lastbarinrange" );
AddColumn( cell_value, "EXCEL cell value", 1.2);

6

14 Likes
4

Thanks it worked like Charm.

6