Generate Excel and Word from within FM

[e34m5]

Is there an API that can be used from within FM to generate Excel or Word reports. I don't mean export.

I need something like VB that can have full control over Excel or Word.

I've searched but all I can fing is info on Exporting.

[Ben Goldstein]

You can have FileMaker create files in word/excel's xml format (if using word/excel 2003 or later). This would give you full control over how it will look. This can be done setting s text field in FileMaker then exporting the field to a file.

Easiest to first create however you want the file to look like in word/excel, then save it as xml, then open with notepad(or other text editor) and paste into FileMaker text field. Next step would be to have scripts/calculations set that text field prior to exporting.

Ben Goldstein

www.swconsulting.com

www.cardsfm.com

[e34m5]

Wow. That's really convoluted. So the answer is NO then. No way to control Excel from within FM.

[comment]

You need to be more specific (preferably MUCH more specific) about what exactly does "control" mean.

[Lee Smith]

Can you name any two other applcations where one can be controled by another?

Lee

[e34m5]

Of course I can. From within VB, Delphi, MSAccess, etc. I can easily control Excel, Word, Outlook, AutoCAD, etc.

Why is this so hard to understand. That's what API's are for. It's just like if you were using the other app yourself.

This allows me to code what a report is going to look like completely from within the control if the calling app.

I guess FM does not have the ability to get out to any other windows app that has an exposed API. Most development environments allow control of these via objects.

