Offline JavaScript and Basic (OLJ, OLB)
Offline JavaScript and Basic (OLJ, OLB)
Language: JScript and VBScript (Microsoft). JavaScript (w3schools)
Debugger (recommended): Visual Studio for Windows (Express, Desktop, Professional, Premium, or Ultimate)
Debugger (quick install): scd10en.exe (On Windows 8 you can install in compatibility mode)
VBS Tutorial: Scripting: Your First Steps
File System Object: Introduction to the File System Object Model
The SQL Offline - Automate Migraton from Excel to SQL Server (Macros) video shows you how to create the sample macro below.
A complete list of videos can be found here: http://inters.com/videos.
Sample OLB macro file reads Excel worksheets (single XLS or folder) and exports them to SQL Server importfolder.zip.
Offline Basic documents (OLB files) store formulas and macros.
OFFLINE BASIC DOCUMENT
The Offline Basic editor supports supports autocompletion (Intellisense).
To display all formulas click
Full Module View in the document toolbar.
To work with most formulas you do not need to display the OLB document. For example, to create a column formula click Column in the Formula tab.
The formula that you enter into the Script Edit dialog box is automatically saved to an OLB file when you save the OL file.
Formulas are not stored in Offline (OL) files. Instead, they are stored in Offline Basic (OLB) or Offline JavaScript (OLJ) files with a matching name.
For example, the OL file c:\data\sample.ol pairs with c:\data\sample.olb. OLB and OLJ files are automatically saved when a matching OL file is saved.
If you open an OL file and scripting is enabled (both Startup and Enabled are selected in the Formula tab) formulas will run automatically.
Startup is an application setting and Enabled is an OL document setting; Both must be selected for formulas to run automatically.
If an OLB file is loaded with an OL file, it is hidden by default. To display the formula, click Edit Formula (F11) in the Formula tab.
Peer scripts do not load from the Temporary Internet Files directory.
As you type, language context features activate to help you identify language and objects features. Additional context features are available by activating the right-click context menu.
Formulas are functions that are called by the OL document to calculate a cell value. A formula is called with the row and column number of the cell, and returns the cell value.
You can add functions in the Functions dialog box. To display the Functions dialog box, click Functions in the Formula tab.
FUNCTIONS DIALOG BOX
Macros are functions that you call directly. After you create a function, you can assign that function to run as a macro in the Macros dialog box.
To display the Macros dialog box click Macros in the Formula tab. If the Macros button is not enabled, select Enabled in the Script pane.
MACROS DIALOG BOX
In the macros dialog box, specify an existing function in the Function drop-down list and click Add to create a macro.
After the function is added, you can click the function name under Macros in the Formula tab.
You can add a local macro or a global macro in the Macros dialog box. A global macros automatically loads the OLB file that contains the macro.
All formulas are called with row and column parameters (nRow, nCol). If you call a function as a macro, the row and column values are set to (-1, -1). If can be much easier to test and debug formulas when calling them as a macros (for example: break only if nRow equals -1). The Sub button in the Formula ribbon calls the current function as a macro.
The Main procedure and (Declarations) section run at startup.
To add a global variable click (Declarations) the right list box. Click Main to add a startup procedure.
Events are procedures (Sub) that are called by the application. SQL Offline supports three events (Load, Timer, and Exit).
TIMER EVENT
To enable the timer event set App.TimerInterval to the number of milliseconds between events. A value of 1000 causes the Timer event to fire once a second. To disable the event, set App.TimerInterval to zero.
To stop Events without stopping formulas click Pause in the Formula tab. To stop both formulas and events click Stop. Sub calls the current function (for testing).
Script debuggers are supported.
To begin debugging, you can set a breakpoint in the Offline Basic document or the Script Edit dialog box. You can also click Debugger in the Formula tab, and connect to the OLB document. Open Debugger does not launch a debugger if Just-In-Time debugging is disabled or not supported (Express editions do not support JIT debugging). Instead, you can launch the debugger and Attach to Process or Connect to Running Documents.
VISUAL STUDIO SCRIPT DEBUGGER
The Microsoft Script Debugger is supported, but Visual Studio is recommended.
SCRIPT DEBUGGER
If you need to clear a formula breakpoint that was set in the script editor, you can set and clear the breakpoint in the debugger. You can debug formulas, however a formula is called every time the cell displays.
SQL OFFLINE DOM
In addition to formulas and macros which are used internally, SQL Offline supports external access to the SQL Offline DOM.
You can access the SQL Offline DOM from scripting languages and tools including Windows Script Host (VBS), JS (Java Script), Internet Explorer, Visual Studio Tools for Office (VSTO), and Visual Studio.
SQL Offline can edit VBS files. Windows Script Host (WScript) and the File System object (Fso) are supported.
You can reference Offline.exe for DOM type information. Using Visual Basic you can create an application object with:
Dim oxapp As New Offline.Application()
oxapp = CreateObject("Interscape.Offline.Application")
You can open a grid file with:
Dim oxgrid As Offline.GridDoc
oxgrid = oxapp.Documents.Open("C:\Temp\Offline.OL")
You can also open a grid file from the the application:
Dim oxgrid As Offline.GridDoc
About Formulas, Scripting, and DOM - a special comment from the SQL Offline design team: We have tried to make writing formulas in
SQL Offline simple, scalable, and presented with a thin separation between in-memory data and language.
Formulas, macros, recordings, and the command line all share the same language. They also access the same document in absolute (not relative) terms.
A concern that we had with this approach is that cell values are always accessed using row and column (there is no selection).
We hope you find that command line shortcuts, Intellense, and the ability to record quickly make up for this overhead. And hope you find that the scripting and DOM approach for formulas provide you with unique advantages.
