Need Help creating files from excel data

Scripting Forums AppleScript | Mac OS X
#1

Hi, I am new to applescript and need a little help. I have an excel spreadsheet with two columns containing file name and file contents. There are around 1000 rows. I need to create an apple script that will take the value of A1 and create a file based on that name and the value of B1 as the file contents. I need this to loop through all rows so that in the end, I have a new file for each row. Here is a simple sample dataset:

A+++++++++++++++++++++++B++++++++++++++++++++++

test.txt Sample dataset 1
test2.txt Sample dataset 2
test3.txt Sample dataset 3

I have the following code which will create a file called test.txt with Sampledata set 1 in the file. However, I need it so this code will do this for every row in the file and not just A1 and B1.


tell application "Microsoft Excel"
	
	-- Activates Microsoft Excel.
	activate
	
	-- Assigns the value of cell A1 to the variable myName.
	copy value of cell "A1" to myName
	
	-- Displays a dialog box with the value of myName.
	display dialog myName
	
	-- Assigns the value of cell B1 to the variable myData.
	copy value of cell "B1" to myData
	
	-- Displays a dialog box with the value of myData.
	display dialog myData
	
	
end tell

set logtype to myData -- some number for example
set fileName to myName
set fname to "test"
set folderPath to "/Users/myusername/Documents/" & fname
set theFile to POSIX file (folderPath & "/" & fileName) -- that file doesn't exist yet

set N to open for access theFile with write permission -- creates the text file
set eof N to 0
write (logtype as text) to N
close access N

tell application "TextEdit" to open theFile

Thank you

#2

Hi,

try this


set folderPath to (choose folder with prompt "choose destination folder" default location (path to documents folder)) as text

tell application "Microsoft Excel" to set theData to value of used range of active sheet
repeat with aRow in theData
	set {fileName, fileContents} to items 1 thru 2 of aRow
	set textFile to folderPath & fileName
	try
		set fileReference to open for access file textFile with write permission
		set eof fileReference to 0
		write fileContents to fileReference
		close access fileReference
	on error
		try
			close access file textFile
		end try
	end try
end repeat
#3

StefanK: Wow! Thank you for the quick reply. It worked perfectly!