creating folders from excel values

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:

  1. (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.
  2. 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?
  3. Can someone hold my hand through the last part of the script below? I have copied the entire script from this thread: http://macscripter.net/viewtopic.php?id=31835

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

Hope it works,
ief2

Great! It returns an error though.

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.

Hi,

try this


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

That works like a charm! I modified it to include a set of subfolders and a closing dialog. Thank you for helping.

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.

Thanks!