Exporting to an existing Excel spreadsheet.

[fmow]

I suppose this is something that is regularly done in situations where people expect an Excel output either for a printout or for any other data manipulation. I'm wondering if there is a online guide or a blog post just got one through the basics of doing this without FileMaker. I'm well versed in their exporting and importing, at least of the intermediate level of my understanding of FileMaker, so this is more about something more advanced to do.

[comment]

What exactly is supposed to exist in the existing spreadsheet, before you add the exported data into it?

[fmow]

well, the layout and any functions and macros one builds on top of the data which has been structured on an xl spreadsheet the way you like it. 

[comment]

Okay then. I don't know about "the basics of doing this without FileMaker". I suppose you can set up an import to Excel in Excel itself, then call this routine from Filemaker via OS-level scripting.

 

 

As for doing this with Filemaker, your options are pretty limited - esp. if the target spreadsheet is supposed to contain macros. It cannot be done using native Filemaker features alone; you will need some third-party assistance (either a plugin or OS-level script), at least for the final part.

 

The .xlsx file is actually a ZIP archive containing various XML documents. You can export the data from Filemaker as XML, using a custom XSLT stylesheet during the import to transform it to the required format. Then replace the XML document holding the data in the .xslx template with the just exported file. Alternatively, export as .xlsx, extract the data file and insert into the template. Either way, you will need to zip the result - and that's where you must turn to something outside Filemaker.

[bcooney]

Why couldn't you export the data to a text file and reference that file in Excel, having it Refresh on open? See Excel's Get External Data. http://office.microsoft.com/en-us/excel-help/connect-to-import-external-data-HP010089898.aspx

[fmow]

Great suggestions guys, much appreciated, I need to fresh up on my excel.

[jayivan]

May be a bit late of a reply, but I have to export data to Excel sheets provided by various distributors (which they use for importing into their systems), so I must use their Excel documents unmodified. 360Works' plugin Scribe has been a key tool in making this happen. I found it relatively straight forward and easy to script.

[Josh Ormond]

fmow...I'm not entirely clear what you are trying to do. However, if you are just trying to move data into a pre-existing xls...take a look at this video.

 

 

There is also a more expanded explanation on Skeleton Key's website ( and a Mac version of the process ). http://www.skeletonkey.com/filemaker_edi_excel/

 

The Excel file needs to be open as well when you do this. The idea is collect the data into a tab-delimited field, then use Excel's DDE functionality to "paste" the data to Excel. Excel will retain it's existing formatting and just accept the values you push over.

[Wim Decorte]

Ugh... DDE is very very dead and not supported by Microsoft anymore.  Hasn't been for a long time. Don't build business critical functionality on dead technology.

 

I've shown how to do this in devcons going back to about 2002.  The reason most FM developers shy away from the best ways to do this is that they are not comfortable with OS-level scripting technologies.  But it really is not that hard.

 

On Windows, VBscript and PowerShell are the way to go.  VBscript relies on ActiveX, the successor to DDE and also on the way out but likely to be around for the foreseeable future.

[Josh Ormond]

Well, I can't say I disagree about not building business critical functionality on dead technology. However, the OP referenced an intermediate level of understanding of FM. So I am not confident a full-on 'developer' approach is what fmow was looking for. But I'm always happy to be wrong...which happens often.  Besides, nothing business critical should be relying too much on Excel. I know, through experience, the problems and hassles it causes. Having worked for enough companies that ran most of the business off of .xls.

 

I, personally, used DDE to fill an immediate request and deadline, while I learned about other technology options. But for many users of FM, if they were that adept at OS level scripting, they aren't typically using FileMaker. That's nothing about FM, just a demographical thing. For those of us that use it's full power and push the envelope, it represents a set of tools we use to deliver a quality product. That just doesn't represent most of the FileMaker user base.

 

Ugh... DDE is very very dead and not supported by Microsoft anymore.  Hasn't been for a long time. Don't build business critical functionality on dead technology.

 

I've shown how to do this in devcons going back to about 2002.  The reason most FM developers shy away from the best ways to do this is that they are not comfortable with OS-level scripting technologies.  But it really is not that hard.

 

