Import .xls using variables in script

[Chris1961]

Hi

Sorry, new here so please forgive me if I am in the wrong area!

I have a file which relies totally on externally provided data. I cannot link directly to the data and have to download the individual .xls files manually from the internet and I store them in a child-folder called "Lincs" in the My Documents folder using a direct path name.

Problem is, this will be used by other people in other countries and the route to their My Documents folder is differnet to mine.

To get round this problem I used the following in my script:

Set Error Capture [ On ]

Set Variable [ $file; Value:"filewin:" & Get ( DocumentsPath ) & "namefolder" & settings::globaltextnamefilewithextension ]

Import Records [ Source:“$file”; Method:Update existing; Character Set:“Windows ANSI”] [ No dialog ]I

f [ Get ( LastError ) = 100 ]

Show Custom Dialog [ Title: "Error"; Message: "File Does Not Exist"; Buttons: “OK” ]

End If

This seems to work OK.

Query:

When I set the filepath manually within the script (ie [file:../../../../../Temp/BostonRD.xls]) I click OK, then select 'Sheet 1' of the x.ls file, and then select 'data' as the 'First Row Option'. Then, when I click on 'Specify Import Order', the source fields are displayed which I can match up. I can also select 'Don't Import First Record (contains field names)", and then click OK again. Then click on 'Import Options' and select 'Import'. All works great.

However, when I use a variable to import the file, I am not given the 'Sheet No' option and when I try to specify the import order no source options are displayed. Also I cannot choose to omit the first record containing the field names.

It works in principle, but imports the 'title fields' or a blank record from the .xls file. Also, how does it know which fields to match up?

As I say, it works in principle but I don't understand why and how can I stop it importing the 'title fields' from the .xls file?

Any help would be greatly appreciated.

Chris

[dansmith65]

Using a variable for a path to import records can be a little tricky since FileMaker cant find the file via your variable while you are editing your script. So, while editing the script, set the filepath manually so you can specify the import options, then you can change the path back to the variable path, and it should work.

You can also just specify multiple paths, and leave it like that. To do this, enter this text in the "Specify File" window:

$file

file:../../../../../Temp/BostonRD.xls

[Chris1961]

Hi Dan

Thanks for that. Yes, makes sense when you think about it. As I said, it seemed to work OK, I just couldn't see why!

Chris