5 Replies Latest reply on Mar 9, 2009 9:13 AM by comment_1

    Copy/Paste data from Excel to FileMaker and vice-versa

    acousticalconsultant

      Title

      Copy/Paste data from Excel to FileMaker and vice-versa

      Post

      Hi,

       

      I tried posting this same message a few hours ago, but it hasn't shown up in the message list, so I'll try again - I hope I'm not double-posting.

       

      Here goes:

      I would like to be able to select multiple cells within Excel, copy them to the clipboard, and paste them into a FileMaker record so that I can then, at a later time, select the data within FileMaker, copy to the clipboard and paste back into an Excel workbook.  I have had no luck trying to acheive this.  The closest I have come is by creating an Excel object inside the FileMaker record, but the user cannot access the object within the record to paste the data.

       

      Can anyone help with this?

      Thanks.

       

        • 1. Re: Copy/Paste data from Excel to FileMaker and vice-versa
          Sorbsbuster
            

          Hi, acousticalconsultant, and welcome to the forum,

           

           

          I must be missing something in your explanation; sorry. 

           

          - I have created an Excel workbook and inserted data in two columns:

          1    A

          2    B

          3    C

          4    D

           

          - I highlighted all 8 cells and 'copied'.

          - I 'Pasted' them into a Filemaker text field.

          - I went back into the FM field, selected all, and 'Copied'.

          - I created a new worksheet, and 'pasted'.

          - It pasted a copy, exactly the same as the original.

           

          I thought that was what you wanted, no?

           

          Alan.

          • 2. Re: Copy/Paste data from Excel to FileMaker and vice-versa
            acousticalconsultant
              

            Hi Sorbsbuster,

             

            Thanks.  That was simple enough.  I had tried this on previous versions of FM, and it didn't seem to work.  When the data was pasted back into Excel, it would all get pasted into one cell.  I see that the cells now become tab-separated when pasted into FM which solves the issue.  Now, to expand my request, once the data is pasted into one field, this data is no longer capable of being searched.  I.e.  in Excel I have:

             

            Headers:  X    Y    Z

            Data:      1    2    3

             

            Once I paste the data ("1, 2, 3") into my FM field, I can no longer do a search for a particular data range in "Y" since the data is stored in one field.  So then, would there be a way to either paste the data across multiple fields in one step, or to somehow search one value within the field containing several cells of information?

             

            I expect that this is not possible, and that the best way would be to view the FM data in "Form View", paste the necessary data back into Excel and then perform a search using filters and whatnot.

             

            I hope my question is clear enough to understand what I am after.

             

            Thanks.

            • 3. Re: Copy/Paste data from Excel to FileMaker and vice-versa
              comment_1
                

              You can copy an array of records and fields in Filemaker and paste it as rows and colums into Excel - but not the other way around (i.e. you cannot populate multiple records/fields by pasting).

               

              You could import the data from Excel instead. Or run a script that pastes the data into a global field, then parses it out to "real" records and fields. 

              • 4. Re: Copy/Paste data from Excel to FileMaker and vice-versa
                acousticalconsultant
                  

                I thought about importing/exporing using a script, but I wanted to avoid that rather cumbersome method.  Instead, I thought of a way to parse the data into fields.  Using calculation fields, after the data is pasted into one global field, the calculated fields automatically parse the data that it finds between the tabs.  I'm not sure if that was what you meant in your post, however using this method doesn't require scripts so it's quite simple and user-friendly.

                 

                • 5. Re: Copy/Paste data from Excel to FileMaker and vice-versa
                  comment_1
                     Well, you certainly could prepare a permanent "array" of records and fields for displaying the data in a global field. However, there are some limitations you need to take into account. For example, the data can only be viewed, but not modified. And there is a preset maximum number of records. I am not sure what exactly is the overall purpose here, so I can't say how these (and other) limitations will affect what you want to do.


                  BTW, I don't see anything cumbersome in importing, and I don't see a contradiction between using scripts and having a simple and user-friendly solution. On the contrary: scripts are there to make it simple and friendly for the user.