New topic: How to involve Excel?
<http://forums.realsoftware.com/viewtopic.php?t=3265> Page 1 of 1 [ 12 posts ] Previous topic | Next topic Author Message AudreyHepburn Post subject: How to involve Excel?Posted: Mon Mar 06, 2006 11:16 am Joined: Tue Feb 21, 2006 11:34 am Posts: 123 Hi, I've got a question: how can I involve Excel in my RB projects, and what is possible? Can I open Excel files, and get information from them into my project? Can I save data to an Excel file in runtime? What else is (possibly) possible? Audrey _________________ RB 2008r1 on openSUSE 10.2 Top Bob Keeney Post subject: Posted: Fri Mar 24, 2006 9:30 pm Joined: Fri Sep 30, 2005 11:48 am Posts: 3463 Location: Lenexa, KS Take a look at ExcelApplication in the Language Reference. It requires that you have Microsoft Excel. On the Mac your app has to be located in a specific location in the Office folder. There are also some 3rd party solutions that can do some read and write of excel files. I would start at http://www.rbgarage.com. Top Ivan Thomson Post subject: Excel in REALbasicPosted: Thu Apr 06, 2006 5:16 pm Joined: Thu Jan 19, 2006 1:08 pm Posts: 4 Location: Scotland I know this is a bit off topic (or maybe not)...but REALbasic sure could use a more extensive set of example and text in the language reference regarding Excel (and OFFICE) commands in REALbasic. The information that is there is sketchy at best and does not really explain what to do with excel commands that do not comform to the RB way of doing things? There is not enough explaination. They just kind of toss you a few bones and say oh well that is that if you need more go look it up in Excel or something...yeah ok that is great... except Excel uses VB or VBA and this does not translate well into RB especially when dealing with RBs way of doing OFFICE commands. Thanks. Top Bob Keeney Post subject: Posted: Thu Apr 06, 2006 5:26 pm Joined: Fri Sep 30, 2005 11:48 am Posts: 3463 Location: Lenexa, KS Quote:The information that is there is sketchy at best and does not really explain what to do with excel commands that do not comform to the RB way of doing things? There is not enough explaination. They just kind of toss you a few bones and say oh well that is that if you need more go look it up in Excel or something...yeah ok that is great... except Excel uses VB or VBA and this does not translate well into RB especially when dealing with RBs way of doing OFFICE commands. You'll get no argument from me on this one. I suggest making an entry (along with the many others) into the feedback system http://support.realsoftware.com/feedback/ that the documentation for MS Office is sketchy (at best). If enough people complain maybe something will get done. Now that I'm done complaining, I've used Excel code examples from http://www.planet-source-code.com and converted it into RB successfully. Been a while since I've played with it but I know it can be done. Cheers, Bob Keeney Top kxir Post subject: How to involve Excel?Posted: Fri Jun 16, 2006 7:28 pm Joined: Tue Dec 27, 2005 11:42 am Posts: 32 It appears that you can control Excel spreadsheets from RB code, but there are restrictions. You can create a spreadsheet from scratch and put whatever you want in it and open it. If you are clever or try hard enough, you can read some spreadsheets into an RB app and manipulate the content any way you like. Here are the restrictions that I'm aware of: 1. On Macs all of the files (RB and Excel types) must be in one folder. (I find this an outrageous restriction. I refuse to live with it.) 2. If you want full Excel functionality, you need to work with Excel files that are saved in XML format, one of Excel's options in recent versions. And then you must deal with the complexity of the way Microsoft uses XML to define a spreadsheet. That could be very daunting, or fairly simple, depending on what features you want to use. 3. For bare-bones reading and writing files that Excel can use from an RB app, you can use tab-delimited text files. They work quite well as long as you don't need any formatting, formulas or fancy Excel features. The RB programming for this option is quite simple. See my post (Excel Worksheet and Realbasic TabPanel control) on another thread. Good luck. Ron Top SteveW Post subject: Re: Excel in REALbasicPosted: Tue Jun 20, 2006 2:37 am Joined: Thu Nov 10, 2005 4:07 am Posts: 678 Location: Ely, England Ivan Thomson wrote:REALbasic sure could use a more extensive set of example and text in the language reference regarding Excel There is not enough explaination. I completely agree, REAL must have spent quite a bit of development time creating the office automation classes and very little time updating the help file to explain how to use them. The same is true with OLE objects. A comprehensive tutorial would be great... Top damon Post subject: Posted: Mon Jun 26, 2006 5:49 pm Joined: Mon Oct 17, 2005 6:43 pm Posts: 116 Hi , Hope this helps ' create a excel application Dim excel as ExcelApplication ' define a workbook so you can add to it Dim book as ExcelWorkbook ' define a sheet so you can play with it Dim sheet as object ' clear the excel so it can be used. excel = new ExcelApplication ' this will hide excel so as you fill it in or read from it the user will not see what you are doing. excel.Visible = false ' it is true buy default ' this will tell excell not to ask the user to overwrite the file if it already exists, it will just overwrite the file. excel.AlertBeforeOverwriting=false ' this will add a book to your spreadsheet book = excel.Workbooks.Add ' most excels add three sheets by default to the spreadsheet, use this commadn to delete the unwanted ones. excel.activesheet.delete excel.activesheet.delete ' command slike this will simply fill in data excel.Range("A1").Value="Purchase Order" excel.Range("B1").Value="Via Daisychain Bureau" excel.Range("C1").Value="Sent "+da.longdate+" "+da.longtime ' this will merge cells for you excel.range("C1:D1").mergeCells=true ' this will set the selected range to a number format excel.Range("C4").numberFormat="0" ' this will set the range font to italics excel.range("C3","D4").font.italic=TRUE ' the default is false ' this will set the range to be horizontal allignment. excel.range("C3","D4").HorizontalAlignment=3 ' 1=general ' 2=left ' 3=centre ' 4=right ' 5=will fill the line with the text, weird but cool. ' 6=justify ' 7=centre across selection ' 8=distributed ' this will set the range to be centered vertically. excel.range("C3","D4").VerticalAlignment=2 ' 1=top ' 2=midle ' 3=bottom ' 4=justify ' 5=distributed ' this will change the row height of the specified row(s). excel.range("A1").rowHeight=20 ' this will change the column width of the specified column(s) excel.range("A1")ColumnWidth = 29.57 ' this will set the range font to bold excel.range("A1","C1").font.bold=true ' the default is false ' this will change the contents of the selected cell font excel.range("A1","C1").font.name="Arial" ' this will change the font size of the selected cells. excel.range("A1","C1").font.size=12 ' this will change the format of the selected cells to stop excell from turning it to scientific notation. excel.Range("b3").numberFormat="@" ' this is how you set the name of the current sheet, it appears in the tab at the bottom of the sheet excel.ActiveSheet.Name = order ' set the sheet name ' this will set the current cell to a date excel.Range("B4").Value="30/10/2006" ' this will change the format of the current cell to display the date in the way you want. excel.Range("B4").numberFormat="mmmm d, yyyy" ' this will set the current cell to a time in 24 hour clock mode excel.Range("B9").Value="22:55" ' this will change the format of the current cell to display the time in the way you want. excel.Range("B9").numberFormat="hh:mm AM/PM" ' this will change the background colour of a cell or range of cells excel.range("A1","F1").interior.ColorIndex=5 ' this will change the text colour of the cell or range of cells excel.range("A1","C1").font.colorIndex=2 ' 1=black ' 2= white ' 3= red ' 4= bright green ' 5=mid-dark blue ' 6= yellow ' 7=pink ' 8=cyan ' 9=dark red '10=dark green '11=dark blue '12=dark yellow '13=purple '14=sea green/blue '15=light grey '16=grey '17=light purple/mouve '18=plum '19=cream '20=light baby blue '21=dark purple '22=flesh '23=blue '24=light grey '25= dark blue '26=bright pink '27=bright yellow '28=bright cyan '29=21 '30=9 '31=14 '32=5 '33=mid dark cyan '34=light pastel blue (20) '35=light pastel green '36=pastel yellow '37=pastel blue '38=pastel pink '39=pastel purple '40=light flesh '41=mid blue/purple '42=33 '43=olive '44=gold '45=gold/orange '46=orange '47=purple/mauve '48=grey (darker than 15) '49=teal '50=sea green '51=british racing green '52=brown '53=dark orange '54=dark pink '55=dark mauve '56=dark grey ' colour is subjective so if you disagree you try to label 57 of them ' this will right align the cell excel.range("C6","C9").HorizontalAlignment=4 ' this will change the selected cell to curency i.e. $12,345.67 excel.Range("E"+str(16+i)).Style = "Currency" ' this is how you get a cell to do maths. a simple SUM used here. excel.Range("F3").Value="=SUM(D6*E6+")" ' printer specific options ' this will set the printer to print in landscape, 1=portrait (default) excel.activesheet.pagesetup.orientation=2 ' changes the left margin of the page to 25 ( I think it is MM but not real sure) excel.activesheet.pagesetup.leftMargin=25 ' changes the right margin of the page to 25 ( I think it is MM but not real sure) excel.activesheet.pagesetup.rightmargin=25 ' changes the zoom setting to false i.e. dont enlarge the image to fit a page default=not really sure excel.activesheet.pagesetup.zoom=false ' this will set the pages wide value to 1 so no matter how big the spreadsheet is it will be one page wide when printed excel.activesheet.pagesetup.fitToPageswide=1 ' this will set the pages high value to 1 so no matter how big the spreadsheet is it will be one page high when printed excel.activesheet.pagesetup.fitToPagesTall=1 ' really cool, excel will auto size all the columns so that everything is laid out needly, you could then add more room to areas if you wanted later. excel.columns().AutoFit ' this will unlock specific cells for editing, the defalt is false excel.range("D6:E9").Locked=false ' this will select a specific cell 'excel.range("B3").select_ ' this will password protect a specific sheet, if more than one sheet the others are not protected. excel.activesheet.protect("passwordhere") ' this will add another sheet sheet=excel.sheets.Add ' this will save a spreadsheet excel.activeworkbook.saveAs(f.absolutePath,format for save) ' Format for save table ' 1= XLS '44= HTML '23= CSV '20= TXT '46= XML ' this will save a spreadsheet with a password protection of the spreadsheet, so the user has to enter a password in order to open it. excel.activeworkbook.saveAs(f.absolutePath,format for save,"passwordhere") ' this will close the active work book if it is not saved it will ask the user to save it excel.activeworkbook.close ' this will close the active work book if it is not saved it will NOT ask the user to save it excel.activeworkbook.close (false) ' never used this but sure I read it somewhere. ' this will quit excel and close the process in windows. excel.Quit Thanks Damon Top shawngiese Post subject: Posted: Thu Aug 09, 2007 7:17 am Joined: Sun Apr 16, 2006 9:25 pm Posts: 280 Location: Switzerland Thank you for that great list. It helped me figure out the close dialog (without saving anything). _________________ RB2006-2010, MacOSX 10.6.2, Windows XP Pro SP2/ Vista/ Server 2008 MCSE, Microsoft Small Business Specialist - http://www.sirkevi.com Realbasic sample source code at http://software.sirkevi.com Top grogan197 Post subject: Posted: Sun Dec 16, 2007 4:52 pm Joined: Mon Dec 10, 2007 5:41 pm Posts: 7 Im trying to open an existing excel document so that I can add some data to it then print it. Does anyone know what code you need to use to open an existing excel document instead of creating a new one? Is it possible for RB to give excel the command to print too? or would the user have to select print from within excel. Thanks! Top SandyB Post subject: Posted: Sun Dec 16, 2007 9:57 pm Joined: Fri Jan 06, 2006 3:56 pm Posts: 299 Location: Longwood, FL, USA Can anyone (that's spelled Damon) post a similar list of commands to operate Word from RB? Especially the commands to start mailmerge, print it, and then quit Word back to RB? Many people could benefit from that? Thanks in advance! Top dgdavidge Post subject: Re: How to involve Excel?Posted: Fri Mar 22, 2013 10:13 pm Joined: Fri Jun 02, 2006 1:43 pm Posts: 187 Location: Santa Ynez, CA To reprint the top 3 rows on each page: excel.ActiveSheet.PageSetup.PrintTitleRows = "1:3" Top pfargo Post subject: Re: How to involve Excel?Posted: Sat Mar 23, 2013 4:56 am Joined: Mon Oct 13, 2008 4:26 am Posts: 48 If you don't mind spending a very small amount of money I highly recommend Eugene Dakin's mini books. Program Excel with RealStudio in Windows Program Word 2010 with RealStudio in Windows They cover most of what most people will need, are updated from time to time and Eugene will pretty much answer any questions you may have. I'm surprised he hasn't picked up on this thread. Link: http://great-white-software.com/rblibrary/index.php?main_page=index&manufacturers_id=34 Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 12 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
