Thursday, December 13, 2018

#1 2010-04-01 06:51:54 pm

astronaut1973
Member
Registered: 2010-02-26
Posts: 4

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

Applescript:


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)


Filed under: folder, excel, used range

Offline

 

#2 2010-04-01 11:50:03 pm

Trash Man
Sanitation Department
Registered: 2005-10-20
Posts: 5336

Re: creating folders from excel values

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.

Applescript:

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


One mans trash is another mans treasure

Offline

 

#3 2010-04-02 12:58:15 am

astronaut1973
Member
Registered: 2010-02-26
Posts: 4

Re: creating folders from excel values

Great! It returns an error though.

error "Finder got an error: Can’t make {4.10078E+5, \"_\", \"\"} into type Unicode text." number -1700 from {4.10078E+5, "_", ""} to Unicode text


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.

Offline

 

#4 2010-04-02 02:25:07 am

StefanK
Member
From:: St. Gallen, Switzerland
Registered: 2006-10-21
Posts: 11526
Website

Re: creating folders from excel values

Hi,

try this

Applescript:


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

Last edited by StefanK (2010-04-02 02:27:54 am)


regards

Stefan

Offline

 

#5 2010-04-03 12:19:19 pm

astronaut1973
Member
Registered: 2010-02-26
Posts: 4

Re: creating folders from excel values

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

Offline

 

#6 2010-04-08 11:04:24 am

astronaut1973
Member
Registered: 2010-02-26
Posts: 4

Re: creating folders from excel values

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.

Offline

 

#7 2018-12-05 05:42:03 pm

gato
Member
Registered: 2018-12-04
Posts: 1

Re: creating folders from excel values

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!

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)