Scripting Windows (VBS) vs MAC - Get it to work on both?

All Replies

• Friday, December 12, 2008 7:31 PM

   

   
  

  0

  Sign In to Vote
  Here is an excerpt from the manual but I am not sure what it is trying to say....
  
  I am guessing the "Language Support" refers to english, spanish, etc....   Or does it mean scripting language.
  
  

  Language support
  By default, Expression Media uses the language of the operating system or remembers the last
  language used. For information about how to change the language used by Expression Media, see ―‖on page .

   
• Friday, December 12, 2008 7:51 PM

  Moderator

   

   
  

  0

  Sign In to Vote
    I'm sorry - the Mac can't run VB script and there is no translation utility that I know of. Your script would have to be rewritten in AppleScript.
  
  We do have paid support options for advisory cases, where we could translate your code into AppleScript, but they are pretty expensive.
  
  Other than that, here are some resources for AppleScript:
  
  http://homepage.mac.com/swain/Macinchem/Applescript/AppScript_tut/AppScrip_tut_1/appscript_tut_1.htm
  http://www.tandb.com.au/applescript/tutorial/
  http://developer.apple.com/documentation/AppleScript/Conceptual/AppleScriptX/AppleScriptX.html
  
  Hope this helps - Anita Oakley
  Microsoft Expression Media Support

  • Marked As Answer by Anita OakleyMicrosoft Employee, Moderator Friday, December 12, 2008 7:52 PM
  • Unmarked As Answer by bdempster Saturday, December 13, 2008 3:25 PM
  • Marked As Answer by bdempster Saturday, December 13, 2008 3:25 PM
  •  
   
• Friday, December 12, 2008 8:46 PM

   

   
  

  0

  Sign In to Vote
  You can export information out of xMedia using XSL and this will work cross-platform.  Here are some links to examples I've posted on other forums:
  
  http://thedambook.com/smf/index.php?topic=3014.0
  
  http://forum.iview-multimedia.com/viewtopic.php?t=6151
  
  http://forum.iview-multimedia.com/viewtopic.php?p=21288
  
  You will need to scroll down to see my postings in reply to the original posts.
  
  It's probable that these examples won't do exactly what you require.  From the code extract above it says you are 'required to pull keywords from the catalog and export these to an Excel template'.  Well XSL won't be able to create a .xls Excel file.  It can create a text file, which would be .txt or .csv.  With a bit of work it would be possible to export the data in XML that is compliant with Office 2003's XML format - the file extension would be .xlsx.
  
  If you can explain your exact requirements, maybe with example output, then I can tell you what can, or can't, be done.
  
  Ian
  
  
   
