The Excel portion of the following script doesn;t work. I made this in OS 8.1 (The best operating system Apple ever Had), and it worked perfectly. Why do the Apple programmers make things more complicated than easier. I used to be able to write a MacScript script as follows: MacScript "Hard disk name:folder name:file name:
abd it worked perfectly. Now I have to write a 5 line script. This is progress?
Anyway, any help with this script would be appreciated.
tell application “Finder”
activate
set hdName to “HD”
display dialog ¬
“Enter the name of a folder to create list for.” default answer “Excel 98 Files”
set file_title to text returned of result
set file_list to hdName & “:” & file_title
set fileName to file_list
set fileName to fileName & “:”
set file_list to list folder file_list
set holder to item 1 of file_list
set holder2 to fileName & holder & “:”
repeat with x from 1 to number of items of file_list
set holder to item x of file_list
set holder2 to fileName & holder & “:”
end repeat
end tell
tell application “Microsoft Excel”
Activate
set FormulaR1C1 of Range “R1C8” to hdName
Select Range “R1C8:R1C9”
set ColorIndex of Font of Selection to 3
set Bold of Font of Selection to true
set FormulaR1C1 of Range “R2C8” to “=COUNTA(R4C2:R300C2)”
Select Range “R2C8”
set HorizontalAlignment of Selection to xlLeft
set ColorIndex of Font of Selection to 3
set Bold of Font of Selection to true
set FormulaR1C1 of Range “R2C3” to file_title
set y to 4
set z to 1
repeat with x from 1 to number of items of file_list
set FormulaR1C1 of Range (“R” & y & “C1”) to z
set FormulaR1C1 of Range (“R” & y & “C2”) to item x of file_list
set y to y + 1
set z to z + 1
end repeat
Select Range “R2C3”
set ColorIndex of Font of Selection to 3
set Size of Font of Selection to 18
set Bold of Font of Selection to true
set Name of Font of Selection to “New York”
set FormulaR1C1 of Range “R3C2” to “File”
Select Range “R3C2”
set Bold of Font of Selection to true
set HorizontalAlignment of Selection to xlCenter
set DisplayHeadings of ActiveWindow to false
set DisplayGridlines of ActiveWindow to false
Select Range “R1C1:R150C12”
set ColorIndex of Interior of Selection to 15
set Pattern of Interior of Selection to xlSolid
Select Range (“R1C2”)
set ColumnWidth of Selection to 25
Select Range “R3C8”
set HorizontalAlignment of Selection to xlLeft
set sheetName to Name of ActiveSheet
if the number of items of file_title < 32 then
set Name of Sheet sheetName to file_title
end if
Select Range “R1C1”
beep
end tell
The problem is only with the Excel portiion of the script.
Jack:
Sorry to hear about your frustration with scripting OSX. Respectfully, however, your problem may still be in the Finder portion. You are working way too hard to get the filenames for Excel to process. Consider this script for the front portion:
tell application "Finder"
set a to choose folder
set b to every file in folder a --list [b] contains all the complete paths to the files in the chosen folder, but in a raw format
end tell
repeat with c from 1 to count b
set this_file to item c of b as Unicode text --This sets the filename to the Unicode format that the Application can understand
--Do the Excel stuff
end repeat
I do not have much experience with scripting Excel, but there is a huge pdf document available from Microsoft that may help you if there are still difficulties:
http://www.microsoft.com/mac/resources/resources.aspx?pid=asforoffice
Good luck, and keep posting if there are still problems.
casdvm
Thanks for trying to help. Your script wouldn’t compile and I very carefully copied it exactly. It didn’t like the “a” at the end of the 3rd line.
In my file, it didn’t like the Activate after Microsoft Excel.
Even though I may have used to much text in the Finder part, it came out with all the correct answers for Excel. I checked it with a display dialog function. I did download that PDF file you mentioned, and it would take me four years to get anything out of it. I did spend a couple of hours on it, and could find nothing helpful.
I do appreciate your writing.
I have been a Macintosh advocate since 1986, but since they went to OS 8.5 and made the folder relative instead of absolute I thing they are on a quick slide down hill. I think Tiger is the worst of all. If they didn’t have IPod they would be in real trouble.
Jack Karnes
Jack:
There is no need to type any script from this forum that is enclosed within a box. The top line of the box states:
Open this Scriplet in your Editor
Simply click on that line, and whatever your default editor is should pop up and the code within the box will now be in your editor and should compile with the push of a button. Please try my original script again; I am using Script Editor 2.1.1 in OSX 10.4.3. It simply must work for your system as well as mine.
I know that the pdf file from Microsoft is huge, but it is searchable within Acrobat Reader. The best way I have found to use it is to search for what I am looking for, copy their syntax and start from there.
I am using the most recent version of Excel for Mac (2004), and I assume you are also, since you are working in the 10.4.2 environment (albeit begrudgingly). I have not had time to play around with your Excel code; could you please explain what you are trying to accomplish with it? I would be happy to work on it and see what I can do. I don’t use Excel much, but am always on the lookout to learn how to script something new.
I am a recent convert to the Mac (Nov 2002) and have only known OSX. After 10 years of struggling with Windows (I believe their only decent version was 3.11) I just quit. I was tired of being a slave to the machine instead the machine serving me. I used a Commodore 64 for over 10 years. It got me through college and the early years of my career. THAT was a fine machine and I still believe it was the best PC ever designed for the home user. I wrote a lot of code (mostly for fun, but some for profit) on that baby, and I still wish sometimes I had it today. (My wife made me clear it out and that was a sad day.) Anyway, I personally adore OSX because I feel free again. Free to do whatever I want with my machine instead of being forced to do it the machine’s way. I think Applescript and UNIX are the best and have enjoyed learning them these last few months. So anyway, sorry to ramble on, but I thought you might enjoy the perspective of a recent initiate.
Anyway, let’s work on getting your Excel script to function properly.
Craig Smith
Really appreciate your interest, however, I must disagree with you that the problem is in the “Finder” portion. The display dialog responses would never had worked if there was a problem before coming to them. As to the PDF file, I did use the search and tried there examples, and they wouldn’t compile. I wish you had Excel 98 and AppleScript extension 1.01 and I think you might think differently. That script simply list the files in any folder you select on an Excel worksheet. I have a web site with many Excel files. It has been published all over the world and I was called the guru of Excel by MacAddict magazine. The address is: http://www.mindspring.com/~jackkarnes
Apple changed the language when they went to 8.5. This obsoleted hundreds of scripts that people were using and depending on. They also dropped the best program they had, HyperCard. I was able to use my old scripts on 9.2 by trashing the AppleScript extension 1.8 with 1.01. I only use 9.2 because I have to to run my Epson 1280 printer. Everything else is done on 8.1. I got 10.4.2 because I had to to run PowerPoint 2004 which will take more music formats than PowerPoint 98. Well, that’s my story and I’m sticking to it. Anyway I like your posts and I wished you lived next door so we could actually figure these things out.
Ahhhh, now I see much more clearly. It really is a shame to have spent all that effort putting those nifty scripts together, and now seeing most of them become dysfunctional due to “improved” software versions. My attitude, however, is to blame Bill, not Steve. 
Nice site, by the way.
Craig
Thanks Craig for looking at my site. Acturally, all the ones that don’t contain AppleScript will work on 2004. Download Complete Suite. It contains a distance finder that I am most proud of having only one year of college Algebra. I know the troubles with PC’s as I had to install Virtual PC to adjust the dialog boxes in the macros. If the file is in the PC liist it will work on 2004 because it doesn’t contain AppleScript. However, use the Mac version with the same title./Jack
I was wrong about my Excel files working on 2004. Although the macros will work, it is the same problem that I have with PC’s. The fonts are too big for the buttons. All the buttons have to be resized. The dialog boxes are not too bad, but this is not a good example of my work. I’m fixing up Complete Suite for 2004, and I will put it up on my web site./Jack