Re: Using Export to create a spreadsheet containing formulas
- To: mathgroup at smc.vnet.net
- Subject: [mg106985] Re: Using Export to create a spreadsheet containing formulas
- From: Norbert Marxer <marxer at mec.li>
- Date: Sat, 30 Jan 2010 07:13:46 -0500 (EST)
- References: <hjonb7$4ii$1@smc.vnet.net>
On Jan 27, 7:43 am, gekko <pfall... at gmail.com> wrote: > Hi Group, > I would like to know how (or if) I can use the Export function to > create a spreadsheet with formulas in some of the cells. The > documentation mentions how formulas can be Imported but it's not clear > how to do the same with Export. > > Alternatively, if anyone knows of an alternative to Export that would > be great too. (Note, I'm not really interested in an "interactive" > solution, e.g. updating the spreadsheet while it's open. The goal is > to automate the creation of a potentially large number of spreadsheets > with a standard form). > > Many thanks in advance! > > Thanks, P. Hello If you want to interactively manipulate an Excel file, you could use NETLink. This involves the following steps. (*This installs ... and displays a dialog field to open an Excel file - you could also open without dialog window of course*) Needs["NETLink`"] InstallNET[] excel = CreateCOMObject["Excel.Application"] If[! NETObjectQ[excel], Return[$Failed]] excel[Visible] = True excel[FindFile[]] (*This selects the Excel Workbook and Excel Worksheet (here the first worksheet) *) workbook = excel[Workbooks[1]] worksheet = excel[Workbooks[1][Worksheets[1]]] (*This writes two numbers and a formula into three Excel cells*) srcRange = worksheet@Range["B12"]@Resize[1, 1]; srcRange@Value = 88; srcRange = worksheet@Range["C12"]@Resize[1, 1]; srcRange@Value = 99; srcRange = worksheet@Range["D12"]@Resize[1, 1]; srcRange@Value = "=B12*C12"; I hope you can use this information as a starting point. In another post, which a sent yesterday, you can find some more information. http://groups.google.com/group/comp.soft-sys.math.mathematica/browse_thread= /thread/ae3ccc6993e5e20f/8725ee118f87505f#8725ee118f87505f Best Regards Norbert Marxer