Opening a File referenced by variable

Hi all,

I hope someone can help me out here. I am only a part-timer applescripter (maybe one per year or less)

I am attempting to get Excel98 to open a text file for which I do not know the name.

The following code gives me an error.

tell application “Finder”
get name of every file in folder “Unsent” of startup disk
set the list_of_files to result
end tell

repeat with fileName in (list_of_files as list)
if fileName starts with “.” then
else
tell application “Finder”
activate
select file “Microsoft Excel” of folder “Microsoft Office 98” of folder
“Applications” of startup disk
open selection
end tell
set fileNameAndPath to “MacintoshHD:Unsent:” & fileName
set replacementFileName to (a reference to file fileNameAndPath

            tell application "Microsoft Excel"
                Activate
                OpenText replacementFileName Origin xlMacintosh StartAtRow 1 
                DataType xlDelimited TextQualifier xlDoubleQuote FieldInfo {1.0, 1.0} 
                with TabDelimiter without TreatConsecutiveDelimitersAsOne, 
                SemicolonDelimiter, CommaDelimiter, SpaceDelimiter and Other

etc…

The error I get is:

AppleScript Error
file “MacintoshHD:Unsent:WO_299995.xls” of <> doesn’t understand the OpenText message.

If I hard code the the filename like:

OpenText “MacintoshHD:Unsent:WO_299995.xls” Origin xlMacintosh StartAtRow 1 DataType xlDelimited TextQualifier xlDoubleQuote FieldInfo {1.0, 1.0} with TabDelimiter without TreatConsecutiveDelimitersAsOne, SemicolonDelimiter, CommaDelimiter, SpaceDelimiter and Other

it works fine.

If I use a straight character variable like:

set replacementFileName to “MacintoshHD:Unsent:” & fileName

then I get an error stating that Excel could not open ‘’ and that I should check to see if the file was deleted or moved or some such message.

Any ideas?

Thanks!

Mark

You’re using the Finder to open an application. That’s not nessesary, just “tell” the app to do something:

tell application "Microsoft Excel"...

I’m not familiar with Excel’s OpenText command, but I think you simply want to open the found files:


tell application "Finder" to ¬
	set aliasList to (every file of folder "Users" where ¬
		(name of it) does not start with ".") as alias list

tell application "Microsoft Excel"

	repeat with oneFile in aliasList

		set oneFile to oneFile's content -- dereference just in case

		open oneFile

		-- ...excel commands on the open file here...

		close oneFile

	end repeat
end tell

Thank you for the quick reply Arthur!

However, When the following is executed:

tell application “Finder” to ¬
set aliasList to (every file of folder ¬
“Unsent” where (name of it) does not start with “.”) as alias list

I get the following error:

Can’t make alias “MacintoshHD:Unsent:WO_299995.xls” into a «class alst»

I am not sure if excel will accept the Open command with a text file as opposed to the OpenText command. It may pause and ask for user input as to the formatting of the “import” and I cannot let that happen.

The basic function of this excercise is to open a tab delimited text file, insert 3 rows, paste some information above it and have excel save it as a normal excel worksheet. The I pass it off to Eudora to email the file as an attachment.

Again, hardcoding the file name works fine but I must be able to substitute the hard coded path and file names with a variable.

Thanks again. Still hoping for some additional help. I am scouring the AS manual and www frantically trying to get this to work.

Mark

I know why that occurred. I only had one file in the folder. When I put more than one file in the folder it works. There really should never be more than one file in the folder for this process so I left the “list” off the end and it works for one file. It another file gets in there I suppose it would break so I may count the nuber of files before executing the proper code block.

It seems that even when I get a variable to show properly in the result it behaves as though nothing is there. I litterally cut the variable away from the open command and received the same error that Excel could not open ‘’

There has to be a way to do this.

Whoops, you’re on OS 9, (or before). Yeah, this is an annoying little problem. You can use this to ensure you always get a list of aliases, (if you need a list of aliases):


tell application "Finder"
	try
		set aliasList to every file of folder (folder path) as alias list
	on error
		try
			set aliasList to every file of folder (folder path) as alias as list
		on error
			set aliasList to {}
		end try
	end try
end tell
-- aliasList == a list of zero or more aliases

The key thing I was going for was simply to ensure that you didn’t pass a Finder object reference off to the application, ie: Excel isn’t going to know what to do with ‘file “…” of folder “…” of startup disk etc’.

Sorry I don’t know more about Excel scripting, but I haven’t even had a copy of Excel on my machine in over a year or so.

Umm, I meant ‘(folder path)’ as in ‘put your folder path here’. :slight_smile:

Thanks Arthur!

I talked to the client and he stated that they could use a fixed file name so I am off the hook with this one.

Script is working as needed for now.

Thanks again for all your help!

I learned something and that is what this place is all about!

Mark