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: [email protected] 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 [email protected], "gp_sydney" <[EMAIL PROTECTED]> 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 [email protected], "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 > > >
