| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
XL.LoadBook
Loads a Excel file.
| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
| XL | 2.9 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
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
- XL.Sheet.SetRowHidden
- XL.Sheet.SetTabColor
- XL.Sheet.SetTabColorRGB
- XL.Sheet.SetTopLeftView
- XL.Sheet.SetVerPageBreak
- XL.Sheet.SetZoom
- XL.Sheet.SplitInfo
- XL.Sheet.Table
- XL.Sheet.TableSize
- XL.Sheet.Type
Release notes
- Version 14.3
- Fixed a memory leak in loading Excel file from disk with XL.LoadBook function.
- Version 14.2
- Added keepAllSheets parameter for XL.LoadBookPartially function.
- Version 8.0
- Added better error message when calling XL.LoadBook or XL.NewBook without calling XL.Initialize.
- Added XL.LoadBookPartially function.
Example Databases
Blog Entries
- MBS FileMaker Plugin, version 14.3pr1
- MBS Plugin Advent calendar: 13 - XL
- Tips and tricks with MBS
- New column names in an Excel document
- MBS Filemaker Plugin, version 4.4pr8
- MBS Filemaker Plugin, version 4.4pr5
FileMaker Magazin
This function checks for a license.
Created 18th August 2014, last changed 13th February 2020
