[Excel] Open text file, copy values into template, save template.

Scripting Forums AppleScript | Mac OS X
,
#1

Over the last week, I’ve tried macros, VB, watched Automator tutorials (only to learn that my system didn’t have the necessary Excel modules because I’m using v.X), researched Spreadsheet::WriteExcel and Spreadsheet::ParseExcel (Perl modules, since I’ve learned Perl over the last year)… Before I try another mechanism, I thought I’d throw myself on the mercy of a forum of experienced individuals to find out if what I need to do is possible before failing miserably because I strayed outside the realm of possibility.

Scenario: I have thousands of text files of data: ruthless, ruthless data. I have an Excel template that can process said data painlessly and (relatively) quickly. The problem is that I can’t get from one to the other because (A) methods that can save an Excel spreadsheet based on the original file name (it’s also part of the text file) can’t seem to handle the originating text and (B) methods that can handle the originating text can’t seem to make proper spreadsheet files. I wrote the Perl scripts that generated the output, so they’re about as user-friendly as possible: tab delimited, identically formatted, with all the settings the spreadsheet needs perfectly placed.

What I require, if someone could point me in the right direction is something that would let me:
-Open a text file (for example, “data00001.txt”)
-Paste values into Excel (sheet “Input”, cell “A11” or whatever, I made my template, I can handle adjusting it as long as the data gets in there somewhere)
-Save the file as data00001.xls or data00001.xls.txt… Or “nonesensestringofletters1”, as long as their each unique so I’m not overwriting my data.

Actually, I’ve already written (in one of my failed attempts) a macro for the last two parts if there is some way of saying
-Open text file (in text edit, preferably since it’s quicker)
-Copy all
-Open Excel template
-Run macro1 (that’d paste the data, calculate and then save the file based on the contents of a cell, quick as a bunny)
-Close Excel template (so I don’t have 4,000 Excel files open on my system)

I’ve done some searches and found bits and pieces, but is this combined effort possible in AppleScript? Can it be automated for all the “.txt” files in a folder?

If someone could aim a poor, tired, going-prematurely-gray graduate student in the general direction, I’d be very appreciative.

Thanks for any help or suggestions (or even, “you can’t do that”, which would save me time in trying another doomed-to-fail direction)!

Model: iMac, but have a Pro desktop for the heavy lifting
AppleScript: 2.0.1
Browser: Firefox 3.0.6
Operating System: Mac OS X (10.5)

#2

What version of Excel are you using? In Excel 2008, in the File menu there is “Import…” which should solve your problem.

#3

Hi,

here an example (Excel 2004), which asks for a .txt file,
launches Excel, makes a new workbook, writes the contents of the text file in cell 11 of column 1
and saves the Excel file on desktop with the same file name as the text file.

In Excel v.X the syntax could be slightly different


set theFile to choose file
set theText to read theFile
set newName to text 1 thru -5 of name of (info for theFile)
tell application "Microsoft Excel"
	activate
	make new workbook
	set value of cell 11 of column 1 to theText
	save active workbook in ((path to desktop as text) & newName & ".xls")
end tell
#4

Gah, I’m so close and I need to go to a meeting. Thank you for the push in the right direction, it got me to where I needed to go to, now I just need to get to where I need to be. I got it to work perfectly for one file if I choose the .txt file and the .xlt file (the data file and the template file).

set theFile to choose file
set notesFile to choose file
tell application "Finder"
	set the clipboard to (read theFile)
	open notesFile
	tell application "Microsoft Excel"
		Activate
		Evaluate "Template.xlt!Macro2()"
		Evaluate "Template.xlt!Macro1()"
	end tell
end tell

Macro2 handles the saving as a file name, Macro1 closes the workbook.

Choose my text file, choose my template, everything’s gravy. The problem I’ve run into now is with making it into a batch process where I choose the template and the folder:

set notesFile to choose file
tell application "Finder"
	set the source_folder to choose folder
	set sourceFiles to every file of source_folder whose name contains ".txt"
	set fileCounter to 1
	repeat with theFile in sourceFiles
		set the clipboard to (read theFile)
		open notesFile
		tell application "Microsoft Excel"
			Activate
			Evaluate "Template.xlt!Macro2()"
			Evaluate "Template.xlt!Macro1()"
		end tell
	end repeat
end tell

I get an “Can’t make document file ‘batchfilename.txt’ of the folder ‘Testbunny’ of folder ‘username’ of folder ‘users’ of startup disk into type file”. If I do it with the other method though, I can open up the file and it runs great.

If anyone could point out my boo-boo, I’d really appreciate it. Thanks!

#5

the variable theFile is a Finder file specifier, but the read command expects a path string (with prefix file) or an alias


set notesFile to choose file
set the source_folder to choose folder
tell application "Finder" to set sourceFiles to every file of source_folder whose name contains ".txt"
set fileCounter to 1
repeat with theFile in sourceFiles
	set the clipboard to (read theFile as alias)
	open notesFile
	tell application "Microsoft Excel"
		activate
		Evaluate "Template.xlt!Macro2()"
		Evaluate "Template.xlt!Macro1()"
	end tell
end repeat

Not: I recommend to avoid nested application tell blocks

#6

I’m still getting:

Finder got an error: Can’t make document file “batch1246.txt” of folder “TestbunnyThree” of folder “username” of folder “Users” of startup disk into type file.

I have the feeling I’m missing something simple and obvious, since I can get the simple script to run with just batch1246 (and it runs perfectly).

#7

sorry, I forgot some parentheses


.
set the clipboard to (read (theFile as alias))
.
#8

I could kiss you. Needed one more change (after AppleScript started yelling at me that it couldn’t open the file, I realized I need to use a “tell Finder” to open notesFile.

For posterity, what seems to be working:

set notesFile to choose file
set the source_folder to choose folder
tell application "Finder" to set sourceFiles to every file of source_folder whose name contains ".txt"
set fileCounter to 1
repeat with theFile in sourceFiles
	set the clipboard to (read (theFile as alias))
	tell application "Finder"
		open notesFile
	end tell
	tell application "Microsoft Excel"
		Activate
		Evaluate "Template.xlt!Macro2()"
		Evaluate "Template.xlt!Macro1()"
	end tell
end repeat

Now to change Template.xlt from a blank sheet to the template I need.

Thank you so much for your assistance, I’m now equally happy that it works and depressed I didn’t think to try this sooner…

#9

sigh

I got this working perfectly at my desk, went to throw it onto the server and found out we had Office 2004 installed there. Everything still works except for the activation of the macro.

run VB macro "Template.xlt!Macro2()"

does absolutely nothing, but if I run the macro from within Excel, the macro works without a hitch. Is there a different way to call Excel macros from within Applescript in 2004?