Import - Specify Excel Worksheet name

[jkrier]

I'm importing an excel file (Import Records - Specify Data source - file=filewin:/$FileName).

I want to pass the worksheet name into the import process. Is there a way you append the worksheet name in the data source box?

Currently I have to leave the Import dialog on so they can specify the worksheet name since it is not the first worksheet in the excel file.

My app is importing vendor quotes from various folders on our Windows file system.

[bcooney]

My app is importing vendor quotes from various folders on our Windows file system.

I must be missing something--if you are changing source workbooks, how would you hardcode a path?

[jkrier]

Sorry I didn't give enough info.

I am getting the filename by using the Productive Computing's File Manipulator Plugin - It is a nice dialog box prompting for the filename and allowing me to put them in the correct default folder.

External ( "PCFM GetFilePath";$DefaultFolder)

The resulting filename/path is stored in $FileName.

I'm importing from that filename. I can use a variable for the filename import but can't specify the worksheet name.

My users always name the worksheet "Import" so I could hardcode the worksheet name if there was a place to put it.

Any help, direction is appreciated. Thanks.

I don't want to make a mess of this if I don't have to.

Edited September 4, 2007 by Guest

[TimonF]

I ran into the same problem. I can't even think of any work-around in my case. Anybody, please!?

[Fenton]

If you use a plug-in or the built-in scripting tools of your operating system to copy|move|rename a file (usually the same tool can do all 3), then you can take whatever file is selected, move/rename it to what FileMaker expects. Then, as far as FileMaker is concerned, it is the same file.

I'm not sure what would happen if the worksheet was named slightly different or in a different location in the file. Best to standardize this.

Since you're on a Mac you do not really need a plug-in to handle this, as FileMaker can run AppleScript, using its Perform AppleScript script step.

Here's an example copy/move/rename, in plain AppleScript. The below would copy a chosen file to a folder "Files to Import" on the desktop (you have to create this), and rename the file to "Import_File.txt"

set DT to path to desktop as text

set folder_path to DT & "Files to Import"

set import_file_name to "Import_File.txt"

set existing_file to folder_path & import_file_name

tell application "Finder"

set orig_file to choose file

set new_file to duplicate orig_file to folder folder_path with replacing

try

delete file existing_file -- if any

end try

set name of new_file to import_file_name

end tell

[TimonF]

Thanks for your quick reply, Fenton. But i think your example only showed how to copy and rename a file.

We were talking about the incapability to specify a worksheet from an excel file to import when using a script. I also had a look at applescript and now i think i will have to export the worksheets i need to separate excel files (containing one sheet each) and then i'd be able to import them automatically.

I still have a hard time coding this because i'm new to applescript. You seem to know your way around this language quite well, perhaps you could write down a little script that does what i want - if that's no bother. It'd be very appreciated and save me a lot time. I had a sneak peak at the office suite but my efforts to devise a script failed..