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

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



PureBytes Links

Trading Reference Links

Absolutely!  I've found many differences over the different versions of XL -
very maddening and frustrating. Found that I had to program for the earliest
version.

d 

> -----Original Message-----
> From: amibroker@xxxxxxxxxxxxxxx 
> [mailto:amibroker@xxxxxxxxxxxxxxx] On Behalf Of gp_sydney
> Sent: Saturday, July 14, 2007 7:29 PM
> To: amibroker@xxxxxxxxxxxxxxx
> Subject: [amibroker] Re: Difficulities in getting COM syntax 
> with Excel VBA
> 
> 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
> 
> 
> 



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/