Jump to content

Excel Modeling


jeffshap
 Share

This topic is 5760 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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?

Link to comment
Share on other sites

This topic is 5760 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.