9 Replies Latest reply on Aug 22, 2013 2:38 PM by kiwikaty

    DDE save.as to Excel or another option?

    kiwikaty

      Hi, I am pretty desperate for some help in relation to an issue that is pushing me well over deadline. I am on windows 7 and am trying to get DDE to work and getting error 3. I am trying to use DDE to do a save.as for excel. I know DDE is old school but please read on...

       

      I have no idea if I am getting error 3 because I don't have the syntax right... so was wondering if anyone had an example of what you need to use where you want to use DDE from fm to excel to do a save.as? I have the file path as a variable.

       

      I am trying to do this crazy workaround as I need the excel file I have created via html to be saved as a real xls before fm emails it to the user. I need to change excel from defaulting it save type .html and instead make it save type .xls - so it is more than changing the name (which is already .xls) - I am really trying to change what filetype excel thinks the file is (which I think you can do if you get the DDE command right).

       

      I was meant to have this solution delivered last week and have tried dozens of different approaches to no success. I first tried adding code to the html to recognize that the output should be an xls but still get this message "The file you are trying to open,XXXX,is in different format than specified by the file extension" when the file is opened in xls - even if I bite the bullet and tell users to click yes to this message as they open it, I am still having the issue that when they make changes to the spreadsheet and save it... excel is saving it as .html and not .xls. I have to try and force the file to become a real xls before attaching to the email.

       

      I am generating the html from within fm (so I can format the excel spreadsheet and keep the formulas intact) then exporting the html chunk in a single field (and single record) as a tab separated file but getting filemaker to call it $filePath.xls - this works in the fact the spreadsheet looks as I want it with the embedded data and formulas in place and excel happily opens it thanks to the .xls extension - I am just stuck on tricking excel into re-saving it as a proper.xls.

       

      I don't know if DDE is the answer but will try anything as long as I can make it work without having to do anything to the 100+ client machines.

       

      Any advice appreciated.

      Kind regards

      Katy

        • 1. Re: DDE save.as to Excel or another option?
          wimdecorte

          DDE is not the answer, a simple VBscript that you can generate from FM and exeucte from FM is the way to go.  Just use the Excel ActiveX interface to open the html file and resave it as an XLS.

           

          If all your deployments are going to be on Windows 7 or higher you can consider using PowerShell instead of VBscript.

          • 2. Re: DDE save.as to Excel or another option?
            kiwikaty

            Hi,

             

            Thank you so much for your reply. Would I do that as a "send event" and if so (if you have time) would you be kind enough to copy a script step where you have done this do I could see an example of what the construction looks like.

             

            I was ready to admit defeat which is very very un-characteristic!

             

            If I can get it to work then you will be my hero.

             

            Kind Regards

             

            Katy

            • 3. Re: DDE save.as to Excel or another option?
              kiwikaty

              Sorry - yes all windows 7 and above and macs as well but I will worry about the mac users next. The person who needs to distribute over 300 customised spreadsheets to lecturing staff this week is on a PC. I had written them an export of the data but the formulas break and you get no formatting. Creating HTML as a single calc was the solution to this but then the way excel treated the file became a show stopper. If I can't get the vba/power shell scrip done today they are going to export the data and then format the headings and add the formulas to each one before manually attaching to an email. I am so close yet so far away!

               

              Thank you again for your help.

               

              Kind Regards

               

              Katy

              • 4. Re: DDE save.as to Excel or another option?
                wimdecorte

                I don't have time to put together a full working demo but there should be plenty of examples around here that I previously did.  I've shown this a number of FileMaker Devcons over the years.

                 

                The process is like this:

                 

                - you craft the VBscript and store it in a text field, using placeholders where necessary (such as the file name and file path)

                - use Set Field to populate a global text field with a copy of that VBscript and subsitute in the placeholders with the whatever needs to be there

                - you use Export Field Contents to export the VBscript from the global field (typically to the Temp folder that you can get with the Get() function)

                - you use Send Event to trigger the VBscript from the hard disk

                - (since you use the temp folder the VBscript gets deleted at the end of the script - you may need to put in a small pause here to make sure the script does not end before the VBscript is done)

                 

                A quick Google search should give you enough working examples of a VBscript that tells Excel to open a file and save it as something else.

                 

                HTH

                • 5. Re: DDE save.as to Excel or another option?
                  kiwikaty

                  You’re wonderful – I will let you know how I get on ☺. Many thanks.

                  • 6. Re: DDE save.as to Excel or another option?
                    kiwikaty

                    "A whole new world".... I have it working and the potential is huge! There were a few gotcha's to work though like setting a second set of file path variables so the slashes were in the direction VBA wanted them in but once I worked through them I was thrilled with the result. I will have some fun now doing a mac equivalent. I am happy to pdf the full script for anyone who want to see it - just send me a private message. It won't be fancy but definitely functional.

                     

                    I really can't thank you enough. I managed to get to 5 dev cons but then the global recession put international conferences well out of the budget... if I manage to get to one in the future I will be sure to thank you with a chocolate fish in person (although I am not sure choccy fish have the same significance in your part of the world )!

                     

                    Thank you so much again.

                    Katy

                    • 7. Re: DDE save.as to Excel or another option?
                      wimdecorte

                      You're welcome.  Using OS-level scripting like this is tremendously powerful and very rewarding.

                      • 8. Re: DDE save.as to Excel or another option?
                        beverly

                        Yep! and for 'another option', you can create EXCEL as TEXT (in an HTML table), save the extension as ".xls" or ".htm" and it should open just fine in EXCEL. I do this for many clients that need it. And there's always the Export as .csv (.mer has the field names as the first row, but is essentially the .csv format).

                         

                        Also you can create as XML (with the .xls extension) and this can have more advanced Excel such as styles and formulas. But you need some XSLT(s) to make this happen when you Export as XML.

                         

                        Just giving some options. These are all x-plat and don't require DDE.

                         

                        Beverly

                        • 9. Re: DDE save.as to Excel or another option?
                          kiwikaty

                          Hi Beverly

                           

                          Thank you for taking the time to reply. I am using the option of creating the spreadsheet as text and giving it the .xls extension which does open in excel but not without giving this messages on opening the file ""The file you are trying to open,XXXX,is in different format than specified by the file extension" and when they save this file it defaults to file type to html even though the file extension is .xls. I spent a lot of time googling and tried lots of things that would tell excel how to treat the file e.g.

                          <!-- Change the MIME TYPE -->

                          <meta Content-Type: application/msexcel>

                          <!-- Name the Sheet -->

                          <meta Content-Disposition: attachment; filename=NAMEOFWORKSHEET.xls>

                           

                          ...but the resulting messages in excel persisted.

                           

                          I did not want to have to send a whole lot of instructions with the files (like click yes to this message and when saving please change file type to...) as we are talking about over 200 lecturers who just want it to be as straight forward as possible.

                           

                          I did not know that doing it as xml you could include styling and formula's so will try to find some examples of this on the net, It will be interesting to see how excel behaves with a file generated this way. I wonder if it will still give the warning message and what file type it will default to on saving?

                           

                          One thing I have discovered is that Macs and Windows will treat the file differently, macs seem to feel less inclined to pop up lots of messages which in this case is a good thing - pity we have about a 50% distribution of each.

                           

                          In this day and age sending out spreadsheets and getting them sent back seems an inefficient way to collect the data given that we have solutions on site using cwp but that is what they wanted  .

                           

                          Thank you again for the reply. You feel pretty lucky to be given advice by senior developers who you know must be flat out themselves with their own work!