28 Replies Latest reply on Aug 23, 2013 3:21 AM by Trevorׅ

    Open With

    Trevorׅ Adobe Community Professional

      I am saving a txt file and I want it to open (execute) in excel after the script creates it.

       

      I probably can't do it in JS and need to do a little doScript in VB and AS

       

      Can anyone show me how to do this.

       

      Something like

       

      function openWith (myFile, myProgram) 
           {
                   if ($.os.match(/Mac/i)) app.doScript ("appleOpenWith (" + myFile + ", " +  myProgram + ")", ScriptLanguage.APPLESCRIPT_LANGUAGE);
                   else app.doScript ("windowsOpenWith (" + myFile + ", " +  myProgram + ")", ScriptLanguage.VISUALBASIC);
          }
      
      
      function appleOpenWith (myFile, myProgram)
           {
                \\ SOME APPLE SCRIPT HERE. WHAT????
           }
      
      function windowsOpenWith(myFile, myProgram)
           {
                \\ SOME VB HERE. WHAT????
           }
      

       

       

      Thanks

       

      Trevor

        • 1. Re: Open With
          Vamitul Level 4

          no need for VB or AS:

           

          http://jongware.mit.edu/idcs5/pc_File.html#execute

           

           

          kind of bad for security, i guess, but..

          • 2. Re: Open With
            TᴀW Adobe Community Professional & MVP

            You could just rename the extension to xls and do an execute() on it.

            Excel will take that in its stride and it should work, provided Excel is

            the default app for xls on your computer.

             

            Ariel

            1 person found this helpful
            • 3. Re: Open With
              Trevorׅ Adobe Community Professional

              Hi Ariel and Vlad,

               

              Thanks for you replies,

               

              The renaming helps that the "txt" file opens with excel the problem with excel gives a warning that the file is corupt and do you want to open it.

              A bit b'deved as we would say but It is however quite a big help.

              It just would be a lot nicer to use the VB and AS and not get the warning.

               

              Trevor

              • 4. Re: Open With
                TᴀW Adobe Community Professional & MVP

                Well, there's probably better ways of doing it, but if you want to stay

                with JScript (Windows only), try saving this little snippet to your

                desktop with the extension .js.

                 

                If you double click on it, it will run. So you could execute() it with

                ExtendScript, and this gives you access to the Excel DOM:

                 

                ExcelApp = new ActiveXObject("Excel.Application");

                ExcelSheet = new ActiveXObject("Excel.Sheet");

                  // Make Excel visible through the Application object.

                ExcelSheet.Application.Visible = true;

                // Place some text in the first cell of the sheet.

                ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1";

                // Save the sheet.

                ExcelSheet.SaveAs("C:
                TEST.XLS");

                // Close Excel with the Quit method on the Application object.

                ExcelSheet.Application.Quit();

                 

                Below is the full documentation from Microsoft's JScript help file:

                 

                ActiveXObject

                 

                Enables and returns a reference to an Automation object.

                 

                 

                newObj = new ActiveXObject(servername.typename[, location])

                 

                 

                Arguments

                newObj

                Required. The variable name to which the ActiveXObject is assigned.

                 

                servername

                Required. The name of the application providing the object.

                 

                typename

                Required. The type or class of the object to create.

                 

                location

                Optional. The name of the network server where the object is to be created.

                 

                Remarks

                Automation servers provide at least one type of object. For example, a

                word-processing application may provide an application object, a

                document object, and a toolbar object.

                 

                To create an Automation object, assign the new ActiveXObject to an

                object variable:

                 

                var ExcelSheet;

                ExcelApp = new ActiveXObject("Excel.Application");

                ExcelSheet = new ActiveXObject("Excel.Sheet");

                 

                 

                This code starts the application creating the object (in this case, a

                Microsoft Excel worksheet). Once an object is created, you refer to it

                in code using the object variable you defined. In the following example,

                you access properties and methods of the new object using the object

                variable ExcelSheet and other Excel objects, including the Application

                object and the ActiveSheet.Cells collection.

                 

                // Make Excel visible through the Application object.

                ExcelSheet.Application.Visible = true;

                // Place some text in the first cell of the sheet.

                ExcelSheet.ActiveSheet.Cells(1,1).Value = "This is column A, row 1";

                // Save the sheet.

                ExcelSheet.SaveAs("C:
                TEST.XLS");

                // Close Excel with the Quit method on the Application object.

                ExcelSheet.Application.Quit();

                 

                 

                Creating an object on a remote server can only be accomplished when

                Internet security is turned off. You can create an object on a remote

                networked computer by passing the name of the computer to the servername

                argument of ActiveXObject. That name is the same as the machine name

                portion of a share name. For a network share named "
                myserver\public",

                the servername is "myserver". In addition, you can specify servername

                using DNS format or an IP address.

                 

                The following code returns the version number of an instance of Excel

                running on a remote network computer named "myserver":

                 

                function GetAppVersion() {

                    var XLApp = new ActiveXObject("Excel.Application", "MyServer");

                    return(XLApp.Version);

                }

                 

                 

                An error occurs if the specified remote server does not exist or cannot

                be found.

                 

                 

                Ariel

                1 person found this helpful
                • 5. Re: Open With
                  Trevorׅ Adobe Community Professional

                  Ariel

                   

                  If they had a "very very helpful" link I would have clicked it.

                  I shall look over it carefully over the next couple of days.

                  It looks like it solves 1/2 the "real" problem.  That is not how to open a tab delimited text file with excel but how to create an excel sheet.  That is really great.

                   

                  The other half is how to do it on a Mac.

                   

                  Hope to get some more feedback.

                   

                  Trevor

                  • 7. Re: Open With
                    Laubender Adobe Community Professional & MVP

                    @Trevor – The Excel2004 AppleScript reference is ok. I recently used it myself for a small script. But I think it's nearly no good for Excel 2011 or higher. Too much things have changed since.

                     

                    The second link is much more promising…

                     

                    Needless to say, but without a Mac it's impossible to debug any AppleScripts…
                    I cannot help you much, I hardly have any experience with AppleScript.

                     

                    Uwe

                    • 8. Re: Open With
                      -hans- Level 4

                      well, i can only test with excel 2004 at the moment, but as the only thing you want to do is opening that file it should be the same.

                      Tested a tabdelimitted-textfile.

                       

                      tell application "Microsoft Excel"

                      open alias "some:mac:path"

                      end tell

                       

                      I own Excell 2011 mac and may test any code ... if you want

                       

                      Hans

                      1 person found this helpful
                      • 9. Re: Open With
                        Trevorׅ Adobe Community Professional

                        Thanks Uwe and Hans for the feadback,

                         

                        Hans you seemed to have answered the apple side of my original question although Ariel has defiantly whetted my appetite to create a real excel file rather than a txt file which has to opened and then imported into excel.

                         

                        I hope to send you in about 5 hours time 2 very simple scripts to test (will be a big help)

                         

                        1) using "open text file filename "DATA.TXT" data type delimited tab true"

                        2) The same as 1 but get excel to save the opened txt files as an excel file.

                         

                        After those very simple ones I would like to try create a very simple non formatted speadsheet  from scratch.

                         

                        Thanks again, will be in touch,

                         

                        Trevor

                        • 10. Re: Open With
                          Trevorׅ Adobe Community Professional

                          OK Here's for some homework

                           

                          First script creates the whole file in Excel then opens the save as dialog.

                           

                          // make xls file on mac
                          // http://forums.adobe.com/message/5610204#5610204
                          // http://www.mactech.com/articles/mactech/Vol.23/23.02/2302AppleScript/index.html was a big help
                          // http://macscripter.net/viewtopic.php?id=39768 also helped
                          if (($.os.match(/Mac/i))
                              {
                                  var rangeContents = '{{"My Text File opened in excel"},{"This is cell A:2", "This is cell B:2", "This is cell C:2"}, {"This is cell A:3", "This is cell B:3", "This is cell C:3"}, {"Thanks a million""}}';
                                  var myAppleScript2 = 'tell application "Microsoft Excel"\r'
                                                       + 'set theWorkbook to make new workbook\r'
                                                       + 'tell active sheet of theWorkbook\r'
                                                       + 'set value of range "A1:C4" to ' + rangeContents + '\r'
                                                       + 'autofit columns\r'
                                                       + 'end tell\r' 
                                                       + 'show (get dialog dialog save as)\r'
                                                       + 'end tell\r' ;
                                  app.doScript (myAppleScript2, ScriptLanguage.APPLESCRIPT_LANGUAGE);
                              }
                          

                           

                           

                          Second script Opens Text file with Excel then saves it as an excel file. @Ariel might get to see if my mac saveas function works.

                           

                          // For Mac Open txt File with Excel and Save as xls
                          // http://forums.adobe.com/message/5610204#5610204
                          // http://www.mactech.com/articles/mactech/Vol.23/23.02/2302AppleScript/index.html was a big help
                          txtFileContents = "My Text File opened in excel\nThis is cell A:2\tThis is cell B:2\tThis is cell C:2\nThis is cell A:3\tThis is cell B:3\tThis is cell C:3\nThanks a million\n";
                          
                          
                          if ((textFile = saveFile (txtFileContents)) && ($.os.match(/Mac/i)) && (confirm ("Do you wish to open the text file in Microsoft Excel? ", false)))
                              {
                                  var macFileName = textFile.fsName.replace(/\\/g, ":").replace(/::/, ":"); // highly doubt the replaces are neaded Please can you test,
                                  var macXlsFileName = macFileName.replace(/txt$/i, "xls"); 
                                  var myAppleScript1 = 'tell application "Microsoft Excel"\r'
                                                       + 'open text file filename "' + macFileName + '" data type delimited tab true\r'
                                                       + 'tell active sheet\r'
                                                       + 'autofit columns\r'
                                                       + 'end tell\r' 
                                                       + 'tell active workbook\r'
                                                       + 'save workbook as  filename "' + macXlsFileName + '"file format Excel98to2004 file format\r'
                                                       + 'end tell\r'
                                                       + 'end tell\r' ;
                                   app.doScript (myAppleScript1, ScriptLanguage.APPLESCRIPT_LANGUAGE);
                              }
                          
                          
                          function SaveDialog (myFilePromt /* The file promt" */ 
                                                          ,Preset /* The desired suffix */
                                                          , warning /* OPTIONAL if true warn on overwrite, defalt is FALSE */)
                              {
                          
                                  var   suffix = Preset.match(/[^.]*$/),
                                          regSuffix = new RegExp("\\." + suffix + "$", "i"),
                                          myFile = ($.os.match(/Mac/i))  ?  File.openDialog (myFilePromt, function (f) { return (f instanceof Folder) || f.name.match(regSuffix);}) // if Mac
                                                        : File.saveDialog (myFilePromt, [Preset]); // else
                                  if (myFile)
                                      {
                                          var myFileName = myFile.name;
                                          if (! regSuffix.test(myFileName)) myFile = File (myFile.path + "/" + myFileName += "." + suffix);
                                          if (myFile.exists)
                                              {
                                                  if ($.os.match(/Mac/i) && myFile.readonly) 
                                                      {
                                                          alert (myFile.name + "\rThis file is set to read-only.\rTry again with a different file name."); SaveDialog (myFilePromt, Preset, warning);
                                                      };
                                                  else if (warning) if (!confirm ("The file " + File.decode(myFile.name) + "\nexists, do want to overwrite it? ", false, "Comfirm Save")) SaveDialog (myFilePromt, Preset, warning);
                                               }
                                          return myFile;
                                      }
                                }
                          
                          function saveFile (fileContents)
                              {
                                  var myNewFile =  SaveDialog ("Please enter then name of text file to save." // The file save prompt --- Change as desired
                                                                         ,"Text:*.txt" // The filter --- Change as desired
                                                                         , true) // OPTIONAL if true give warn on overwrite, default is FALSE 
                          
                                  if (myNewFile) // save file if cancel was not pressed
                                      {
                                          var mod;
                                          mod = (myNewFile.exists) ? myNewFile.modified.toString() : 0;                
                                          myNewFile.open('w');
                                          myNewFile.write(fileContents);
                                          myNewFile.close();
                                          if (myNewFile.modified.toString() == mod)
                                              {
                                                  alert((File.decode(myNewFile)) + "\nWAS NOT SAVED\n(Check if the file is readonly or open)");
                                                  saveFile ();
                                              }   
                                          return myNewFile // file saved
                                      }
                                  return false // file not saved
                              }
                          
                          
                          
                          

                           

                           

                          Scripts are completly untested

                          Chance of them working:- Not so great

                           

                          Script 3:

                          Sorry about this one completly off the topic, not a mac specific script and nothing to do with excel or the original question

                          BUT if someone could test it on a NON-ENGLISH computer it would be a very big help to me.

                          The script retrieves the date from an Internet site.

                          The Results on the js console by me look like this

                          ***************************************
                          Start Time:     1377020918626
                          Local Time:     Tue Aug 20 2013 20:48:38 GMT+0300
                          Server Time:     Tue, 20 Aug 2013 17:48:38 GMT
                          End Time:     1377020918627
                          Total Time:     0.001 seconds
                          ***************************************
                          

                          What I need to know is how the dates are displayed on the non English systems

                          Here's the Link to that script. (The Link will only be valid for a day or 2)

                           

                          I really appreciate the help

                          If the mac scripts work I shall try working on Windows wich I can test myself.

                          Thanks again

                           

                          Trevor

                          • 11. Re: Open With
                            -hans- Level 4

                            Sorry I'm in hurry and take a closer look this evening.

                             

                            first script:

                            those changes, just misstypes:

                             

                            if (($.os.match(/Mac/i)))
                                {
                                    var rangeContents = '{{"My Text File opened in excel"},{"This is cell A:2", "This is cell B:2", "This is cell C:2"}, {"This is cell A:3", "This is cell B:3", "This is cell C:3"}, {"Thanks a million"}}';
                                  

                             

                            second script:

                             

                            the filerefrence has got to be changed a bit:

                             

                            + 'open text file POSIX file filename  "' + macFileName + '" as text data type delimited tab true\r'

                             

                             

                            had a saving crash in excel then, but haven't got the time now ...

                            • 12. Re: Open With
                              Laubender Adobe Community Professional & MVP

                              @Trevor – same with me: cannot test before this evening (it's around 9 in the morning in my time zone here).

                               

                              Uwe

                              • 13. Re: Open With
                                Trevorׅ Adobe Community Professional

                                Hi Again

                                 

                                @Vamitul

                                If you have a chance this morning to try out the 3rd script (The windows one) it would be a big help.

                                 

                                @Hans & Uwe

                                Thanks for taking a look into things and I'll wait in suspense till the evening to here the full results.

                                From what I gather script 1 which is the really important one sounds hopeful

                                I'm an hour ahead of you guys (one hour later) so if you look at the time I posted that script and then I hope you can forgive me for the typos.

                                 

                                1) I forgot to say in the post above, please can you tell me on which version of excel you tested the scripts.

                                The line

                                + 'save workbook as  filename "' + macXlsFileName + '"file format Excel98to2004 file format\r'

                                Looks to me that it's not going to run on excel 2004 as I highly doubt Excel98to2004 is a valid format on excel 2004! any ideas of a format that works for 2004 and 2010?  If I leave out the file format part then I think it's just going to be saved as a txt file.

                                The saving crash was probably caused by this problem, Sorry about that.

                                 

                                 

                                @Ariel

                                Regarding Windows No Luck here

                                 

                                I seem to have a catch 22 situation.

                                If I make a js snippet  and then do execute on the js file it replies on the fact that the default js program handler is the Windows based script host.

                                This is not always the case.  By me the default handler is the toolkit this means that I need to find a way from the toolkit to execute the js file from the windows script host! oh dear.

                                 

                                Secondly The file created is not a real xls file it a txt file with a xls extension in other word does exactly the same as using js from the toolkit and saving it with an xls extension which works but give the warning "The file you are trying to open, TEST2.XLS, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

                                Doesn't look too professional However as I said above it is still a big help to just change the file extension to xls even with the nasty message.  Just would be very nice to get around this problem.

                                 

                                Trevor

                                • 14. Re: Open With
                                  Vamitul Level 4

                                  trevor, sorry, but my machines, both at work and at home, are set on english locale. running it gives me:

                                   

                                  ***************************************

                                  Start Time:           1377069718746

                                  Local Time:           Wed Aug 21 2013 10:21:58 GMT+0300

                                  Server Time:           Wed, 21 Aug 2013 07:21:57 GMT

                                  End Time:           1377069718746

                                  Total Time:           0 seconds

                                  • 15. Re: Open With
                                    Trevorׅ Adobe Community Professional

                                    Thanks for that

                                    It give me the Idea of changing my Locale to non-English

                                    • 16. Re: Open With
                                      TᴀW Adobe Community Professional & MVP

                                      Hi Trevor,

                                       

                                      Is this script for private or public consumption? Meaning, can we rely

                                      on the way things are set up on your computer, or are you targetting

                                      unknown systems?

                                       

                                      I suppose the answer's the latter, based on your various questions.

                                       

                                      My system's set up in English.

                                       

                                      The first thing that springs to mind is to launch the script with

                                      Windows Script Host and then call ExtendScript, rather than the other

                                      way round, since WSH has more possibilities in terms of Windows-specific

                                      features (ExtendXObjects, etc.)

                                       

                                      Another idea is that maybe it's possible to launch your xls script with

                                      some command-line switches to prevent it from throwing those warning

                                      dialogs.

                                       

                                      Ariel

                                      • 17. Re: Open With
                                        Trevorׅ Adobe Community Professional

                                        I change my locale to German and restarted the computer and I still got the same results.

                                        But I am wondering if the results would be different on a non English version of ID on a non English Locale system?

                                        • 18. Re: Open With
                                          -hans- Level 4

                                          Second script, mac osx 10.4.11 (still alive ;-)), excel 2004

                                           

                                          whole script works here with  few changes:

                                           

                                             var myAppleScript1 = 'tell application "Microsoft Excel"\r'

                                                                       + 'open text file filename (POSIX file "' + macFileName + '" as text) data type delimited tab true\r'

                                                                       + 'tell active sheet\r'

                                                                       + 'autofit columns\r'

                                                                       + 'end tell\r'

                                                                       + 'tell active workbook\r'

                                                                       + 'save workbook as  filename (POSIX file "' + macXlsFileName + '" as text) file format Excel 9795 file format\r'

                                                                       + 'end tell\r'

                                                                       + 'end tell\r' ;

                                           

                                           

                                          Part of the library excel 2004 for "save workbook as"

                                           

                                          save workbook as workbook

                                          [filename Unicode text] : A string that indicates the name of the file to be saved. You can include a full path. If you don't, Microsoft Excel saves the file in the current folder.

                                          [file format add in file format/CSV file format/CSV Mac file format/CSV MSDos file format/CSV Windows file format/DBF2 file format/DBF3 file format/DBF4 file format/DIF file format/Excel2 file format/Excel 2 east asian file format/Excel3 file format/Excel4 file format/Excel5 file format/Excel7 file format/Excel 9795 file format/Excel 4 workbook file format/international add in file format/international macro file format/workbook normal file format/SYLK file format/template file format/current platform text file format/text Mac file format/text MSDos file format/text printer file format/text windows file format/WJ2WD1 file format/Works1 file format/Works 1 all file format/Works 1 fmt file format/Works3 file format/Works4 file format/Works 3 fm 3 file format/works file format/works 2 east asian file format/WQ1 file format/WJ3 file format/WJ3FJ3 file format/HTML file format/XML spreadsheet file format] : Specifies the file format to use when you save the file.

                                          [password Unicode text] : A case-sensitive string, no more than 15 characters, that indicates the protection password to be given to the file.

                                          [write reservation password Unicode text] : A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only.

                                          [read only recommended boolean] : Set to true to display a message when the file is opened, recommending that the file be opened as read-only.

                                          [create backup boolean] : Set to true to create a backup file.

                                          [access mode exclusive/no change/shared] : Sepcifies the access mode for the new file.

                                          [conflict resolution local session changes/other session changes/user resolution] : Specifies who conflict resolutions will be handled.

                                          [add to most recently used list boolean] : Set to true to add this workbook to the list of recently used files. The default value is false.

                                           

                                          I'll give it a test on lion / excel 2011 in the evening.

                                           

                                          One last tipp: applescript-forum on hilfdirselbst.ch. English won't be a problem. It's supervised by Hans Haesler, AppleScript & ExtendScript guy

                                          1 person found this helpful
                                          • 19. Re: Open With
                                            Trevorׅ Adobe Community Professional

                                            Hi Ariel

                                             

                                            I missed your reply earlier and then I was offline for most of the day.

                                             

                                            I need these methods for both private and public use.

                                            I have for now Windows and for me the .xls method is fine and the warnings don't bother me.

                                             

                                            I write most of my pro scripts for the Mac and it's the mac scripts that the most important to me however I really would like to know how to do it for Windows. At least I can try things out for my self on that.

                                             

                                            Running the script from the script host and getting it to call the toolkit is not an option as the clients need to run them from InDesign or Illustrator.

                                             

                                            I think I just need to try and play around (or beg in other places) with the VB to create the Excel file correctly I'm sure it can be done, I'm sure there must be some VB I can run with a doScript that would execute the JS script the correct way.  The JS script could be created and saved in the temp folder.

                                             

                                            Hi Hans,

                                             

                                            Very encouraging feed back, and thanks for the fixes.

                                            Keen to know how the do on the new system.

                                             

                                            Trevor

                                            • 20. Re: Open With
                                              -hans- Level 4

                                              Good evening ,

                                               

                                              lion | excel 2011

                                               

                                              excel freezes with autofit-command. without it'll runs with above changes for the file reference.

                                               

                                              Here the save options for workbooks in excell 2011:

                                               

                                              save workbook as v : Saves changes into a different file.

                                              save workbook as workbook

                                              [filename text] : A string that indicates the name of the file to be saved. You can include a full path. If you don't, Microsoft Excel saves the file in the current folder.

                                              [file format CSV file format/CSV Mac file format/CSV MSDos file format/CSV Windows file format/DBF3 file format/DBF4 file format/DIF file format/Excel2 file format/Excel 2 east asian file format/Excel3 file format/Excel4 file format/Excel5 file format/Excel7 file format/Excel 4 workbook file format/international add in file format/international macro file format/workbook normal file format/SYLK file format/current platform text file format/text Mac file format/text MSDos file format/text printer file format/text windows file format/HTML file format/XML spreadsheet file format/PDF file format/Excel binary file format/Excel XML file format/macro enabled XML file format/macro enabled template file format/template file format/add in file format/Excel98to2004 file format/Excel98to2004 template file format/Excel98to2004 add in file format] : Specifies the file format to use when you save the file.

                                              [password text] : A case-sensitive string, no more than 15 characters, that indicates the protection password to be given to the file.

                                              [write reservation password text] : A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only.

                                              [read only recommended boolean] : Set to true to display a message when the file is opened, recommending that the file be opened as read-only.

                                              [create backup boolean] : Set to true to create a backup file.

                                              [access mode exclusive/no change/shared] : Specifies the access mode for the new file.

                                              [conflict resolution local session changes/other session changes/user resolution] : Specifies who conflict resolutions will be handled.

                                              [add to most recently used list boolean] : Set to true to add this workbook to the list of recently used files. The default value is false.

                                              [overwrite boolean] : Set to true to automatically overwrite an existing file.

                                               

                                               

                                               

                                              _____

                                               

                                              You may at least add a overwrite true at the end of the save command

                                               

                                              Cheers

                                               

                                              Hans

                                              • 21. Re: Open With
                                                Trevorׅ Adobe Community Professional

                                                A good evening / morning to you too.

                                                 

                                                Thanks for that test very crucial feed back.

                                                 

                                                The auto format is a nicety but can manage without it.

                                                 

                                                I am confused about the file format in the 2011 reference it does not seem to support the Excel 9795 file format but from what I gather from you the following line is good for 2011

                                                 

                                                + 'save workbook as  filename (POSIX file "' + macXlsFileName + '" as text) file format Excel 9795 file format\r'

                                                 

                                                Either way his is not a big deal as I can Just call up the saveas dialog as in script 1.

                                                 

                                                In summary the winning script is script 1 with the typo corrections and removing the autofit line.

                                                 

                                                Thanks again for all your help

                                                 

                                                Regards to all,

                                                 

                                                Trevor.

                                                 

                                                P.s. If you get a chance to run the 3rd little date script that Vamitul tested for me (connection needed) I would appreciate that.

                                                • 22. Re: Open With
                                                  -hans- Level 4

                                                  a, sorry I used format 'Excel7 file format' which seems to be common

                                                   

                                                  Script 3, German

                                                   

                                                  ***************************************

                                                  Start Time:     1377149578529

                                                  Local Time:     Thu Aug 22 2013 07:32:58 GMT+0200

                                                  Server Time:     Thu, 22 Aug 2013 05:32:57 GMT

                                                  End Time:     1377149578529

                                                  Total Time:     0 seconds

                                                  ***************************************

                                                  • 23. Re: Open With
                                                    CarlosCanto Adobe Community Professional & MVP

                                                    here's a sample of how to create an excel file using jsx/vbs

                                                     

                                                    // creates and runs a vbs file that creates an excel file
                                                    // carlos canto
                                                    // http://forums.adobe.com/message/5615242?tstart=0#5615242
                                                    
                                                    
                                                    var vbscript = [
                                                    'Err.Clear',
                                                    'On Error Resume Next \r',
                                                    'set app = GetObject(,"Excel.Application") \r',
                                                    'If (Err.number <> 0) Then',
                                                    '   Set app = CreateObject("Excel.Application")',
                                                    'End If \r',
                                                    'app.visible = true',
                                                    'set newDoc = app.Workbooks.Add',
                                                    'app.Range("A1:C1") = Array("1", "2", 3)',
                                                    'app.Dialogs(5).Show \r',
                                                    'set newDoc = nothing',
                                                    'set app = nothing'
                                                    ];
                                                    
                                                    
                                                    var vbfile = File(Folder.desktop +"/createXLSfile.vbs");
                                                    vbfile.open('w');
                                                    vbfile.write(vbscript.join('\r'));
                                                    vbfile.close();
                                                    vbfile.execute();
                                                    
                                                    • 24. Re: Open With
                                                      Trevorׅ Adobe Community Professional

                                                      Wow Carlos that actually worked!

                                                       

                                                      Together with the applescript solves the problem of how to make real excel file using jsx.

                                                      I am marking the answer as correct as it finishes of the real problem of creating the excel.

                                                       

                                                      Hans also totally deserves to be marked as correct but unfortunately there's only one to give out.

                                                      Thanks to Hans also for testing the 3rd (of the topic script) and a special thanks to Ariel for directing this thread on the correct course.

                                                       

                                                      Just 2 more small points very not crucial issues.

                                                       

                                                      1) Mainly for the sake of answering the original question but also would be nice to know.

                                                      How does one use the vb to open a txt file with excel.

                                                       

                                                      2) To add a second row I could obviously just add the line

                                                      'app.Range("A2:C2") = Array("4", "5", 6)',

                                                      Is there a way of of doing this in one line.

                                                      Something like app.Range("A1:C2") = Array(Array("1", "2", 3),Array(4,5,6)), Which does not work.

                                                       

                                                      Thanks again to every body

                                                       

                                                      Trevor

                                                      • 25. Re: Open With
                                                        CarlosCanto Adobe Community Professional & MVP

                                                        Hi Trevor, thanks, I think vbscript does not support initializing multidimensional arrays, they must be explicitly populated...I think

                                                         

                                                         

                                                        this script opens a text file

                                                         

                                                        // creates and runs a vbs file that opens a TAB delimited TXT file
                                                        // carlos canto
                                                        // http://forums.adobe.com/message/5615242?tstart=0#5615242
                                                        
                                                        
                                                        var vbscript = [
                                                        'Err.Clear',
                                                        'On Error Resume Next \r',
                                                        'set app = GetObject(,"Excel.Application") \r',
                                                        'If (Err.number <> 0) Then',
                                                        '   Set app = CreateObject("Excel.Application")',
                                                        'End If \r',
                                                        'app.visible = true',
                                                        'app.Workbooks.OpenText ("C:\\Users\\carlos\\Desktop\\dataTabDelimited.txt")\r',
                                                        'set app = nothing'
                                                        ];
                                                        
                                                        
                                                        var vbfile = File(Folder.desktop +"/textFileOpenWithExcel.vbs");
                                                        vbfile.open('w');
                                                        vbfile.write(vbscript.join('\r'));
                                                        vbfile.close();
                                                        vbfile.execute();
                                                        
                                                        • 26. Re: Open With
                                                          Trevorׅ Adobe Community Professional

                                                          Hi Carlos,

                                                           

                                                          Thanks again that finshes of the answers up the original question.

                                                          One last (I HOPE) question.

                                                          What would I change in this line

                                                          'app.Workbooks.OpenText ("C:\\Users\\carlos\\Desktop\\dataTabDelimited.txt")\r',

                                                          to get excel to open an html file?

                                                           

                                                          The same question over to Hans in applescript

                                                          What would I change in this line

                                                          + 'open text file filename (POSIX file "' + macFileName + '" as text) data type delimited tab true\r'
                                                          to get excel to open an html file?
                                                          

                                                          I would guess it would be

                                                          + 'open HTML file filename (POSIX file "' + macFileName + '" as HTML)\r'
                                                          

                                                          Is that correct?

                                                           

                                                          Last night I put together a script (for illustrator) that exports info about embed links to an excel sheet using the apple and VB scripts from here, seems to work well.

                                                           

                                                          I can't publish it here (copyright) but I shall send in a couple of hours the 5 of you private mails with a link to the script for you to use if it is of any use for you.

                                                           

                                                          Thanks again

                                                           

                                                          Trevor

                                                          • 27. Re: Open With
                                                            -hans- Level 4

                                                            Good morning,

                                                             

                                                            as you can read (;-)) in the AS-Excel-Reference you've posted (4th post)  there's no open command for html-files.

                                                            Commands offered: open; open FileMaker file; open links; open text file; open workbook || (excel 2004)

                                                             

                                                            So I guess a html-file has to be parsed to a delimited text file first ...

                                                             

                                                            Hans

                                                            • 28. Re: Open With
                                                              Trevorׅ Adobe Community Professional

                                                              Hi Hans and Carlos

                                                               

                                                              I see that using Carlos's script in 25 above works for html files as well

                                                              One just needs to change the file suffix from .txt to.html and get rid of the as text... and excel opens the html file without problem.

                                                              'app.Workbooks.OpenText ("C:\\Users\\Trevor\\Desktop\\myHTML.html")\r',

                                                               

                                                               

                                                              So I guess in apple script one this should work

                                                               

                                                              + 'open text file filename (POSIX file "' + macFileName + '")\r'

                                                               

                                                              Parsing to text delimited is not an option because in this case I loose the  images stored in the indesign table which is exported as an html file.

                                                               

                                                              I see I wont be able to send the link to the script I made until Sunday.

                                                               

                                                              Have a good weekend

                                                               

                                                              Trevor