How to output valume to Excel in afl

[junjun]

Hello !!

How do i output data to another program like Excel in afl

Any help would be greatly appreciated. Thank you!!!

[Tomasz]

Excel like any other OLE server can be controlled via OLE directly from AFL. Starting point is:

Excel = CreateObject("Excel.Application");
Excel.Visible = True;
Workbooks = Excel.Workbooks;
Workbooks.Add;
Worksheet = Excel.ActiveSheet;
Range = Worksheet.Range("A1", "A1");
Range.Value = 123;

[junjun]

Thks :

Excel = CreateObject(..... -> will always recreate a Excel instance ,
how can I do use same Excel instance

tks again

[beppe]

@junjun, I think you should experiment with the function GetObject() that is available since version 6.38:

AFL: new function GetObject( path, class ) providing functionality equivalent to JScript GetObject and VBScript GetObject

Please, visit the above link, and take note of @Tomasz's DISCLAIMER about its usage.

[fxshrat]

Actually you do not need that. Is also works with AB versions before 6.38.
One can use CreateStaticObject and doing workbook count.

Excel = CreateStaticObject( "Excel.Application" );

wb = Excel.WorkBooks; 

// forcing Excel to not open multiple instances
if ( wb.Count == 0 ) {
	Excel.Visible = True;
	wb.Add;			
}

[Tomasz]

There is a difference between CreateStaticObject and GetObject functions. You can use one or another depending on your objective.

• CreateStaticObject creates new Excel sheet object ONCE
• GetObject allows you to connect to existing file that is currently open in running Excel instance without creating new Excel sheet

Version(6.38); // minimum required version
Workbook = GetObject("e:\\Full_Path\\To_File\\Sheet.xls", "");
Excel = Workbook.Application;
Excel.Visible = True;
Worksheet = Excel.ActiveSheet;
Range = Worksheet.Range("A1", "A1");
Range.Value = 123;

Notes:

1. GetObject will FAIL (return Null handle) if file does not exist already.
2. The code above assumes that you have Excel already open with specified file and GetObject allows you to manipulate the file that is open in Excel
3. GetObject from .xls file creates "Workbook" object, not application object, so you need to use .Application property to access application

@junjun as a matter of courtesy to someone who answers your questions and provides you the code, you should at least get the spelling of English words correctly and avoid SMS style. There are no such words as "tks". This is quality forum, not facebook.

[fxshrat]

My previous post compared to CreateObject() example which isn't using saved Excel file also.

As for GetObject() function.
If using AB versions lower than 6.38 one may use this VBS function.

// VBS function by Bruce R.
EnableScript( "vbscript" );
<%
Function vbsGetObject( path )
	Err.Clear
	On error resume next

	Set Obj = GetObject( path ).Application

	If Err.Number <> 0 then
		vbsGetObject = 0
	Else
		Set vbsGetObject = Obj
	End if
End function
%>

script = GetScriptObject();
Workbook = script.vbsGetObject("C:\\test.xls");

if ( Workbook != 0 ) {
	// from post #2 at 
	// https://forum.amibroker.com/t/how-to-output-valume-to-excel-in-afl/25527/2
	Excel = Workbook.Application;
	Excel.Visible = True;
	Worksheet = Excel.ActiveSheet;
	Range = Worksheet.Range("A1", "A1");
	Range.Value = 123;
} else
    Error("File does not exist");