10 Replies Latest reply on Aug 26, 2016 7:21 AM by alangodfrey

    FMP-Advanced & Excel

    Dobieg5750

      So have been experimenting and researching the best way to link data from FMP to Excel...want to dump selected records into a spreadsheet prefer to dump table 1 into one tab, then table 2 into another but from what I understand you can only dump into a workbook once...i.e. one tab and it only creates a new workbook...again, from what I've read.

       

      So instead I began researching linking the FM Database to a spreadsheet...heck, in my Excel for Mac even see a link to import from Filemaker...so started to get excited. Of course when I try to import, Excel pops up and says I need FMP installed to do this. Well I have FMP 15 Advanced...but it just won't work.

       

      The goal is this...I need to create exhibits that manipulates a lot of data...and comparing a number of records against a target record (relation to average, rank amongst record set, etc) that is really easy to do in excel but seems terribly hard to do in the reports in FM. So I figured I'd dump the data I needed into pre-designed excel workbooks (like I used to do in Access) and the fields can populate themselves with the raw data I've dumped into hidden sheets.

       

      Any help or point in the right direction (linking FMP-A & Excel or better ways to dump into excel WITHOUT deleting existing spreadsheet data or just confidence that I can do some of this IN FM) would be great.

       

      (& sorry if anything is unclear).


      SG

        • 1. Re: FMP-Advanced & Excel
          beverly

          dump into hidden (or not) worksheet to "flow" into others is a good way. I have a client that does annual reports. The data gets dumped and the report printed, followed by the next dump/print, for about 100.

           

          There may be ways to "communicate" directly, but I'm not sure how. Filemaker used to be a 'source' selectable in Excel.

          • 2. Re: FMP-Advanced & Excel
            Dobieg5750

            Filemaker is definitely a source (both FM Server as well as FM) in Mac Excel. However, when I try to select the file it tells me I need to have FMPro installed on the computer I'm trying to do this with...which I do. I have FM Pro Advanced....so struggling on that end.

             

            Currently trying to dump data into a Text File and create a link in Excel to that file, and just refresh...lot's of experimenting to do tonight I think.

            • 3. Re: FMP-Advanced & Excel
              user19752

              I didn't do it on Mac, but you need to open your database and sharing with ODBC, then define DSN.

              • 4. Re: FMP-Advanced & Excel
                alangodfrey

                You shouldn't have to over-write your Excel file.  If you export using the 'Save as Excel...' function then it will indeed create a new file.  Though that wouldn't be the end of the world; as Beverly says you could use each new file as a data source for a nicely-formatted permanent Excel workbook.

                You can also set up FM as an ODBC source (as Dobieg5750 says), but I stopped that many versions ago as it was incredibly slow - maybe it's been fixed by version 15.  Also I vaguely remember they nobbled FM Pro some time ago so that you needed Server to have an ODBC source.  Maybe I'm wrong.

                 

                An easy way is to export the data as a merge file and import it into Excel by its 'Refresh' function.  You can automate a lot of that:

                - set up the Excel sheet to automatically refresh the data, no dialogue, on opening

                - script the finding and exporting from FM

                - add a script line at the end to open the Excel sheet

                - click the script; next thing you're looking at your nice new shiny Excel analysis

                • 5. Re: FMP-Advanced & Excel
                  Johan Hedman

                  What is it that you want to accomplish in Excel that you can not do in FileMaker. Better to stay inside FileMaker and have everything stored safely there

                  • 6. Re: FMP-Advanced & Excel
                    beverly

                    not necessarily, Johan! the "integration" of FileMaker with other solutions is one of the reasons I stick with it year after year. There are some things native to Excel that we don't have (easily) within FileMaker and if there's a way for the two to communicate, then do it!

                     

                    It's just been a while since I had to get very complex with FileMaker data -> Excel. I can export XML from FMP and create new workbooks with one or multiple worksheets. I cannot export images and get them into Excel with XML. I also cannot 'import' into particular worksheets, but I can create the temporary data as Excel that can then be imported into Excel. On Mac Excel there used to be a way to directly have FM as a "source" ( https://support.office.com/en-us/article/Import-data-from-FileMaker-Pro-dda0a7cc-1f8f-472a-b0ab-1c15a95e7f32 ). If ODBC can be used now (Mac? Win?) then that's great!!

                     

                     

                    There are several plugins that can work with Excel (specifics I have not!) - 'filemaker excel plugin' (if you need to know what I Googled).

                     

                     

                    beverly

                    1 of 1 people found this helpful
                    • 7. Re: FMP-Advanced & Excel
                      Johan Hedman

                      You are right Beverly! There is many things other software can do that FileMaker can not, but first of would always be for me to create as much functions as possible inside FileMaker. Then if I get stock I start looking at other software that might help get further to accomplish what my goal is.

                      • 8. Re: FMP-Advanced & Excel
                        alangodfrey

                        Absolutely - the combination of Filemaker and Excel is a really powerful tool - we often provide very comprehensive reporting solutions, then provide a set of Excel workbooks that can break the FM ceiling and seamlessly (pretty much) let the user have instant access to Excel analyses.  A whole new world for them at the click of an FM button.

                        If you script the data export as 'MergeForSheet1', 'MergeForSheet2', 'MergeForSheet3' etc that would be easy.  You can make the Excel workbook import the data appropriate to each sheet.  You can make it refresh all the sheets' data on opening, with no dialogue.  If you only want some sheets to refresh automatically, then why not exploit Excel's VBA (or even easier just record a macro - which can be made to 'run on open' if you want)?  You can take the user to a refreshed pivot table, a chart..., all those little steps are really easy and add together to be a dead-simple, super-efficient combination.

                         

                        And I'll use the excuse to bang on again: we embedded an Excel workbook into a container field in Filemaker and could interact with it so seamlessly that one could have been fooled into thinking that FM had suddenly grown immense charting and pivoting features.  The only flaw was that the control of the size of the Excel window in the container field was erratic, to say the least. But is was magical to see.  It was so good we phoned FM and asked them to explain how to fix that last one step (it was all so good we were clearly doing something stupid) and they... had absolutely no interest.  Really disappointing.  Maybe I'll try it again (6 or 7 versions later) and see what happens.

                        1 of 1 people found this helpful
                        • 9. Re: FMP-Advanced & Excel
                          Dobieg5750

                          Hey everyone...really great feedback and ideas. I'm going to incorporate much of what was discussed here.

                           

                          To answer one of the questions...the way we currently do some of our reports (pre-filemaker) is house data in Access (easy to load, manipulate and automate much of the data entry) and then data dump into excel.

                           

                          For example, we (in advance) load wage contract data into the database....salary per year, wage steps, longevity, hours of work, holiday pay, Insurance costs, etc. I have that already built (automation and everything) in my new FMP DB.

                           

                          When I need to access (pun) it, I grab the Target and assign Comparable contracts and dump the two sets of data into my pre-designed spreadsheets. When I say pre-designed, I mean 22 different sheets that are all set up and laced with formulas linked to two hidden sheets (Target & Comps). This creates 68 pages of related bargaining exhibits (across 22 sheets) that compare the Target against the Comp Set, Average of the Comp Set, Relation to that Avg (Targ to Avg) and Rank of Target Amongst Comp Set.

                           

                          We do this for every subject (Wages, Hours, etc). In all we do all the heavy lifting by creating these "templates" and then just dump the data upon request and save (either to excel so they can add in their own footnotes or contract proposals for either or both parties, or save it to a PDF for locked down exhibits).

                           

                          So in a nutshell, it is easier to dump data and manipulate with formulas and link everything together in one workbook....BUT the desire to have it be ALL in FM (as I tried in Access) is there (still need to figure out ways to enter in footnotes....perhaps some global fields with a data entry popup or something before I print) as the goal is to zip this application up into a single app.

                           

                          Thanks again everyone and sorry for the lengthly response...I may have an additional question as I work through this (spent the night last night creating a nice little solution for my RANK formula so there is hope!) and will reach out.

                           

                          SG

                          1 of 1 people found this helpful
                          • 10. Re: FMP-Advanced & Excel
                            alangodfrey

                            "Maybe I'll try it again (6 or 7 versions later) and see what happens" - no can do; they've removed OLE completely.

                            1 of 1 people found this helpful