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@xxxxxxxxxps.com,
"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@xxxxxxxxxps.com
> 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@xxxxxxxxxps.com,
"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@xxxxxxxxxps.com,
"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
> > >
>
>
>