I have Excel 2008 on my Mac. About the only complicated thing I ever want to do with Excel files is to manipulate them line by line and spit the output into a cut and pasteable source for my clipboard. I have a vbscript version for Windows, but when I'm in 2008 land, I'm sunk. No vbs. Only Applescript. Stinks.

Frustrated with having to wait until I'm back on my Windows box, not yet willing to shell out more cash and buy Office 2011 (and fed up enough with Office 2004 that I threw it away the second 2008 hit my box), I'm finally going to figure out how to port the code to Applescript.

So first let's essentially run Hello, World, and put a value into a specific Excel cell.

tell application "Microsoft Excel"
 tell worksheet "Sheet1" of active workbook
  set value of cell "A1" to 35
 end tell
end tell


Going to Sheet2 is exactly like you'd expect.

tell application "Microsoft Excel"
 tell worksheet "Sheet2" of active workbook
  set value of cell "A1" to 35
 end tell
end tell


So once we start playing around, we'll probably want some way to keep track of what we've found. In my case, I want to turn line after line from an Excel file to SQL statements. Instead of a giant dialog box, I'll just slap that into a file.

set theText to "some text"
set theFilePath to (path to desktop as string) & "test.txt" as string
set theFileReference to open for access theFilePath with write permission
write theText to theFileReference
close access theFileReference


So now all we've got to do is to throw the contents of each cell into theText. So now we need to find out how to iterate through all Excel cells?

This took a little longer that I would have expected. The key is this line:
get value of cell c of row r of active sheet


That takes the place of .Cells(x,y)

So here's the end result.

tell application "Microsoft Excel"
 
 say "Saving at the end"

 set lastCol to 10 -- yes, I'm cheating.  
 -- you have to know the last col and row for this to work
 set lastRow to 1000 -- I suppose I could add dialog boxes to ask max nums
 set c to 1
 set r to 2 -- that's the first row that's got info it in; skip the first row's col headers
 set strCells to ""
 
 try
  repeat until r = lastRow
   set c to 2
   set strCells to strCells & (r as text) & " -- "
   repeat until c = lastCol
    --display dialog "Row: " & r & " Col: " & c
    set theValue to get value of cell c of row r of active sheet
    set strCells to strCells & theValue & " :: " as text
    -- just use & to append; cast to text with "as text"
    set c to (c + 1)
   end repeat
   set strCells to strCells & return -- return adds a MacOS newline
   set r to (r + 1)
   if r mod 20 = 0 then
    -- beep -- add some sort of feedback.  
    say r -- add cooler feedback.  This talks.
    -- Btw, Applescript for this in Excel is insanely slooooow 
    -- vs. my experience with vbs.  It takes several seconds to 
    -- do 10 rows of ten columns of customer address and phone, etc.
    -- information.
   end if
  end repeat
  close access theFileReference
  
  -- write strCells to theFileReference
 on error
  say "error in the loop"
 end try
 
 try
  set theFilePath to (path to desktop as string) & "test2.txt" as string
  set theFileReference to open for access theFilePath with write permission
  write strCells to theFileReference
  close access theFileReference
 on error
  say "Error in writing to file"
  close access theFileReference
  -- this close seems important.  I can't tell how to get
  -- back the handle to close this if I had an error cause it
  -- to stay open from a past script invocation.
 end try
  
 say "Done!"

end tell


This is PAINFULLY slow, however. I'm used to it screaming. I tried rearranging the code to write out the jive to the file with each line, and that's not noticeably quicker.

tell application "Microsoft Excel"
 
 say "Saving with every row"
 
 set lastCol to 10 -- yes, I'm cheating.  
 -- you have to know the last col and row for this to work
 set lastRow to 100 -- I suppose I could add dialog boxes to ask max nums
 set c to 1
 set r to 2 -- that's the first row that's got info it in; skip the first row's col headers
 set strCells to ""
 
 set theFilePath to (path to desktop as string) & "test5.txt" as string
 set theFileReference to open for access theFilePath with write permission
 try
  repeat until r = lastRow + 1
   set c to 2
   repeat until c = lastCol + 1
    --display dialog "Row: " & r & " Col: " & c
    set theValue to get value of cell c of row r of active sheet
    set strCells to strCells & theValue & " :: " as text
    -- just use & to append; cast to text with "as text"
    set c to (c + 1)
   end repeat
   set strCells to (r as text) & " -- " & strCells & return -- return adds a MacOS newline
   write strCells to theFileReference
   set strCells to ""
   set r to (r + 1)
   if r mod 50 = 0 then
    -- beep -- add some sort of feedback.  
    say r -- add cooler feedback.  This talks.
    -- Btw, Applescript for this in Excel is insanely slooooow 
    -- vs. my experience with vbs.  It takes several seconds to 
    -- do 10 rows of ten columns of customer address and phone, etc.
    -- information.
   end if
  end repeat
  close access theFileReference
  
  -- write strCells to theFileReference
 on error
  say "error in the loop"
  close access theFileReference
  -- this close seems important.  I can't tell how to get
  -- back the handle to close this if I had an error cause it
  -- to stay open from a past script invocation.
 end try
 
 say "Done!"
 display dialog "Done with write to file"
end tell


So it works, in a sort of brute force, inefficient way. The biggest issue I've had is if it's cancelled. Though it says "error in the loops", the file ref seems not to be closed, and then the file I'm writing to remains open with no opportunity to close it. Once the script's done, it just remains open. I tried deleting on the command line with Terminal and the file was still "there" as far as the script was concerned. Perhaps when I reboot it'll be gone, but I can't Google a way to close it. Most answers seem to talk about finding the app that opened it, but when you're the app that did it, I'm not sure what's up.

In any event, it does marginally spider through cell by cell and print things out to a text file, which is what I wanted. You just have to be really patient. No wonder they added

Labels: , , , ,