generating folders from two fields in an excel sheet

Scripting Forums AppleScript | Mac OS X
#1

I am using an applescript to generate folders from an excel sheet and naming them with a combination of column A and column E. Like so:

Column A.Column E

My problem is column A will never be empty, But Column E may or may not be. When Column E is empty the script stops.

I am still learning about scripting, How Can I tell it if E is empty to just use A without the separator? I am in OS 10.5.8 and using excel for Mac 2011

Here is my script so far:

property firstColumn : “A”
property firstRow : 2

set myDoc to choose file with prompt “Select Database”
set myFolder to “HOM_Shortrun:HOM Calendars 2006:”
tell application “Microsoft Excel” to open myDoc
set x to firstRow
set nextColumn to (ASCII character ((ASCII number firstColumn) + 4))
repeat
tell application “Microsoft Excel” to set {v1, v2, v3, v4, v5} to item 1 of (get value of range (firstColumn & x & “:” & nextColumn & x))
if v1 is “” or v5 is “” then exit repeat
tell application “Finder” to make new folder at myFolder with properties {name:((v1 as text) as text) & “.” & v5}
set x to x + 1
end repeat

Thanks

#2

Seems to me, you just have let the script get the value in column E and see if it’s empty. Then use an if statement to adjust the name. This subroutine might give you an idea how to access the data in the cell. Hope it helps you.


to getDataFromExcelCellByRC(rowNum, colNum, shName, wbName, dataType)
	-- P0158v1.10
	-- Independent
	
	-- if dataType = 1, then the DATA value is obtained, else the STRING value is obtained
	
	tell application "Microsoft Excel"
		if dataType = 1 then
			set output to value of cell rowNum of column colNum of sheet shName of workbook wbName
		else
			set output to string value of cell rowNum of column colNum of sheet shName of workbook wbName
		end if -- dataType = 1
	end tell -- "Microsoft Excel"
end getDataFromExcelCellByRC
#3

Actually it was much simpler than that. It was kinda already there. It was exiting repeat if either field was “” so I took out the reference to the Column E so

if v1 is “” or v5 is “” then exit repeat
became
if v1 is “” then exit repeat

Thanks for taking the time to respond though. It got me to look at it differently.

Here’s the final script. Have a great day.

property firstColumn : "A"
property firstRow : 2

set myDoc to choose file with prompt "Select Database"
set myFolder to "HOM_Shortrun:HOM Calendars 2006:"
tell application "Microsoft Excel" to open myDoc
set x to firstRow
set nextColumn to (ASCII character ((ASCII number firstColumn) + 4))
repeat
	tell application "Microsoft Excel" to set {v1, v2, v3, v4, v5} to item 1 of (get value of range (firstColumn & x & ":" & nextColumn & x))
	if v1 is "" then exit repeat
	tell application "Finder" to make new folder at myFolder with properties {name:((v1 as text) as text) & "." & v5}
	set x to x + 1
end repeat