[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[amibroker] Re: Interact with OPENED Excel worksheet, Possible?



PureBytes Links

Trading Reference Links



I'm too busy right now for a detailed explanation, but I'll try to help enough to get you started.  As someone mentioned, you can drive Amibroker from Excel, but I'm assuming that you want Amibroker to be the "master" and Excel to be the "slave".  There are two ways to address the general problem of working with Excel from Amibroker -
  1. Launch an instance of Excel from Amibroker using CreateStaticObject().  This will create a static COM "connection" and you can do almost anything you want with automation.  You then load any workbook that you want into that instance.  The drawback of this approach is that there is NOT a DeleteStaticObject() call.  So, if you close that connected instance of Excel down, you then have some issues.
  2. Connect to a running instance of Excel.  There are two ways to do this with a script call.  Get the first instance OR get a particular instance based on a loaded file name.  Again, you can then do almost anything that you want via COM.  The potential issue with this approach is that it can get very complicated if you have multiple workbooks open in one instance of Excel and have other instances also open.
IMO, you should get what you want working with #1.  It is the easiest to use - even with the drawback.

But, if you have to do #2, you will need two key pieces of info -
  • http://support.microsoft.com/kb/288902
  • You can return a COM object handle from _vbscript_ (in an AFL) to an AFL code variable and use it in object references.
Lastly, FWIW, I've found that there are a couple of other things that you may need after this.  One is getting range data into and out of Excel quickly.  But, those are stories for another day and something that I'll eventually cover on AmibrokerU.

-- Bruce

--- In amibroker@xxxxxxxxxxxxxxx, "abxlab" <w2jchen@xxx> wrote:
>
> update it on my own...
> seems nobody bother to try this way. I finally figured it is impossible for AB (thru Excel.Application object) to access an ever opened Excel spreadsheet. It will actually open another copy of the same file, however it is in READ-ONLY mode.
>
> --- In amibroker@xxxxxxxxxxxxxxx, "amiespresso" w2jchen@ wrote:
> >
> >
> > Hello folks,
> >
> >
> >
> > I've been searching for a solution for quite a while and explored all
> > what I could get, now I feel it's time to ask for help after enough
> > homework done with no luck...
> >
> >
> >
> > To put it simple, I'd like to have AB exchange data back and forth with
> > an OPENED Excel file. Is it all possible?
> >
> >
> >
> > To illustrate it more, I need a bridge b/w AB and Excel so that AB could
> > exchange data with Excel using Scan/Explore, or even during Backtesting
> > execution. The tricky part here is - I have the Excel worksheet opened,
> > for example, Sheet1 is for AB to dump something there, while Sheet2 has
> > my VBA code. And hopefully I still should be able to interact with Excel
> > while AB "talking" to THIS Excel instance.
> >
> >
> >
> > Here I tried the following:
> >
> > 1) XLPlugin - It looks like a right solution by moving data thru memory
> > copy between AB/XL. However after spending many days on this direction,
> > I was blocked by things I couldn't fix without its source code.
> > Basically, its sample.xls couldn't execute properly if AA has
> > Periodicity set as any non-Daily value. XLCase() doesn't work if for
> > example I run Scan/Explore on Hourly bars, which seem so strange. My
> > only guess is VB.net changed its RtlMemoryCopy() used by XLPlugin. This
> > function now has different signature from VB6, which may break things
> > somehow.
> >
> >
> >
> > 2) I tried some sample code that uses "Excel.Application"
> > syntax. The problem here is that AB will initiate another copy of Excel,
> > NOT the one I opened with something loaded already. It's OK to
> > create a new sheet, load it with something, save it and close. But I
> > need interaction with my current Excel.
> >
> >
> >
> > 3) Plain text or CSV file from AB to Excel is straightfoward, but
> > it's not capable of doing quick data exchange between AB and Excel.
> >
> >
> >
> > Can anybody here to enlighten me with some idea or code if you ever
> > faced similar problem and resolved it?
> >
> >
> >
> > Your input is much appreciated!
> >
> >
> >
> >
> >
> > P.S. my envrionments
> >
> > Windows XP and/or Windows 2003 Server Std Edition
> >
> > Amibroker v5.20
> >
> > Office Professional Edition 2003
> >
> > Visual C++ 2005 Redistributable
> >
>


__._,_.___


**** IMPORTANT PLEASE READ ****
This group is for the discussion between users only.
This is *NOT* technical support channel.

TO GET TECHNICAL SUPPORT send an e-mail directly to
SUPPORT {at} amibroker.com

TO SUBMIT SUGGESTIONS please use FEEDBACK CENTER at
http://www.amibroker.com/feedback/
(submissions sent via other channels won't be considered)

For NEW RELEASE ANNOUNCEMENTS and other news always check DEVLOG:
http://www.amibroker.com/devlog/





Your email settings: Individual Email|Traditional
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch to Fully Featured
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___