Monday, December 10, 2007
Tip of the Day: Creating Custom XLS Worksheet/Workbook
Section: Exporting Data, PLUGINs
Chapter: Running R:BASE Your Way!
Platform: R:BASE 7.6 and Turbo V-8 for Windows
Builds: R:BASE 7.6 (7.6.2.31029 or higher)
R:BASE (C/S:I) 7.6 (7.6.2.31029 or higher)
R:BASE V-8 Turbo (8.0.12.31029 or higher)
Exporting R:BASE data as a custom XLS Worksheet/Workbook
is one of the features of R:BASE Gateway Export option.
You can create an XLS Worksheet or XLS Workbook using the
interactive Export Wizard option or a command line.
However, with recent versions of Microsoft Office, such
as Office 2007, if you open the XLS Worksheet or XLS
Workbook created by R:BASE with file extension "xls",
the following warning message is displayed regarding
the invalid file extension type for Office 2007.
"The file you are trying to open, 'filename.XLS', is
in a different format than specified by the file
extension. Verify that the file is not corrupted and
is from a trusted source before opening the file. Do
you want to open the file now? [Yes] [No] [Help]"
Of course, you know that you are opening a valid file.
But, by default, MS Office XLS 2007 is looking for
a file with an extension of ".xlsx", unless you
customize the default installation to read ".xls".
However, if you open the exact same file using prior
versions of MS Office, you'll not be prompted with such
message.
When creating automated custom XLS Worksheets or XLS
Workbooks using the latest versions of R:BASE, how do
you know which version of MS Office your client or
end user is using, so that you can create automated
XLS files with appropriate file extensions?
Did you know that you can use the "RRegistry.RBL" Plugin
to find out the registered default file extension of XLS
on the user's workstation?
Knowing the registered file extension for XLS files on
user's machine, now you can create automated MS XLS
Worksheets or Workbooks with appropriate file extensions.
Here's how:
Example 01 (Create XLS Workbook):
-- Start
SET VAR vCheckExtension TEXT = NULL
PLUGIN RRegistry.RBL 'vCheckExtension|CHECK_KEY|HKCR|.xlsx|'
IF vCheckExtension = 'TRUE' THEN
-- Create XLSX Worksheet for MS Excel 2007
GATEWAY EXPORT XLSW CustomersByState.xlsx SELECT ALL +
FROM Customer ORDER BY Company +
OPTION SHEET_NAME All States|SHEET_INDEX 1
ELSE
GATEWAY EXPORT XLSW CustomersByState.xls SELECT ALL +
FROM Customer ORDER BY Company +
OPTION SHEET_NAME All States|SHEET_INDEX 1
ENDIF
CLEAR VARIABLE vCheckExtension
RETURN
-- End
Example 02 (Create XLS Workbook using Customized Spec File):
-- Start
SET VAR vCheckExtension TEXT = NULL
PLUGIN RRegistry.RBL 'vCheckExtension|CHECK_KEY|HKCR|.xlsx|'
IF vCheckExtension = 'TRUE' THEN
-- Create XLSX Worksheet for MS Excel 2007
GATEWAY EXPORT XLSW Customer.XLSX +
SELECT * FROM Customer ORDER BY Company +
OPTION SPECIFICATION_FILE_NAME Customer.rgw +
|SHEET_NAME Customers +
|ACTION OPENVIEW
ELSE
-- Create XLS Worksheet for older MS Excel
GATEWAY EXPORT XLSW Customer.XLS +
SELECT * FROM Customer ORDER BY Company +
OPTION SPECIFICATION_FILE_NAME Customer.rgw +
|SHEET_NAME Customers +
|ACTION OPENVIEW
ENDIF
CLEAR VARIABLE vCheckExtension
RETURN
-- End
Note:
RRegistry.RBL Plugin is included with R:BASE Plugin Power Pack.
http://www.rbase.com/products/pluginpowerpack/
Enjoy and make sure to have fun!
Very Best R:egards,
Razzak.
P.S.
A sample application to create custom XLS Worksheet/Workbook
is also available on 2007 R:BASE Developers' Conference USB
flash drive. http://www.rbase.com/cp/
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