On Windows, VBscript and PowerShell are the way to go.  VBscript relies on ActiveX, the successor to DDE and also on the way out but likely to be around for the foreseeable future.

 

On a side note, have you run into a lot of security speed-bumps on users systems using VBScript and ActiveX? My own experience with VBScript has been somewhat limited to date. But in the few cases where I was working with it, OS security was happy to block anything ActiveX related.

[comment]

The Excel file needs to be open as well when you do this.

 

The point here (for me anyway) is that you need to have Excel - i.e MS Office - installed for this to work.

[Wim Decorte]

But for many users of FM, if they were that adept at OS level scripting, they aren't typically using FileMaker.

 

 

Could not agree less.   One has nothing to do with the other.  The same challenge would exist if you did this in Access, or FoxPro or 4D,...

The issue is more one of extending your reach.  If you need to integrate A with B always have two choices: stay in A or venture out in B.  If all you do is scroll through the FM script step list looking for what could talk to Excel, then yes, DDE Execute looks like the cat's meow.  But it isn't.  It's the pharaoh's cat's meow.  It's dead, embalmed and put in a museum.

 

Granted, on the Windows side there is not much else to go by in the Script steps list,  Send Event is the way to go but it is not obvious.  At least on the Mac side there an obvious "Perfrom AppleScript" script step.  If we had a "Perform Windows Script" equivalent at least it would prompt more curiosity and exploration.

 

Security issues: no.  Clearly it depends on what you want to do (like send email in a bulk automated fashion is a red flag obviously), but using VBscript or PowerShell actually overcomes the typical security warnings you'd get from trying to use macros in Excel or Word.

[Josh Ormond]

lol...that's an awesome explanation. I will bow to that.

 

The issue is more one of extending your reach.  If you need to integrate A with B always have two choices: stay in A or venture out in B.  If all you do is scroll through the FM script step list looking for what could talk to Excel, then yes, DDE Execute looks like the cat's meow.  But it isn't.  It's the pharaoh's cat's meow.  It's dead, embalmed and put in a museum.

 

VBScript is on my list of things to learn. PowerShell I hadn't really thought too much about, but I will now. At one point, I was working on creating my own XML spreadsheets. It has promise for the specific task I was working on for our office, but I see limited use outside of that. Appreciate the feedback Wim!!

 

comment - yeah, I agree. Excel is good for certain things...but the more you rely on it, the more it sucks you in...to the wrong approach on running a business. I know how to do a lot in Excel...but it is limited and not a db or dbms.

[comment]

comment - yeah, I agree. Excel is good for certain things...but the more you rely on it, the more it sucks you in...to the wrong approach on running a business. I know how to do a lot in Excel...but it is limited and not a db or dbms.

 

That's not my point. The question was how to export a specific Excel document (or to a specific Excel document). Ideally, we should not require any third-party application to do that. As I explained in post #4, it's not possible in this case. Still, we can divide the possible solutions into two categories: those that rely on additional software being installed on the implementing system (be it a plugin or MS Office) and those that can do the job with the software that is known to exist on any system.

[Josh Ormond]

Understood. I think there is still a large part of the FM user base that is in a situation like where I started with FM. Business rules and workflow change so frequently, that learning certain technologies to help do it the right way is just not practical. Nor will the business pay a professional developer to keep up with the constant changes, for whatever reason.

 

I had to learn how to generate an XML Spreadsheet for one specific task. There was a lot involved in the report. So I spent 3 months ( in the midst of the rest of my actual normal workload ) learning how the XML Spreadsheet worked. Completed the project, generated the first file for the weekly report, and the company completely changed the business rules for the product. And I haven't used the approach since, so I would have to relearn it to use it again. Now I'm much more careful about what I invest my time in. It usually ends up being...implement a short-term, albeit hackery, feature to accomplish the business requirement...then add it to the project list as a redeployed fully integrated feature using the best approach possible if the feature continues to be used.

 

But I do definitely agree that a self-contained solution to achieve the goal is preferable.

 

In this case, I think there is also the possibility of generating the Excel spreadsheet and keep the macro logic in a separate file