• Friday, December 12, 2008 9:18 PM

   

   
  

  0

  Sign In to Vote
   Thanks for the information.... That is a very good possibility.  Below is the entire VBScript that works great with the windows version.  I am not that familiar with XML and XSLT but if XSL can create a CSV file it should be straight forward to import it into excel and fit my template.
  
  Short Version:
      Step 1) Export Keywords from active catalog to CSV
      Step 2) Open CSV in Excel and save it as XLS
  

  
  
  
  

  '**************************************************************************************************************************************************

  '* Created: December 12, 2008

  '* Author: Brandon Dempster

  '*

  '* Helpful Resources:

  '*      http://www.beardsworth.co.uk/downloads/iview/Get_MS_Office_document_properties.vbs.txt

  '*      http://forum.iview-multimedia.com/viewtopic.php?t=6908

  '*      PDF: http://download.microsoft.com/download/3/0/d/30df7f2d-1ba9-4868-bc16-c7d90ffef015/Media%202%20Scripting%20Guide%20for%20Windows_Final_2.pdf

  '*

  '**************************************************************************************************************************************************

  On Error Resume Next

  Dim app, mediaItems, mediaItem, i, j, catalog, prompt, initNumber, keyWord, oldKey,oIE, path, Title, level

  Dim L_title_text, L_message1_text, L_message2_text, L_message3_text, L_message4_text

  L_title_text = "Microsoft Expression Media"

  L_message1_text = "Please launch Microsoft Expression Media."

  L_message2_text = "You need to select at least one media item in the active catalog in order to use this script."

  Main()

  Sub Main()

  Set app = CreateObject("ExpressionMedia.Application")

  ' get the active catalog

  If (app.Catalogs.count = 0) Then

  MsgBox L_message1_text, vbCritical, L_title_text

  Elseif (app.ActiveCatalog.Selection.Count = 0) Then

  MsgBox L_message2_text, vbCritical, L_title_text

  Else

  'Declare string with column headings

  Dim myString

  'myString = "File_Name" & vbTab & "Keywords" & vbCrLf

  myString = "Photographers image reference number"  & "," & "caption (50 characters or less)" & "," & "Model Release" & _

  "," & "Property Release" & "," & "Photographers Name" & "," & "Keywords" & "," & "Other" & vbCrLf

  For Each mediaItem In app.ActiveCatalog.Selection

  'Create CSV string for output to file

  myStringmyString = myString & mediaItem.Name & ",,NA,NA,," & mediaItem.Annotations.keyWords & "," & _

  mediaItem.Annotations.Location & " " & mediaItem.Annotations.City & " " & mediaItem.Annotations.State & vbCrLf

  Next

  End If

  'Output keywords to CSV file

  WriteToFile(myString)

  End Sub

  Function WriteToFile(strText)

  Dim objFSO, objFolder, objShell, objTextFile, objFile

  Dim strDirectory, strFile, strFileXLS

  strDirectory = "c:\Keywording"

  strFile = InputBox("Name of Keyword File: ")

  strFileXLS = "\" & strFile & ".xls"

  strFile = "\" & strFile & ".csv"

  'msgbox(strFile)

  ' Create the File System Object

  Set objFSO = CreateObject("Scripting.FileSystemObject")

  'Delete Temp File if it still exists

  If objFSO.FileExists(strDirectory & strFile) Then

  dim answer

  answer=MsgBox("You are about to overwrite an existing keywords export!  Is this ok? ",4,"Warning!")

  'msgbox("Answer: " & answer)

  If answer=6 then 'YES

  Set objFolder = objFSO.GetFolder(strDirectory)

  objFSO.DeleteFile(strDirectory & strFile)

  Else 'NO; display existing file

  If err.number = vbEmpty then

  Set objShell = CreateObject("WScript.Shell")

  objShell.run ("Explorer" &" " & strDirectory & "\" )

  Else msgbox("VBScript Error: " & err.number)

  End If

  EXIT Function'If answer is no then display existing file and exit function

  End If

  End If

  ' Check that the strDirectory folder exists

  If objFSO.FolderExists(strDirectory) Then

  Set objFolder = objFSO.GetFolder(strDirectory)

  Else

  Set objFolder = objFSO.CreateFolder(strDirectory)

  'msgbox("Just created " & strDirectory)

  End If

  If objFSO.FileExists(strDirectory & strFile) Then

  Set objFolder = objFSO.GetFolder(strDirectory)

  Else

  Set objFile = objFSO.CreateTextFile(strDirectory & strFile)

  'msgbox("Just created " & strDirectory & strFile)

  End If

  set objFile = nothing

  set objFolder = nothing

  ' OpenTextFile Method needs a Const value

  ' ForAppending = 8 ForReading = 1, ForWriting = 2

  Const ForAppending = 8

  Set objTextFile = objFSO.OpenTextFile _

  (strDirectory & strFile, ForAppending, True)

  ' Writes strText every time you run this VBScript

  objTextFile.WriteLine(strText)

  objTextFile.Close

  'Open File Directory

  'If err.number = vbEmpty then

  '   Set objShell = CreateObject("WScript.Shell")

  '   objShell.run ("Explorer" &" " & strDirectory & "\" )

  'Else msgbox("VBScript Error: " & err.number)

  'End If

  'EXPORT to Excel

  srccsvfile = strDirectory & strFile

  srcxlsfile = strDirectory & strFileXLS

  Set objExcel = CreateObject("Excel.Application")

  objExcel.Visible = True

  objExcel.displayalerts=True

  Set objWorkbook = objExcel.Workbooks.open(srccsvfile)

  Set objWorksheet1 = objWorkbook.Worksheets(1)

  objWorksheet1.SaveAs srcxlsfile, 2

  WScript.Quit

  End Function

   
• Friday, December 12, 2008 10:32 PM

   

   
  

  0

  Sign In to Vote
  Brandon,
  
  This is straightforward with XSL, can easily get a CSV file of the required data out of this.
  
  It's getting late where I live and I've got a beer with my name on it in the fridge so I'll have a look at this tomorrow.
  
  Ian
   
• Saturday, December 13, 2008 12:09 PM

   

   
  

  0

  Sign In to Vote

  Brandon,
  
  OK - the following XSL script will create the same CSV output as your VBS script, but should work on Windows and MAC.  Note that the VBS script has a couple of issues with it:  first there's a 'bug' where you are building the output string and have got myStringmyString, i.e. repeated.  This means it only outputs the header line with no actual details - easily fixed :).  The second issue, and it won't appear in 99% of catalogues, is that if any keyword has a comma in it then it will cause the CSV file to get the columns mixed up.  I consider it bad practice to have commas in a keyword but have seen it.  This script will cope with this, however it puts quotes around the whole data item, which is standard CSV practice.  Excel ignores these quotes and when you save the CSV file as a XLS version they will disappear.
  
  However there is also a problem in my method which is a feature (aka bug?) in xMedia.  When you execute the XSL script it always works on the whole catalogue rather than just the selected images.  I don't know if this is by design or not, but it's annoying.
  
  Anyway here's the script:

  <?xml version="1.0" encoding="utf-8"?>

  <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output method="text" indent="no"/>

  <xsl:template match="/">

  <!-- Column Headers -->

  <xsl:text>Photographers image reference number</xsl:text>

  <xsl:text>,caption (50 characters or less)</xsl:text>

  <xsl:text>,Model Release</xsl:text>

  <xsl:text>,Property Release</xsl:text>

  <xsl:text>,Photographers Name</xsl:text>

  <xsl:text>,Keywords</xsl:text>

  <xsl:text>,Other</xsl:text>

  <xsl:text>&#xD;</xsl:text>

  <!-- Loop through each media item -->

  <xsl:for-each select="CatalogType/MediaItemList/MediaItem">

  <!-- Output required data -->

  <xsl:value-of select="AssetProperties/Filename"/>

  <xsl:text>,</xsl:text>

  <!-- blank column -->

  <xsl:text>,</xsl:text>

  <xsl:text>NA</xsl:text>

  <xsl:text>,</xsl:text>

  <xsl:text>NA</xsl:text>

  <xsl:text>,</xsl:text>

  <!-- blank column -->

  <xsl:text>,</xsl:text>

  <!-- Open quotes - handling commas keywords -->

  <xsl:text>"</xsl:text>

  <xsl:for-each select="AnnotationFields/Keyword">

  <xsl:value-of select="."/>

  <xsl:if test="position() &lt; last()">

  <xsl:text>;</xsl:text>

  </xsl:if>

  </xsl:for-each>

  <!-- Close quotes - handling commas keywords -->

  <xsl:text>"</xsl:text>

  <xsl:text>,</xsl:text>

  <xsl:value-of select="AnnotationFields/Location"/>

  <xsl:text> </xsl:text>

  <xsl:value-of select="AnnotationFields/City"/>

  <xsl:text> </xsl:text>

  <xsl:value-of select="AnnotationFields/State"/>

  <xsl:text>&#xD;</xsl:text>

  </xsl:for-each>

  </xsl:template>

  </xsl:stylesheet>

  You need to copy the above into a text file and save it.  The file name is up to you but the extension should be ".xsl".  Now open your catalogue and from the "Make" menu select "XML Data File...".  A new window will appear with various options.   The only one required is the "Annotations" tick box, which must be ticked.  I would recommend that you don't tick the others as it will speed the processing up.  Don't tick anything in the "Create Folders" section otherwise it will create some folders for you!  At the bottom of the window is a section for "XSL Transform".  There is a button with a "+" on it.  Select this and it will display a Choose File dialog.  You now need to select the XSL file that you have just created.  Now select the "Make" button.  This will now prompt you to enter a file name and location for the result of the XSL transformation.  You must specify the extension to the file name as it defaults to XML.  You need to add ".csv".  Select the "Save" button and it will create the CSV file for you.
  
  I've used the same column names etc as in your script and get identical results in Excel on my test catalogue with the exception of keywords that contain commas.  I've made the above script a bit more verbose that it needs so that you can clearly see where each column header and data is output.  The only "trick" required is that in the XML version of the catalogue each keyword is held separately.  This means the ";" delimiter has to be added manually to recreate the output from your VBS script.  The trick is to not add a delimiter to the end of the list i.e. you want to see "Word 1;Word 2;Word 3" and not "Word 1;Word 2;Word 3;".
  
  If you were to set up custom fields in xMedia you could include information such as Model Release and Photographers Name in your catalogue, rather than having to add them to a spreadsheet where they are not linked to the images.  They could then be exported by XSL to CSV.  The only thing that would be difficult is to export any data held within Catalogue Sets - it's only difficult because I'm not an XSL expert, just a dabbler!
  
  Note that if you still want to use VBS you can call the XSL transformation from there.  I've not done this but it is documented in the scripting guide.  Of course this won't work directly on a MAC, but would make any VBS script smaller, and so potentially easier to port to AppleScript.
  
  Anyway I hope the script is of use.  If you have any questions then let me know and I'll help if I can.
  
  Ian

  • Marked As Answer by bdempster Saturday, December 13, 2008 3:25 PM
  •  
   
• Saturday, December 13, 2008 1:12 PM

   

   
  

  0

  Sign In to Vote
  ian.w said:

  However there is also a problem in my method which is a feature (aka bug?) in xMedia.  When you execute the XSL script it always works on the whole catalogue rather than just the selected images.  I don't know if this is by design or not, but it's annoying.
  
  

  
  I've changed my mind on this!  If you select the images that you want to export the details for and then do "Show Selected" on the "Find" menu it hides all the unselected images.  Then the export works for just the images in view.
  
  
  However there is a more fatal problem in that the XML export doesn't work at all if any of the images are not on-line i.e. on CDs, DVDs or dismounted hard drives.  This is an annoying feature as they are only needed if you select any of the "Create Folders" options in the XSL Data File options.
  
  Ian
   
• Saturday, December 13, 2008 2:10 PM

   

   
  

  0

  Sign In to Vote

  THANKS IAN!  It gives me enough to figure out another option.  The first bug you mentioned "myStringmyString" actually happened when I cut and paste it into the forum window for some reason and it wouldn't let me delete it....  I didn't even think about the issue with the commas in the keywords not working....  I have not ran into any of my images with commas in the keywords but I can see how that would cause issues.

  Thanks for the detailed instructions... I will give it a shot today.  Have a good weekend.

  • Edited by bdempster Saturday, December 13, 2008 3:29 PM
  •  
   
• Saturday, December 13, 2008 3:26 PM

   

   
  

  0

  Sign In to Vote
  While I like the script because it is a one step process.... The transform works perfectly.....  I was able to try it on both a Mac and PC, it works well.
  
  Thanks for all your help!  You saved me a lot of time.
   
• Friday, January 30, 2009 9:38 PM

   

   
  

  0

  Sign In to Vote

  Just to follow-up on my own original post...  I have done a lot of research and found serveral ways to do this depending on your OS.

  1) Using VBScript to Export to Excel

  2) The method that Ian explains above

  3) Using VBA in Excel

  All three methods work well on Windows but the only one that works on MAC is number 2.

  For More Details and Examples

  Thanks again IAN.

  • Marked As Answer by bdempster Friday, January 30, 2009 9:42 PM
  •