How to Read data from Excel in AFL

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.

  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

Thanks it worked like Charm.

1 Like