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: 10.0   10.1   10.2   10.3   10.4   10.5   11.0   11.1   11.2   11.3    Statistic    FMM    Blog  

XL.LoadBook

Loads a Excel file.

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

Parameters

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

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

Release notes

Example Databases

Blog Entries

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


XL.IsInitialized - XL.LoadBookPartially

Feedback: Report problem or ask question.




Links
MBS Xojo tutorial videos

Start Chat