Like this code below:

        oExcel = CreateObject("Excel.Application")

        oBook = oExcel.Workbooks.Add

        oSheet = CType(oBook.Worksheets(1), Excel.Worksheet)

        oSheet.Name = "HDM"



        oSheet.Cells(1, 1) = "HOME"



        With oSheet.Cells(1, 1)

            .Font.Size = 24

            .Font.Bold = True

        End With



        oSheet.Columns("A:B").ColumnWidth = 19

        oSheet.Range(ColumnLetter(j) & 2 & ":" & ColumnLetter(j + 2) & 2).BorderAround(xlContinuous)

        oSheet.Range(ColumnLetter(j) & 2 & ":" & ColumnLetter(j + 2) & 2).HorizontalAlignment = xlCenterAcrossSelection



        oSheet.Cells(3, j) = "QTY"

        oSheet.Cells(3, j).Font.Bold = True

        oSheet.Cells(3, j).HorizontalAlignment = xlCenter



        oSheet.Cells(3, j + 1) = "Daily"

        oSheet.Cells(3, j + 1).Font.Bold = True

        oSheet.Cells(3, j + HorizontalAlignment = xlCenter



        oSheet.Cells(3, j + 2) = "Weekly"

        oSheet.Cells(3, j + 2).Font.Bold = True

        oSheet.Cells(3, j + HorizontalAlignment = xlCenter

[comment]

I am not a Windows user, so I can only point you to this page:

http://www.filemaker.com/help/html/scripts_ref2.37.54.html#1029233

You should also explore the Windows Automation section of these forums:

http://fmforums.com/forum/showforum.php?fid/38/

[Ben Goldstein]

you can write the visual basic to a field, then export it to a .vbs file then execute it all within FileMaker. I actualy think xml is less cumbersome then writing vba but guess it depends on what your used to..

[e34m5]

Doubel translaltion. How can you say that is easier.

I am stunned that FM has no way to properly interface with Windows. How can you create real business apps then. FM is then no a seriuos enough platform to do this with.

Unfortunately one of my clients was sold a bill of goods when it comes to FM's abilities. Now I need to convnce them to totally cross over to a real enterprise platform.

[mr_vodka]

As I posted on the other forums, there is no real API.

However, I'm not sure if its fair to ask FMI to create an API set that will interface directly with MS Office. Wouldn't that be a direct conflict to their competition on Microsoft's side?

The products that you mention earlier, VB, Delphi, MSAccess, arent great examples as Microsoft stuff always can talk with other Microsoft applications by nature.

As for FileMaker, since its a database and not a programming language, I dont know if I can fairly fault it although it would be a nice feature.

[Ben Goldstein]

Have given you 2 ways to integrate with excel/word.. There maybe some 3rd party plug-ins that can help as well. I have done lots of integrations with both excel/word and FileMaker in large enterprises.

XML is easier with a FileMaker integration with Word/Excel from the standpoint that there is no programming involved (just copy/paste and fill in the blanks). Also think xml is also the future of application integration which is why Microsoft is going that route with the latest versions of their apps.

Obviously you've worked extensively with other apps and have a bias for those. I agree Microsoft apps and full programming environments will integrate better with each other than a 3rd party app. Sorry you feel your client was sold a bill of goods as I understand it that "FileMaker has built in functions specifically to programically change other Windows applications", it does not. However the other options xml/output to vbs exist and are easy to implement..

Ben

[Wim Decorte]

The other apps have their own API (the same one you'd use from Delphi or VB or any other tool) so the real question is: how can I leverage that from within FM?

Any number of ways, let me use VBscript as the example: you can dynamically generate your VBscript syntax in a FM field and then export it to a *.vbs file and execute it with the Send Event script step. Or you can use a plugin that can take VBscript syntax from a field and execute it, returning the result to FM. There are at least 2 plugins that can execute VBscript syntax. One of them free.

Other plugins let you use other scripting / programming environments like java, PHP,...

So it is all possible and not terribly convoluted either. But it's not a nicely integrated as say VBA.

If you prefer to use XML then that's another option. In my opinion there are enough options to fit any developer's skill set / preference.

[Wim Decorte]

As for FileMaker, since its a database and not a programming language, I dont know if I can fairly fault it although it would be a nice feature.

I don't agree here. A desktop database / workgroup database like FM does not exist on its own island. In fact FMI has been pushing the message that FM is not an island but integrates niceley. And they've given us some tools like XML to make it happen.

But what FM really lacks (except through the plugin API) is something like Access's VBA editor where you can write code in an easy syntax that touches the OS and the API of other applications like all of the Office apps.

The fact that we don't have this exact functionality doesn't stop us from doing this kind of integration though. Without plugins it takes jumping through a few hoops.

[Wim Decorte]

Can you name any two other applcations where one can be controled by another?

Lee

All of the Office Apps (Word, Excel, Outlook, Access) and the extended Office apps like Project, Visio,... It's been around forever. I've shown this at a few devcons now. Every app has its own published app that you can use in VBA within other apps, or in OS scripting (like VBscript) or in real programming environments (like VB / VB.NET, C++, C#, java,...)

Even Google Docs is now going that way, where you an embed scripting.

[e34m5]

^^ Bingo. FM is not a real programming language which in turn makes things much more difficult. However, even interpreted languages such as those found in Paradox, FoxPro can all reference API's and DLL's

BTW, Delphi, Java, C++ are not MS products and they do fine.

So if FM is so good, show me an example of how to do the following within the code of a button click:

- Add a reference to a DLL

- Instantiate an object to this DLL

- Execute commands against this DLL

I've been a developer for over 20 years now and I can assure that I hadn't even heard of FM until 2 weeks ago. Neither had any of my colleagues who have been coding even longer.

Edited June 3, 2009 by Guest

[e34m5]

Well

[Ben Goldstein]

? Your initial question was can FileMaker create Excel/Word. The answer is yes using the above described methods.

This latest question is about dlls? It has been stated in two posts already that FileMaker is not a full fledged program environment (one by me and one by Wim). It also does not sell itself as one!

Anything you an do in vbs you can do in FileMaker by outputing the vbs file then executing it (one button click). Am not an expert at vbs but if vbs can do all of the dll questions you have then the answer is yes, if not then the answer is no.

You seem to be trying to pick a platform fight while some of us are trying to help you in your situation.

[mr_vodka]

Man I feel left out... lol

As for FileMaker, since its a database and not a programming language

:(

[e34m5]

All youe needed to answer originally is NO. The fact is FM cannot access another windows app.

Using VBS or XML is tanatamount to you claiming to speak German when in fact I say something to you and then you turn to your freind that does speak German.

I'm just trying to understand why anyone would use FM to create an application. It is even more crude than MSAccess.

Heck Access at least supports SQL (not very well mind you).

Reminds me of the old days of PFS an other garbage like that.

[mr_vodka]

Every tool has its pluses and minuses. There are plenty of things FM can do better than Access. There are plenty of things Access can do better than FM. There are things that MySQL can do better than both.

[comment]

Every now and then, some whippersnapper will walk in and inform us what a bunch of idiots we are for using Filemaker. Usually, they don't have a clue about what Filemaker can and cannot do (perhaps they didn't even hear about it until 2 weeks before), and aren't interested in learning either.

There's no doubt that your client made a serious mistake: they either shouldn't have bought Filemaker, or they shouldn't have hired you to develop in it.

[e34m5]

I've been fighting FM for quite a while. I was hired to fix the issues they have with FM because of thr seriuos limititation it has.

I will challenge you to create in FM any application comparable to what I have created in a real platform.

FM is neither a good database nor a good GUI maker.

It was obviuosly created so that very inexperienced people can make something. Unfortunately some of these people have real businesses and now have issues.

BTW. This is the fouth client that has asked me to convert them away from FM.

Good for me. Keeps in business. You all keep making FM apps so that I can fix them.

Thanks.

[LaRetta]

:jester: Good words, Comment!

cxxxv, then go away so we can get back to work helping people who WANT to design using FileMaker. After all, the more time we waste responding to your babble, the fewer shoes your children will have.

[Lee Smith]

All of the Office Apps (Word, Excel, Outlook, Access) and the extended Office apps like Project, Visio,... It's been around forever. I've shown this at a few devcons now. Every app has its own published app that you can use in VBA within other apps, or in OS scripting (like VBscript) or in real programming environments (like VB / VB.NET, C++, C#, java,...)

Even Google Docs is now going that way, where you an embed scripting.

I was understanding that the OP was meaning control as a strieght contol of one applications tools by another applictions tools, not using something like VB or AppleScript.

BTW, I moved this thread here, so that it would catch your eye, figuring if anyone could help the OP, it would be you.

Lee

[Lee Smith]

I've been fighting FM for quite a while. I was hired to fix the issues they have with FM because of thr seriuos limititation it has.

I will challenge you to create in FM any application comparable to what I have created in a real platform.

You all keep making FM apps so that I can fix them.

Thanks.

Give it to me.

[e34m5]

Make an application that will read AutoCAD entity block information. Display that to the users. Allow them to change the value of the attributes and then write them back to AutoCAD.

Users should also be able to fill in required information in a grid and let the system automatically create and generate AutoCAD files.

Users should also be able to instruct the system to move, delete, or create new blocks without using AutoCAD. The system will generate the blocks and place them in the files accordingly.

Users should also be able to fully control the layers in drawing. Including defining, deleting and modifying existing layers.

All these files must be native .DWG files. Not DXF or any other variation such as XML or such.

Oh and BTW, this needs to be an enterprise wide app using Oracle in the background for proper data storage, supporting multiuser and multithereading.

Need I go on.

[Lee Smith]

I'm done

[e34m5]

Please tell me you aren't really that stupid. I guess that sound you heard was this topic going right over your head.

[tgilders]

Hi cxxxv,

As I'm sure you're aware, FileMaker has a plug-in architecture that allows you to extend FileMaker to implement whatever functionality you desire/require. From what I understand, it sounds like you have an enormous opportunity here to crack open your favorite C++ IDE and create the functionality that we've all desperately been needing within FileMaker.

I think you'll make quite a killing. Just think of how much revenue you'll make if you can sell just one license to each of the 10 million registered users of FileMaker Pro!

[mr_vodka]

How many applications do you know that can have that kind of DIRECT interaction with AutoCAD? Many of these applications still have to call an external routine written in some other scripting language. FileMaker wasnt built as or even claims to be a true scripting platform. Asking FM to create AutoCAD files is like trying to break a concrete wall with a ball point hammer... Or like trying to run an e-commerce site with Access.

Like I said earlier, different tools are better at different things.

Many growing companies change software every few years because of such factors as needs change, business rules change, personnel change, outgrowing software, and other factors.

FileMaker may have been a great solution for them at one point, but they may have outgrown it, much like other companies outgrow Access for a MS SQL Server with VB front end. With these larger applications though, development time will always go up as well as costs.

FileMaker on the other hand is perfectly happy in its own niche. Rapid development. I can have a report done in FileMaker in minutes pulling from my Oracle, MS SQL, and MySQL servers simultaneously, where it would take a whole lot longer in Crystal Reports. Again right tool for the right job.

[e34m5]

Hmm..I wrote an application that does all that for AutoCAD simply by taking advantage of the Autodesk API. Why is this so hard for you people to understand.

FM can't even take care of the most mundane Windows calls.

[mr_vodka]

Why is it so hard for you to understand that FileMaker may not be the right tool for your current needs? So far many people have given you a solution and still you seem to care more about the fact that you dont like the product more than trying to find a solution... The answer is easy... If it doesnt fit your needs then go do it in something else you know will achieve it or are more comfortable with.

Do you really think you are the only person ever to use an API? There are many active FM users that have strong backgrounds in other technologies as well...

I dont know what you want anymore. You asked a question and it was answered numerous time... At this point, I believe that you are just being argumentative for no reason.

[Ocean West]

cxxxv,

Many folks here, who've kindly been trying to assist you, have been working with FileMaker for a combined century (or more) of experience.

We apparently cannot solve your issues with regard why FileMaker is the way it is, and cannot change the nature of FileMaker in such a way it to make it fit the, solution, expectations and pressures you are facing.

FileMaker has its position in the marketplace and fills that nicely and adequately, and many of us here build end to end solutions that involve some clever outside of the box thinking, using third party tools to achieve goals, and design requirements, pushing the boundaries of what this tool can achieve.

You are welcome to attend Devcon where you may sit down one on one with the actual FileMaker core systems engineers and discuss your grievances directly.

FM Forums is not affiliated with FileMaker but has been faithfully serving the community for over a decade, and we take pride in the assisting willing individuals to broaden their education of FileMaker .

As founder and operator of FMForums its my rare an unfortunate duty to declare this thread has run its course. And request that you review our Terms of Service http://www.fmforums.com/forum/showrules.php?