Copy text to Excel file and not to Text file

I have this file that gets the file names from a folder and saves as a text file.

I ned to copy the text into an Excel file and not to text file. Also getting rid of file extensions would be nice.

set sourceFile to (choose file with prompt "Choose Excel file" of type {"XLSX", "XLS", "TXT"} with multiple selections allowed without invisibles)

tell application "Microsoft Excel"
	activate
	open sourceFile
	set wkbk_name to get full name of active workbook
	set theRange to range "A2:A101" of sheet 1 of active workbook
	set bold of font object of theRange to true
end tell

-- Create the text to be written to the file.
-- Just a heading and the item names, indented according to their positions in the hierarchy.
-- (Uncomment the (* *) comment markers to preserve the full paths.)

on createText(posixPaths)
	script o
		property paths : posixPaths
	end script
	
	set rootPath to beginning of o's paths
	set item 1 of o's paths to "Entire contents of " & rootPath & linefeed
	set astid to AppleScript's text item delimiters
	-- (*
	considering case
		set AppleScript's text item delimiters to ""
		set tabStr to {tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab} as text -- Hopefully more than needed!
		
		set AppleScript's text item delimiters to "/"
		set nonIndent to (count rootPath's text items) -- 1
		
		set len to (count posixPaths)
		repeat with i from 2 to len
			set thisPath to item i of o's paths
			set tiCount to (count thisPath's text items)
			set thisName to text item -1 of thisPath
			-- If the item name contains any colons, restore the original slashes.
			if (thisName contains ":") then
				set AppleScript's text item delimiters to ":"
				set thisName to thisName's text items
				set AppleScript's text item delimiters to "/"
				set thisName to thisName as text
			end if
			-- If this is a folder path, append a colon to the name.
			if ((i < len) and (item (i + 1) of o's paths begins with thisPath)) then set thisName to thisName & ":" -- or "/", if preferred.
			-- set item i of o's paths to text 1 thru (tiCount - nonIndent) of tabStr & thisName
		end repeat
		-- *)
		set AppleScript's text item delimiters to linefeed
		set outText to o's paths as text
		-- (*
		set AppleScript's text item delimiters to linefeed & tab
		set outText to outText's text items
		set AppleScript's text item delimiters to linefeed
		set outText to outText as text
	end considering
	-- *)
	set AppleScript's text item delimiters to astid
	
	return outText
end createText

-- Write the text to file as UTF-8.

on writeTextFile(txt, defaultLoc)
	set f to (choose file name with prompt "Save the UTF-8 text listing as." default name (paragraph 1 of txt) & ".txt" default location defaultLoc)
	set fRef to (open for access f with write permission)
	try
		set eof fRef to 0
		write «data rdatEFBBBF» to fRef -- UTF-8 BOM.
		write txt as «class utf8» to fRef
	end try
	close access fRef
	
	display dialog "The listing has been saved in file \"" & f & "\"" buttons {"OK"} default button 1
end writeTextFile

on main()
	set rootFolder to (choose folder with prompt "Choose a folder or disk to catalogue.")
	
	-- List the hierarchy as POSIX paths, omitting any that contain elements beginning with ".".
	set thePaths to paragraphs of (do shell script "find -f " & (quoted form of POSIX path of rootFolder) & " \\! -path \"*/.*\"")
	
	set outText to createText(thePaths)
	writeTextFile(outText, (path to documents folder))
end main

main()

Suggestions. I am searching through these forums.

Thanks in advance, Randy

Hi Randy,

Does this do it?

set theSourceFiles to (choose file with prompt "Choose Excel file" of type {"XLSX", "XLS", "TXT"} with multiple selections allowed without invisibles)

set theFileList to ""

repeat with thisFile in theSourceFiles
	tell (info for thisFile) to set {Nm, Ex} to {name, name extension}
	set BN to text 1 thru ((get offset of "." & Ex in Nm) - 1) of Nm
	set theFileList to theFileList & BN & return
end repeat

set the clipboard to theFileList

tell application "Microsoft Excel"
	set newBook to make new workbook
	paste special (range "A1") what paste values
end tell

Hi TecNik,

to recap . soFar .

Every week or fortnight I have to send items to a factory. I create a folder structure that has a main folder with Factory and batch sent number. This main folder includes 3 subfolders each holding specific file types. To do this I use this script .

try
	set jobName to text returned of (display dialog "Enter factory name:" default answer "")
	set jobNum to text returned of (display dialog "Enter a batch number:" default answer "")
	set tempName to (jobName & " " & jobNum)
	
	set folderpath to POSIX path of (choose folder with prompt "Select client folder")
	
	do shell script "/bin/mkdir -p " & quoted form of (folderpath & "/" & ¬
		tempName & "/" & tempName & " ") & "{Files,jpegs,Renders}"
	
end try

I then would make an Excel file manually, titled to the same factory & batch number. This worksheet would have 3 columns tilesd like the script below.
My last help request was to create this Excel file with the factory & batch number in the title & placed in the correct folder. This I figured out, thanks for questioning my script. The excel portion is .

tell application "Microsoft Excel"
	set newBook to make new workbook
	tell worksheet "Sheet1" of active workbook
		set font style of font object of row 1 to "Bold"
		set horizontal alignment of row 1 to horizontal align center
		set value of range "A1:C1" of active sheet to {"Item", "Qty", "Status"}
		set column width of column 1 to 46 -- characters (columns)
		set column width of range "b:c" to 20 -- characters (columns)
	end tell
	set today to (current date)
	tell page setup object of active sheet
		set page orientation to portrait
		set zoom to false
		set fit to pages wide to 1
		set fit to pages tall to 3
		set left header to (tempName & " " & month of today & " " & day of today & ", " & year of today) as string
		set left footer to "Page &P of &N"
	end tell
	set workbookName to (tempName & ".xlsx") as string
	set destinationPath to folderpath & workbookName
	tell application "Microsoft Excel"
		save active workbook in (folderpath & "/" & ¬
			tempName & "/" & tempName & " ")
	end tell
end tell

Once this Excel and the folders are created, I have to wait until I have the files to add.

I was then making a text file for the files i am sending. This is this, mostly contributed by someone on here a year or s ago.

-- Create the text to be written to the file.
-- Just a heading and the item names, indented according to their positions in the hierarchy.
-- (Uncomment the (* *) comment markers to preserve the full paths.)
on createText(posixPaths)
	script o
		property paths : posixPaths
	end script
	
	set rootPath to beginning of o's paths
	set item 1 of o's paths to "Entire contents of " & rootPath & linefeed
	set astid to AppleScript's text item delimiters
	-- (*
	considering case
		set AppleScript's text item delimiters to "."
		set tabStr to {tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab, tab} as text -- Hopefully more than needed!
		
		set AppleScript's text item delimiters to "/"
		set nonIndent to (count rootPath's text items) -- 1
		
		set len to (count posixPaths)
		repeat with i from 2 to len
			set thisPath to item i of o's paths
			set tiCount to (count thisPath's text items)
			set thisName to text item -1 of thisPath
			-- If the item name contains any colons, restore the original slashes.
			if (thisName contains ":") then
				set AppleScript's text item delimiters to ":"
				set thisName to thisName's text items
				set AppleScript's text item delimiters to "/"
				set thisName to thisName as text
			end if
			-- If this is a folder path, append a colon to the name.
			if ((i < len) and (item (i + 1) of o's paths begins with thisPath)) then set thisName to thisName & ":" -- or "/", if preferred.
			set item i of o's paths to text 1 thru (tiCount - nonIndent) of tabStr & thisName
		end repeat
		-- *)
		set AppleScript's text item delimiters to linefeed
		set outText to o's paths as text
		-- (*
		set AppleScript's text item delimiters to linefeed & tab
		set outText to outText's text items
		set AppleScript's text item delimiters to linefeed
		set outText to outText as text
	end considering
	-- *)
	set AppleScript's text item delimiters to astid
	
	return outText
end createText

-- Write the text to file as UTF-8.
on writeTextFile(txt, defaultLoc)
	set f to (choose file name with prompt "Save the UTF-8 text listing as." default name (paragraph 1 of txt) & ".txt" default location defaultLoc)
	set fRef to (open for access f with write permission)
	try
		set eof fRef to 0
		write «data rdatEFBBBF» to fRef -- UTF-8 BOM.
		write txt as «class utf8» to fRef
	end try
	close access fRef
	
	display dialog "The listing has been saved in file \"" & f & "\"" buttons {"OK"} default button 1
end writeTextFile

on main()
	set rootFolder to (choose folder with prompt "Choose a folder or disk to catalogue.")
	
	-- List the hierarchy as POSIX paths, omitting any that contain elements beginning with ".".
	set thePaths to paragraphs of (do shell script "find -f " & (quoted form of POSIX path of rootFolder) & " \\! -path \"*/.*\"")
	
	set outText to createText(thePaths)
	writeTextFile(outText, (path to documents folder))
end main

main()

Now what I am trying to do is add this text file into the excel sheet already created in this file. If I can create the text from files being sent into the Excel file and bypass the text file creation, Great.

I have just got this far, I can copy a text file info into An Excel worksheet.

set tid to AppleScript's text item delimiters
set falias to (choose file with prompt "Select text file to add to Excel:" without invisibles)
tell application "Microsoft Excel"
	activate
	open text file filename (falias as text) origin Macintosh start row 3 data type delimited with return -- (a line feed)
end tell
set AppleScript's text item delimiters to tid -- whatever they were before - ALWAYS SET THEM BACK!

I am working on getting the text into the created excel file above. If I have to create the text file first, then copy into Excel - possibly then deleting the text file in one go, that would work for me.

Cheers, Randy