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

[amibroker] Re: Difficulities in getting COM syntax with Excel VBA



PureBytes Links

Trading Reference Links

One thing to note is that I'm still using Office 97, so there could be
differences with later versions of Excel.

GP


--- In amibroker@xxxxxxxxxxxxxxx, "Ton Sieverding"
<ton.sieverding@xxx> wrote:
>
> Thanks, I will DebugView step by step the process to see where
things go wrong.
> Will let you know the result ...
> 
> Ton.
> 
>   ----- Original Message ----- 
>   From: gp_sydney 
>   To: amibroker@xxxxxxxxxxxxxxx 
>   Sent: Saturday, July 14, 2007 11:13 AM
>   Subject: [amibroker] Re: Difficulities in getting COM syntax with
Excel VBA
> 
> 
>   Ton,
> 
>   I didn't have that problem, but if an error occurs before the workbook
>   is closed, the Excel object will still be active and locking the
>   output file. The Excel process then needs to be killed in Task
>   Manager. However, the locked file would prevent it being opened in
>   Excel as well.
> 
>   Check in the Task Manager process list (ie. under the Processes tab)
>   that no instances of Excel are running before running the AFL code. If
>   AB still locks up, check the Task Manager process list again and see
>   any Excel processes are still running. If so, try killing them.
> 
>   Otherwise I can only suggest using DebugView and trace statements to
>   find out exactly how far it gets, although if you say the XLS file is
>   being generated and that you can open it in Excel, then it would seem
>   that it is getting right through it.
> 
>   GP
> 
>   --- In amibroker@xxxxxxxxxxxxxxx, "Ton Sieverding"
>   <ton.sieverding@> wrote:
>   >
>   > I did a quick test with underneath mentioned code. Although the code
>   is creating the requested XLS file and I can read the file in Excel,
>   when running the AFL formula in AB the program hangs and I must end
>   the AB task with Windows Task Manager. Any idea ?
>   > 
>   > Regards, Ton.
>   > 
>   > ----- Original Message ----- 
>   > From: gp_sydney 
>   > To: amibroker@xxxxxxxxxxxxxxx 
>   > Sent: Saturday, July 14, 2007 10:06 AM
>   > Subject: [amibroker] Re: Difficulities in getting COM syntax with
>   Excel VBA
>   > 
>   > 
>   > I can fix the date issue by printing it in US format:
>   > 
>   > cell.Value = StrFormat("%1.0f/%1.0f/%1.0f", mm, dd, yy);
>   > 
>   > That then correctly displays in Australian format with Australian
>   > regional settings.
>   > 
>   > Still not sure why the "fnt.Bold = True" statement is taking so long
>   > though.
>   > 
>   > GP
>   > 
>   > --- In amibroker@xxxxxxxxxxxxxxx, "gp_sydney" <gp.investment@>
wrote:
>   > >
>   > > Winston,
>   > > 
>   > > When it comes to Microsoft applications and getting syntax right,
>   > > you'd better pad up those brick walls or your head is going to get
>   > > very sore!
>   > > 
>   > > Here's some code that will write the date and the opening and
>   closing
>   > > prices to an XLS file. Put it in an exploration and just run it
>   over a
>   > > single symbol, or it's going to take a very long time.
>   > > 
>   > > This code creates a new workbook and uses the first worksheet.
>   It sets
>   > > the number format, alignmnent, and width of columns A, B, and C,
>   puts
>   > > titles in the first row of each of the columns with bold font and
>   > > centred text, then fills the other rows with the data.
>   > > 
>   > > Not sure if I've got something wrong here, but I do get a
couple of
>   > > odd things happening. Firstly, the statements that set the
font seem
>   > > to take an unusally long time, and when I look through the
resulting
>   > > file, some of the dates have US date format and some have
>   Australian.
>   > > 
>   > > Also, if you don't specify a path for the file, it seems to
>   default to
>   > > the "My Documents" folder.
>   > > 
>   > > Regards, GP
>   > > 
>   > > 
>   > > excel = CreateObject("Excel.application");
>   > > wb = excel.WorkBooks;
>   > > wb.Add;
>   > > wb1 = wb.Item(1);
>   > > ws = wb1.WorkSheets;
>   > > ws1 = ws.Item(1);
>   > > 
>   > > cmn = ws1.Columns("A");
>   > > cmn.NumberFormat = "d/mm/yyyy";
>   > > cmn.ColumnWidth = 12;
>   > > cmn.HorizontalAlignment = 4; // 2=left, 3=centre, 4=right
>   > > cmn = ws1.Columns("B");
>   > > cmn.NumberFormat = "$0.000";
>   > > cmn.ColumnWidth = 10;
>   > > cmn.HorizontalAlignment = 4;
>   > > cmn = ws1.Columns("C");
>   > > cmn.NumberFormat = "$0.000";
>   > > cmn.ColumnWidth = 10;
>   > > cmn.HorizontalAlignment = 4;
>   > > 
>   > > cell = ws1.Range("A1");
>   > > cell.Value = "Date";
>   > > fnt = cell.Font;
>   > > fnt.Bold = True;
>   > > cell.HorizontalAlignment = 3;
>   > > cell = ws1.Range("B1");
>   > > cell.Value = "Open";
>   > > fnt = cell.Font;
>   > > fnt.Bold = True;
>   > > cell.HorizontalAlignment = 3;
>   > > cell = ws1.Range("C1");
>   > > cell.Value = "Close";
>   > > fnt = cell.Font;
>   > > fnt.Bold = True;
>   > > cell.HorizontalAlignment = 3;
>   > > 
>   > > dn = DateNum();
>   > > for (i = 0; i < BarCount; i++)
>   > > {
>   > > yy = Int(dn[i] / 10000) + 1900;
>   > > mm = Int((dn[i] % 10000) / 100);
>   > > dd = dn[i] % 100;
>   > > cell = ws1.Range("A"+(i+2));
>   > > cell.Value = StrFormat("%1.0f/%1.0f/%1.0f", dd, mm, yy);
>   > > cell = ws1.Range("B"+(i+2));
>   > > cell.Value = Open[i];
>   > > cell = ws1.Range("C"+(i+2));
>   > > cell.Value = Close[i];
>   > > }
>   > > ws1.SaveAs("c:\\Temp\\OpenClose_"+Name()+".xls");
>   > > wb.Close;
>   > > 
>   > > 
>   > > 
>   > > --- In amibroker@xxxxxxxxxxxxxxx, "peakwk79" <phaser2679@> wrote:
>   > > >
>   > > > Hi,
>   > > > 
>   > > > I have been trying to implement the COM interface to Excel
for the
>   > > > past few days but I simply can't get the syntax right.
>   > > > 
>   > > > For starters, I would like to get the opening and closing
>   prices of a
>   > > > particular stock on a particular trading day into Excel. 
>   > > > 
>   > > > Can someone help?
>   > > > 
>   > > > 
>   > > > - Winston
>   > > >
>   > >
>   >
>




Please note that this group is for discussion between users only.

To get support from AmiBroker please send an e-mail directly to 
SUPPORT {at} amibroker.com

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

For other support material please check also:
http://www.amibroker.com/support.html
 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/amibroker/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/amibroker/join
    (Yahoo! ID required)

<*> To change settings via email:
    mailto:amibroker-digest@xxxxxxxxxxxxxxx 
    mailto:amibroker-fullfeatured@xxxxxxxxxxxxxxx

<*> To unsubscribe from this group, send an email to:
    amibroker-unsubscribe@xxxxxxxxxxxxxxx

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/