MathGroup Archive 2010

[Date Index] [Thread Index] [Author Index]

Search the Archive

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


  • Prev by Date: Re: Using variable name as string AND value ?
  • Next by Date: Re: mathematica doesn't find periodic response with laplace transform
  • Previous by thread: Re: Using Export to create a spreadsheet containing formulas
  • Next by thread: use of a button