Creating Markdown files from Excel

Scripting Forums AppleScript | Mac OS X
#1

I’m brand new to AppleScript and trying to create a markdown file from each row of data in an Excel sheet. The number of columns vary, but the operation is the same.

An example Excel file has a header row with A1=Team, B1=Sport and C1=City and the rest of the file has the data, say A2= New York Rangers, B2=NHL, C2= New York, A3=White Sox, B3= MLB, C3= Chicago. All of the values in the A col will be unique. Different files will have different numbers of columns.

I’m hoping to generate a markdown file named New York Rangers like this:

-–
Team:New York Rangers
Sport: NHL
City: New York
-–

Any suggestions would be great. This times out.

tell application “Microsoft Excel”
open theFile
set theSheet to active sheet
set theRows to used range’s rows
set theHeaderRow to item 1 of theRows
set theDataRows to rest of theRows
set theHeaderKeys to value of theHeaderRow

repeat with aRow in theDataRows
	set theFileName to value of cell "A" of aRow as text
	set theFileName to theFileName & ".md"

	set theFilePath to (path to desktop as text) & theFileName
	set theFileRef to open for access theFilePath with write permission

	set theLine to ""
	repeat with i from 1 to count of theHeaderKeys
		set theHeaderKey to item i of theHeaderKeys
		set theValue to value of cell i of aRow as text
		set theLine to theLine & theHeaderKey & ": " & theValue & linefeed
	end repeat

	write theLine to theFileRef as «class utf8»
	close access theFileRef
end repeat

end tell

#2

Where does it time out?

BTW, if you put three back ticks (like this: ``` ) immediately above and below your code, it will all be formatted correctly.

#3

Thanks for the formatting tip. I’m not sure where it times out, that was the message.

#4 
		set theFile to "Macintosh HD:Users:useraccount:Desktop:Book1.xlsx"
		
		tell application "Microsoft Excel"
			open theFile
			set theSheet to active sheet
			set theRows to used range's rows of theSheet
			set theHeaderRow to item 1 of theRows
			set theDataRows to rest of theRows
			set theHeaderKeys to value of theHeaderRow
			
			repeat with aRow in theDataRows
				set theFileName to value of cell 1 of aRow as text
				set theFileName to theFileName & ".md"
				
				set theFilePath to (path to desktop as text) & theFileName
				set theFileRef to open for access theFilePath with write permission
				
				set theLine to ""
				repeat with i from 1 to count of theHeaderKeys
					set theHeaderKey to item i of theHeaderKeys
					set theValue to value of cell i of aRow as text
					set theLine to theLine & theHeaderKey & ": " & theValue & linefeed
				end repeat
				
				write theLine to theFileRef as «class utf8»
				close access theFileRef
			end repeat
		end tell

It was timing out because you had not specified to get the “used range” of anything. In other words, Excel didn’t know what the used range was a part of. In my example, you’ll see I added “of theSheet”

You’ll also notice that I changed…

set theFileName to value of cell "A" of aRow as text

to

set theFileName to value of cell 1 of aRow as text

NOTE: I thought it would be important to let you know that Excel works a little differently than other applications. When you say “set theSheet to active sheet”, it will work but if you for any reason interact with Excel while it’s running and a different sheet becomes “active”, Excel will now point to the new active sheet with that variable. It’s safer to set your variable “theSheet” using “sheet 1” or the sheet name. It will just help prevent potential issues.