I'm trying to open all files in a folder and append the contents into an Excel file. Every file is a text file and I can use the text, and paragraph attributes and loop through to append them to a cell. Then I use a few if statements to delete headers and end of the text lines. Then I use text item delimiters to get specific information to cells.
The problem is, I sometimes get different errors; one of the computers I tried outright gives "TextEdit can't get text of document 1" while one of the test computers works after the second try, and another computer sometimes gives "variable fileContent is not defined" I have no idea why I receive this error, or what causes it.
I thought it might be because of a desync between the script and the files, so instead of using it on the server, I got them on my desktop. No cigar. I tried adding a delay of one second, but still, it sometimes works, and sometimes does not.
Further info: File count varies between 15 to 40. The Excel spreadsheet is empty at first. Unsure if I can or should share example files, but you can find them here: https://wetransfer.com/downloads/7c79b740c876564c740fcb16c345716b20230317182515/5c4114
Here's the code:
set errorMsg to "Unless you canceled the app, starting from item one, please try the following steps:" & return & return & "Retry running the app" & return & return & "If restarting the app does not work: Quit TextEdit and Excel apps, and restart both of them" & return & return & "If the two above steps don't work: Use fewer files" & return & return & "If none of the above work: Continue manually"
tell application "Finder" to set myFiles to every file of (choose folder)
tell application "Microsoft Excel" to set thisWorkbook to make new workbook
tell application "TextEdit"
set thisFile to 1
repeat with theFile from 1 to count of items in myFiles
set theFile to item thisFile of myFiles
try
open theFile
delay 1
set fileContent to text of document 1
on error
display dialog errorMsg
end try
set currentParagraph to 1
set reverseRow to 1
tell application "Microsoft Excel"
set lastRow to (first row index of (get end (last cell of column 3 of active sheet) direction toward the top))
set currentRow to lastRow + 1
repeat with currentParagraph from 1 to count of paragraphs of fileContent
set value of cell ("C" & currentRow) to paragraph currentParagraph of fileContent
set value of cell ("A" & currentRow) to second word of fileContent
set currentParagraph to currentParagraph + 1
set currentRow to currentRow + 1
end repeat
--DELETES END OF TX FILLER PROGRAM AND HEADER ROW WITH WEEKDAY/DATE
--DATES WILL BE ADDED LATER
repeat with reverseRow from ((count of rows in used range of active sheet) + 1) to 1 by -1
if string value of cell ("C" & reverseRow) contains "PONIEDZIAŁEK" or string value of cell ("C" & reverseRow) contains "WTOREK" or string value of cell ("C" & reverseRow) contains "ŚRODA" or string value of cell ("C" & reverseRow) contains "CZWARTEK" or string value of cell ("C" & reverseRow) contains "PIĄTEK" or string value of cell ("C" & reverseRow) contains "SOBOTA" or string value of cell ("C" & reverseRow) contains "NIEDZIELA" or string value of cell ("C" & reverseRow) contains "06:00 Zakończenie dnia" then
delete row reverseRow
--display dialog "deleted row " & reverseRow
end if
end repeat
end tell
tell document 1 to close
set thisFile to thisFile + 1
end repeat
tell application "Microsoft Excel"
set currentRow to 2
set lastRow to (first row index of (get end (last cell of column 3 of active sheet) direction toward the top))
repeat with currentRow from 2 to lastRow
set theText to string value of cell ("C" & currentRow)
set {oldDelims, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ";"}
set theColumns to text items of theText
set value of cell ("J" & currentRow) to item 1 of theColumns
set theText to string value of cell ("J" & currentRow)
set {oldDelims, AppleScript's text item delimiters} to {AppleScript's text item delimiters, "-"}
set theColumns to text items of theText
set value of cell ("K" & currentRow) to item 1 of theColumns
set AppleScript's text item delimiters to oldDelims
set theText to string value of cell ("K" & currentRow)
set charCount to get count of characters of theText
set theChars to text 1 thru 5 of theText
set value of cell ("B" & currentRow) to theChars
set value of cell ("N" & currentRow) to "=REPLACE(" & "K" & currentRow & ",1,5, " & quote & space & quote & ")"
set value of cell ("C" & currentRow) to get value of cell ("N" & currentRow)
set value of cell ("C" & currentRow) to value of cell ("K" & currentRow)
set currentRow to currentRow + 1
end repeat
set value of range "J:J" to ""
set value of range "K:K" to ""
set value of range "N:N" to ""
end tell
end tell
display dialog "Done!"
Edit:
Per Mockman's suggestions, I've altered the code. I'm afraid it's still giving the same error: the variable fileContent is not defined.
I've changed the delay from 0.4 to 1, didn't do anything. I've changed it to 2, now it's back again to 'sometimes works, sometimes doesn't' state.
Here's the updated code:
set errorMsg to "Unless you canceled the app, starting from item one, please try the following steps:" & return & return & "Retry running the app" & return & return & "If restarting the app does not work: Quit TextEdit and Excel apps, and restart both of them" & return & return & "If the two above steps don't work: Use fewer files" & return & return & "If none of the above work: Continue manually"
tell application "Finder" to set myFiles to every file of (choose folder)
tell application "Microsoft Excel" to set thisWorkbook to make new workbook
delay 0.4
tell application "TextEdit"
--set thisFile to 1
repeat with theFile in myFiles
--set theFile to item thisFile of myFiles
try
open theFile
delay 2
set fileContent to text of document 1
(*
on error
display dialog errorMsg
*)
end try
set currentParagraph to 1
set reverseRow to 1
tell application "Microsoft Excel"
set lastRow to (first row index of (get end (last cell of column 3 of active sheet) direction toward the top))
set currentRow to lastRow + 1
repeat with currentParagraph from 1 to count of paragraphs of fileContent
set value of cell ("C" & currentRow) to paragraph currentParagraph of fileContent
set value of cell ("A" & currentRow) to second word of fileContent
set currentParagraph to currentParagraph + 1
set currentRow to currentRow + 1
end repeat
--DELETES END OF TX FILLER PROGRAM AND HEADER ROW WITH WEEKDAY/DATE
--DATES WILL BE ADDED LATER
repeat with reverseRow from ((count of rows in used range of active sheet) + 1) to 1 by -1
if string value of cell ("C" & reverseRow) contains "PONIEDZIAŁEK" or string value of cell ("C" & reverseRow) contains "WTOREK" or string value of cell ("C" & reverseRow) contains "ŚRODA" or string value of cell ("C" & reverseRow) contains "CZWARTEK" or string value of cell ("C" & reverseRow) contains "PIĄTEK" or string value of cell ("C" & reverseRow) contains "SOBOTA" or string value of cell ("C" & reverseRow) contains "NIEDZIELA" or string value of cell ("C" & reverseRow) contains "06:00 Zakończenie dnia" then
delete row reverseRow
--display dialog "deleted row " & reverseRow
end if
end repeat
end tell
tell document 1 to close
--set thisFile to thisFile + 1
end repeat
tell application "Microsoft Excel"
set currentRow to 2
set lastRow to (first row index of (get end (last cell of column 3 of active sheet) direction toward the top))
repeat with currentRow from 2 to lastRow
set theText to string value of cell ("C" & currentRow)
set {oldDelims, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ";"}
set theColumns to text items of theText
set value of cell ("J" & currentRow) to item 1 of theColumns
set theText to string value of cell ("J" & currentRow)
set {oldDelims, AppleScript's text item delimiters} to {AppleScript's text item delimiters, "-"}
set theColumns to text items of theText
set value of cell ("K" & currentRow) to item 1 of theColumns
set AppleScript's text item delimiters to oldDelims
set theText to string value of cell ("K" & currentRow)
set charCount to get count of characters of theText
set theChars to text 1 thru 5 of theText
set value of cell ("B" & currentRow) to theChars
set value of cell ("N" & currentRow) to "=REPLACE(" & "K" & currentRow & ",1,5, " & quote & space & quote & ")"
set value of cell ("C" & currentRow) to get value of cell ("N" & currentRow)
set value of cell ("C" & currentRow) to value of cell ("K" & currentRow)
set currentRow to currentRow + 1
end repeat
set value of range "J:J" to ""
set value of range "K:K" to ""
set value of range "N:N" to ""
end tell
end tell
display dialog "Done!"
Try this:
Change the lines between 'tell textedit' to and 'on error' to this:
And delete the last line of the first repeat loop:
I don't know what your data or spreadsheet look like so I won't comment on them but when I ran the script with some generic text, it seemed to work. At least it moved some text around and completed successfully.
You may not need the
delay 0.4here but you can test that out. If there is a place for a delay, I would look at placing one immediately after the line in which Excel creates a new workbook — that's probably the most labour-intensive part for the computer.As for the loop comment… since you are working directly with the list content and not trying to match items in separate lists, you can use
repeat with x in ywithybeing the list andxbeing each item in the list.Using
repeat with x from 1 to count of yreally only adds value when you want to get matching items from multiple lists, e.g.set xx to item x of list1 & item x of list2. I'm oversimplifying a bit but hopefully making sense.The confusion part is that the script used
theFileandthisFilefor essentially the same thing — both are counting cycles through the loop. However, you also usetheFileas a file specifier in this line:set theFile to item thisFile of myFiles.Also, the likely reason that you got different error messages is that the try statement obscures things. In this case, the script probably failed to open a text file and thus fileContent wasn't set, but since it was inside a try block, it didn't come to light until a later line attempted to use fileContent.