Topics   All   MacOS (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Deprecated   Guides   Examples   Videos
New in version: 9.1   9.2   9.3   9.4   9.5   10.0   10.1   10.2   10.3   10.4    Statistic  

XL.LoadBook

Loads a Excel file.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
XL 2.9 Yes Yes Yes Yes Yes
MBS( "XL.LoadBook"; Data { ; Type } )   More

Parameters

Parameter Description Example value
Data Either text with file path or container value which contains the Excel file data. "C:\test\test.xls"
Type Optional
Which type of file this is.
Pass 0 for unknown, 1 to try xml format (xlsx) and 2 to try binary format (xls).
0

Result

Returns book reference number on success.

Description

Loads a Excel file.
Works with both XML based xlsx files and older xls files.
Book reference numbers are starting at 19000 and counting up for each new book.

This function requires a native path. Use Path.FileMakerPathToNativePath to convert a FileMaker path to a native path if required. If you like to have the user choose the path, you can use FileDialog functions.
For Server be aware that server has limited permissions and may not be able to access all files on a computer.

Examples

Load file from container with auto detect:

Set Variable [ $book; Value: MBS( "XL.LoadBook"; MyTable::myFileContainer; 0 ) ]

Preprocess file before import in FileMaker by removing a row and a sheet:

# Find libXL and load it
If [ MBS("XL.IsInitialized") ≠ 1 ]
    Perform Script [ Specified: From list ; “InitXL” ; Parameter: ]
End If
#
# path to xls or xlsx file
Set Variable [ $path ; Value: MBS( "Path.AddPathComponent"; MBS( "Folders.UserDesktop" ); "test.xls" ) ]
#
# Load template excel file
Set Variable [ $bookRef ; Value: MBS( "XL.LoadBook"; $path) ]
If [ MBS("IsError") ]
    Show Custom Dialog [ "Error" ; $bookRef ]
    Exit Script [ Text Result: ]
End If
#
# Remove first row of first sheet
Set Variable [ $r ; Value: MBS( "XL.Sheet.RemoveRow"; $BookRef; 0 /* sheet index */; 0 /* row index */) ]
#
# Remove second sheet
Set Variable [ $r ; Value: MBS( "XL.Book.DeleteSheet"; $bookRef; 1 /* sheet index */ ) ]
#
# Save to file and cleanup
Set Field [ XL::OutputFile ; MBS("XL.Book.SaveToFile"; $bookRef; $path) ]
Set Variable [ $r ; Value: MBS( "XL.Book.Release"; $bookRef) ]
#
# now let FileMaker import file

See also

Example Databases

Blog Entries

Release notes

Created 18th August 2014, last changed 13th February 2020


XL.IsInitialized   -   XL.LoadBookPartially

Feedback: Report problem or ask question.




Links
MBS FileMaker tutorial videos