Excel Modeling

[jeffshap]

What is the best way to dymically build a spreadsheet in Excel based on data in FMP. For example, say I have 3 cells A1, B1, C1 and I want to populate A1 & B1 with values but I want to populate C1 with one of two different forumlas based on the relationship of other data in the database. I can't export the data because certain cells need to have formulas so the user can play with values and see other cells update.

This can be done w/Office Automation in other languages, but I am not sure how to best accomplish this in FMP.

[Lee Smith]

Have your tried to Export the data as an Excel file?

[jeffshap]

Yes but I need to export forumlas and formats too.

[Lee Smith]

I don't think that you can do that. AFAIK, the results is all that will export out. Even the formatting is a little different.

Lee

[Wim Decorte]

you can do it through XML and XSLT natively in FM. Check www.filemaker.com/xml for an example (I think it's still there).

The other approach would be to use VBscript generated from your FileMaker data. Check the Windows automation forum for more info on that.

[jeffshap]

In my situation, it seems the most effective way is going to build a VBS in a text field, export the text field to a file and execute the VBS. Very cumbersome but I think it is going to be the only way.

[Genx]

Very cumbersome but I think it is going to be the only way.

It's not THAT cumbersome...

Though when i used to do that i would come accross errors on certain pc's every once in a while where FM would say that the file had exported... but hadn't yet, i had to put 5 second pauses in after all my export / run's...

You could always try Grokitt Tools VBScript plugin, it's quite awesome really (if you are in fact on version 8.5 as your first post says)... plus you can do error checking ... and it's cheap, i think it's the cheapest plug-in i've purchased and the one i've used the most

[Brudderman]

If you have a formula (such as =A1+B1) in a text field in FM and export to Excel, it does not automatically import as a formula. Once in Excel, however, if you do a replace all (replacing = with =), Excel will convert the text to a formula. So it may be possible to construct your formulas in FM and then have Excel manipulate them with a macro. I don't know exactly what you're after in the way of formatting, but a VBA procedure in Excel would probably be easier to deal with than exported lots of VBS from FM. Some examples of the results you desire might help.

[jeffshap]

I appreciate the help on this and I will give some thought to doing more in Excel and less in a monster VBS.

If more info helps, my goal is to model inventory against events with variables. Live data is refreshed into the model and demand is projected. The data and projections span multiple worksheets and is pulled from multiple tables. I did this fairly easily in other langauages that had direct access to Excel Automation but I am fairly new to FMP.

Edited December 10, 2006 by Guest

[Wim Decorte]

I appreciate the help on this and I will give some thought to doing more in Excel and less in a monster VBS.

It wouldn't be a monster VBS at all to do this. It would actually be very straightforward and less lines of "code" than an XSLT.

[jeffshap]

I must be missing something. Is there an easier way to build a spreadsheet with thousands of values & forumlas? There is another contraint that ODBC won't necessalry work in my instance.

[Brudderman]

direct access to Excel Automation

What do you mean by Excel Automation? I'm not familiar with the term.

I guess I still don't understand why you need to do a Filemaker export of thousands of numbers, formulas and formats to create a whole new Excel sheet. It sounds like you have a model already built up in Excel with the formulas and formats that you want and only the data needs to be updated. Is that right?

If so, then why don't you just export from FM all of the data that you want ported to the Excel model and then import that data into the Excel model and replace the data that's already there? With properly constructed formulas, range names (either static or dynamic) and/or Excel procedures you should be able to then refer to the new data set and not have to rebuild formulas and formats from scratch.

If this isn't what you need to do, maybe you can explain further.

[jeffshap]

I haven't had a chance to tackle this again, but I wish it was as easy as just doing an export from one table. The data is pulled from multiple tables and raw data needs to summarized, analyzed to popluate cells. There are situations where I think I could get above 65K rows, a limit in Excel. Also, it is hard to explain but the model is not static, and the model itself changes based on trends born out at the time the model is built.

Maybe I am using the wrong term, but I have always called it "Excel Automation ". I started the process by dynamically building a VBS which I was saving to a file and then executing. Here are a couple of lines how the object model is exposed in VBS.

SET objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

SET objWorkbook=objExcel.Workbooks.Add()

SET oWS1 = objWorkbook.Worksheets(1)

oWS1.Cells(1, 1).Value = " "

oWS1.Cells(2, 1).Value = ".6"

oWS1.Cells(2, 2).Value = "200"

oWS1.Cells(2, 3).Value = "=B1*B2"

I think I am probably making this too complex and I will be back with you on what I come up with. I think it is going to be best handled by combining some analysis into a new FMP table, setup a TOG, export the raw data into files less than 65K records, use VBS to startup Excel and execute a VBA app to finish off the format. Oi!

I will f

[Stuart Taylor]

Do you think making excel behave like filemaker would be any easier?

Seems like you want FileMaker to be Excel ...

Simple answer it is not.

Thats not to say that it would be impossible ... infact i have a recurring obsession with working it out ... and i'm pretty close but i would expect it to take more than a year of dedicated effort to even get close.

I'll probably do it just to prove that its possible ... but i would not bother doing it for business reasons ... because excel already exists, so why bother?