13 Replies Latest reply on Jul 8, 2016 2:03 PM by ClaudeChamberland

    Specifying excel worksheet when importing

    marcholt

      Title

      Specifying excel worksheet when importing

      Post

      Hi All, Is there a way to specify an excel worksheet when importing using script steps with no dialogue box shown and using a variable as a filename. See below screen shots:    

       

       

       

      Static Filepath

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Variable Filepath

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      When you choose a static file another window appears prompting you to choose a worksheet, this doesn’t appear when using variables. The excel file contains multiple worksheets and I need to be able to choose which one to use. 

       

      FileMaker Pro 9 Adv

      FileMaker Server 9

      Windows XP 

       

      Any idea’s?

       

      Thank you in advance!

        • 1. Re: Specifying excel worksheet when importing
          TSGal

          MarcHolt:

           

          Thank you for your post.

           

          At this time, a script will not permit the selection of a specific Worksheet via a variable.  I encourage you to enter this information into our Feature Suggestion web form at:

           

          http://www.filemaker.com/company/feature_request.html

           

          I could easily copy your request and paste it into the web form, but there are questions asked that only you can answer.  In addition, the people who monitor these requests are from Development and Product Management, and they like to see how customers are using our product.

           

          When entering the suggestion, please just don't say "Add this feature."  Instead, explain what you are trying to do, how implementing this will help your efficiency/business/etc.  In other words, real-life examples generally receive more attention than non-descriptive suggestions.

           

          Back to your original problem, one way of getting around this is to create separate script steps for each Worksheet, and depending on the variable, select the appropriate import script step. 

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Specifying excel worksheet when importing
            TomMcInggl
               How does one enter a Worksheet name into an Import Script statement in FM10Adv on OSX?  If I select No Dialog I cannot see the Worksheet names.  If I select Dialog - the worksheet name drop down appears when the script statement is run.
            • 3. Re: Specifying excel worksheet when importing
              philmodjunk
                

              In your script editor, select the import records step.

               

              At the bottom of the editor you'll see three script options check boxes.

              If you leave both "Perform without dialog" and "Specify data source" options cleared, the script will pause on this step and open up a dialog box for selecting the file from which to import data.

              • 4. Re: Specifying excel worksheet when importing
                TomMcInggl
                  

                I am able to select the file by checking both "Perform without dialog"  and  "Specify data source"  and putting the filename in a $scriptVariable that I edit into the script.  What I cannot enter during script editing is the name of a worksheet within the file.  In all cases, FM10Adv makes me select the worksheet name from a drop down list during script execution.

                 

                Tom 

                • 5. Re: Specifying excel worksheet when importing
                  FentonJones
                    

                  Another workaround approach, which I developed because the Excel worksheets sent to me had varying names for the 1st Worksheet, even though the columns were the same. Which was to use AppleScript to rename the 1st (or whichever) worksheet. 

                   

                  In this case the files were downloaded from a site which put up HTML files, and called them ".xls". Which works OK, if you're opening them with Excel; but certainly does not work if you try and open them with FileMaker. So I used AppleScript to tell Excel to open each, then save as a real Excel file. While I was at it, I renamed the worksheet.

                   

                  This is the AppleScript I used. It expects there to be ONLY Excel-openable files in the folder chosen. I could make it more robust in that sense; but it was only for me.

                   

                   

                  set ex_folder to (choose folder)

                   

                  tellapplication "Finder"

                  setex_filestoeveryfileofex_folderasalias list

                  endtell

                   

                  repeatwithiinex_files

                  setfile_pathtoiastext

                  ignoring application responses

                  -- ignore the "this is a web application" warning

                  tellapplication "Microsoft Excel"

                  try

                  openi

                  tellfirstworkbook

                  setwsto (a referencetofirstworksheet)

                  setnameofwsto "COMP_RESULTS"

                  save workbook as filename file_path file format Excel98to2004 file format with overwrite

                  endtell

                  close first workbook

                  onerrorerrmsgnumbererrNum

                  beep

                  display alert errNum message errmsg & return & "Could not continue" as warning buttons {"OK"} default button 1 giving up after 5

                  return

                  endtry

                  endtell

                  endignoring

                  endrepeat

                   

                  tell me to display dialog "Done" buttons {"OK"} default button 1 giving up after 10 

                  • 6. Re: Specifying excel worksheet when importing
                    FentonJones
                       P.S. I also already had Excel open. Since Excel must actually open the file, the screen flashes as each window draws (cascading) then closes. I don't mind; it takes less than a second to do each file. But possibly that could be suppressed, if you have a lot of files.
                    • 7. Re: Specifying excel worksheet when importing
                      TomMcInggl
                        

                      There will be between 1 and 16 worksheets in each of about 15 spreadsheets but the names of the worksheets are predefined and will be consistent. 

                       

                      The catch is that they are being used to create script commands to set permissions in a Windows Filing system and the programs will need to run on Windows as well as a Mac. 

                       

                      John Osbourne agreed that "It definitely would be a nice feature to completely automate the import of a spreadsheet with multiple worksheets.

                       

                      While waiting for response from FileMaker, I am going ahead with a manual approach.

                       

                       

                      • 8. Re: Specifying excel worksheet when importing
                        TSGal

                        TomMcInggl:

                         

                        Thank you for your posts, and I'm a little unsure when you say "While waiting for response from FileMaker, ..."   Are you waiting for another post from me?  Are you waiting for more information from the answer I originally provided?  Are you waiting for some kind of feedback from entering the suggestion?

                         

                        TSGal

                        FileMaker, Inc. 

                        • 9. Re: Specifying excel worksheet when importing
                          TomMcInggl
                            

                          I was expecting an indication from Filemaker that the feature will be implemented and it should be shipped by some date.  Given, the extensive use of Excel by people that don't think in terms of databases, good support for Excel Worksheets would help all Filemaker developers.

                           

                          Thanks for checking on my post.

                           

                          Tom

                           

                           

                          • 10. Re: Specifying excel worksheet when importing
                            TSGal

                            TomMcInggl:

                             

                            I know this sounds cold and heartless, but FileMaker, Inc. does not comment on future products.

                             

                            The best I can do is point you to where you will be heard (Feature Suggestion web form).  You'll have to wait and see if the suggestion is implemented.

                             

                            TSGal

                            FileMaker, Inc. 

                            • 11. Re: Specifying excel worksheet when importing
                              PreetHooda

                              I know above is almost year old post. But wondering, did this feature was ever implemented ?

                              Can we import excel file without prompting for worksheet dialog box ?

                              I am using fm adv 11.0v1. Prompt answer will be helpful. Thanks.

                              • 12. Re: Specifying excel worksheet when importing
                                ChrisBishop

                                Absolutely you can do this.

                                When creating the Import script step, use an actual document path to be able to choose the worksheet.  Then place the variable before that in the path list.  For example:

                                $path
                                /Macintosh HD/Users/cbishop/Desktop/link_to_example_file.xlsx


                                Chris Bishop
                                Software Developer
                                318, Inc.

                                • 13. Re: Specifying excel worksheet when importing
                                  ClaudeChamberland

                                  I know above is a few years old post. But wondering, did this feature was ever implemented ?

                                  Can we import excel file without prompting for worksheet dialog box ? Is it possible to include the worksheet in the variable for the path?

                                  I am using fm adv 15.  Thanks.