2 Replies Latest reply on Jan 5, 2011 3:29 AM by Sorbsbuster

    Automatically Insert Excel Files into Container Fields

    kingsley47

      Title

      Automatically Insert Excel Files into Container Fields

      Post

      I want to be able to connect specific excel files to specific records based on a field in that record. I do not care if I am making a shortcut or actually opening an excel file within filemaker. However, I would prefer just a shortcut.

      My issue is that I cannot find a way to enter a field variable into the file name. It doesn't seem to work if I choose to insert file or insert object. Could someone please explain to me how I should go about doing this?

        • 1. Re: Automatically Insert Excel Files into Container Fields
          philmodjunk

          You'll need to compute the filepath, not just the filename and put it in your variable. The exact form that path takes varies depending on whether you are using a Mac or Windows computer. There are also get functions that can be used to help specify the filepath and these are intelligent enough to compute the correct path for either Operating System.

          Here's just one example for an excel file located in a folder, named "Excel Files" on your desk top:

          Set Variable [$FilePath ; value:  Get ( DesktopPath ) & "Excel Files/" & "YourfilenameHere.xls"]
          Insert File [YourTable::Container field ; $FilePath]  // and you can insert by reference or insert an actual copy of the file here

          To see examples of a number of different filepaths for both operating systems, look up "Creating File Paths" in FileMaker help.

          • 2. Re: Automatically Insert Excel Files into Container Fields
            Sorbsbuster

            kingsley47 - Please only 'Insert a reference only'; your file size will explode if you embed the Excel files in the container field.  And of course note that after you have linked the Excel shet you cannot move or re-name it, so plan your filing and naming system carefully.

            If you really do insert an Excel Object into the container field you could get very excited about the possibilities: double-click the field and an Excel sheet belonging to that record opens up immediately, ready to be edited.  Make the container field large enough on your layout to be readable, and you could bury an Excel sheet in it which displays charts of the related data in your database.  Use it to show a pivot-table of your information..., effectively have a whole list of Excel features seamlessly combined into FM.

            Well, almost.  It has been a hobby-horse of mine for years, since I stumbled upon ways to link Excel to FM to exploit its charting capabilities.  Unfortunately the explosion in file size was a show-stopper, then there was the problem of the Excel window (either displayed in the container field or double-clicked) sometimes opening at strange zoom levels.  What it appeared to be able to do was all so exciting I only gave up very reluctantly, and in spite of my extreme enthusiasm for what I had 'discovered' I got absolutely No Enthusiams back from FM to help me figure out the niggles.  You could try it for yourself, and join my campaign...