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@xxxxxxxxxps.com,
"Ton Sieverding"
<ton.sieverding@...> 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@xxxxxxxxxps.com
> 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@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
> > > >
> > >
>
>
>