I’m still a newbie and not quite able to get my head around some (most) of Applescript’s quirks. I’m trying to generate a bunch of folders with names from cell values in Excel. Let’s say i have a workbook file with these values: A1:“some number”, B1:“Some name”. This will generate a folder with the name “some number_some name”
The below script takes me almost half way. As you can see, i only get the first half of the name.
I have a few questions that i hope some of you may be able to answer:
(The obvious one) How do i get the second part of the name? I have tried quite a few things, but none of them compiles.
The number of rows in the workbook may vary, but the first value will always start at row 9. How do i set a used range from row 9 down to the last row?
set numList to getCellList("D", 9, 300)
set myDoc to choose file with prompt "Velg Excell-dokument"
set myFolder to choose folder with prompt "Hvor vil du ha mappene?"
repeat with i in numList
tell application "Microsoft Excel" to tell document (myDoc as string) to tell sheet 1 to set cellValue to (value of cell i)
tell application "Finder" to make new folder at myFolder with properties {name:cellValue}
end repeat
on getCellList(aLetter, startNumber, endNumber)
set myList to {}
repeat with i from startNumber to endNumber
set end of myList to (aLetter & i) as string
end repeat
return myList
end getCellList
Model: MacBookPro
AppleScript: 2.3
Browser: Safari 531.9
Operating System: Mac OS X (10.6)
The script is not tested and I haven’t got the time to explain it, but I guess you should be able to figure it out yourself.
set startRow to 9
set endRow to 300
set D_Collumn to getCellList("D", startRow, endRow) -- Column 1
set E_Collumn to getCellList("E", startRow, endRow) -- Column 2
set myDoc to choose file with prompt "Velg Excell-dokument" -- Choose Doc
set myFolder to choose folder with prompt "Hvor vil du ha mappene?" -- Choose output folder
repeat with i from startRow to endRow
tell application "Microsoft Excel"
tell document (myDoc as string) to tell sheet 1 to set D_CellValue to (value of cell (item i of D_Collumn)) -- value of cell i of column D
tell document (myDoc as string) to tell sheet 1 to set E_CellValue to (value of cell (item i of E_Collumn)) -- value of cell i of collumn E
end tell
set folderName to (D_CellValue & "_" & E_CellValue) -- Create name
tell application "Finder" to make new folder at myFolder with properties {name:folderName} -- Make new folder
end repeat
on getCellList(aLetter, startNumber, endNumber)
set myList to {}
repeat with i from startNumber to endNumber
set end of myList to (aLetter & i) as string
end repeat
return myList
end getCellList
It seems that Excel puts a tab between the two name variables (as it should, after all it’s a spreadsheet), but that doesn’t go well with filenames in osx. I’ll see if i can figure out how to remove the tab.
property firstColumn : "D"
property firstRow : 9
set myDoc to choose file with prompt "Velg Excell-dokument"
set myFolder to choose folder with prompt "Hvor vil du ha mappene?"
tell application "Microsoft Excel" to open myDoc
set x to firstRow
set nextColumn to (ASCII character ((ASCII number firstColumn) + 1))
repeat
tell application "Microsoft Excel" to set {v1, v2} to item 1 of (get value of range (firstColumn & x & ":" & nextColumn & x))
if v1 is "" or v2 is "" then exit repeat
tell application "Finder" to make new folder at myFolder with properties {name:((v1 as integer) as text) & "_" & v2}
set x to x + 1
end repeat
I have run into another problem. While i have been able to create subfolders (level 2) for each folder in the list, i can’t figure out how to create another folder level (level 3). In addition i would like the third folder level to be present only in some of the folders in the second level.
This is helpful! I’m in the process of modifying it for my needs. How would I make it skip empty Excel cells? My attempts thus far have been unsuccessful.