Idea is to have more than one (say 4 for general purpose) afls EXPORE. The exploration output is to be written to different sheets of an excel file. Two conditions here:
The starting point of each exploration maybe different e.g. Sheet1 cell A1 for one exploration, Sheet 2 cell B3 for second exploration etc…
Secondly there could be further calculations within each sheets like conditional formatting, therefore when writing to the sheet, it should only be restricted to particular cells. That is to say not to overwrite the whole sheet.
The number of columns in each sheet are quiet high, so maybe even 60 at it.
Correct me if I’m wrong - you want to export from AmiBroker to xlsx and make some formatting from AFL ? If so, then probably OLE & VBA in AFL is the way to go for you - but IMHO it’s a bit vanity over sanity. You can easily schedule a batch to export the data and from then do the conditional formatting ect … . Of course feel free give it a shoot.
Still a grey area. I already have xlsx files that have 4 sheets as RAW wherein I paste the exploration results manually. The said 4 raw sheets are further linked to other sheets within the file as well as to number of other excel files that create shortlists. That’s why conditional formatting comes to the fore. As such a csv output may not technically help. And hence a restriction as far as batch file export is concerned.
A way around would be to create number of output files through batch process, link them further to the RAW files & then get the required output. But this would be error prone as any change in the basic afl &/or excel structure will need to be implemented at number of places. (Especially with functions like vlookup -> which do not dynamically change the column numbers if any changes are made to RAW files).
Hence requirement to write to particular sheets in an exel sheet.
Idea is not just to create specific shortlists but try permutation combination of various parameters while selecting the scrips to trade.
While on the matter, any basic synatx or READ for the OLE or VBA would certainly help.
Seems like an spreadsheet on a spreadsheet linked to…a spreadsheet situation. Perhaps you can do it all in AB.
Anyway, as for OLE you need to look in the old vault. This thread looks like a good start.
The round about is creating apx for each exploration & writing it to csv. The same is linked to the excel file RAW sheets.
A bit of hook is use “Save as” with diffeent file name for the excel (not csv files) and to break links in excel to make it work. A crude way but still works to a level.
While on the matter can anybody suggest how to break links only for particular cells in sheet instead of the whole sheet or file? Maybe a bit off topic, but should help a lot.