Appending/Importing from Excel 2007′s xlsx via automation

*** i moved to my own blog site so to check on my future posts, please go to www.junblogs.com  ***

most people don't realize that the saveas() event of office automation
has a 2nd parameter, where you can select the format of the file you
are saving.  and so i will be showing how this 2nd parameter can become
very useful to most of us especially in importing and exporting of
office files.  you will also see later how it has been instrumental to
my enhanced rtf tool which i will soon share as well here.

with the change in the format of excel 2007's xlsx, we are finding it now a little bit harder to append or import data from it via the regular way since office 2003.  so i will show you some tricks in achieving that, via automation. 

first, create something like this inside excel 2007, then save it (do not save as):

next create these commands:

create cursor junk (name c(10), age i, address c(20))
local lcfile
lcfile = getfile("xlsx") && get the file you created and saved using excel 2007 format (xslx)
loexcel = createobject("excel.application")
loexcel.workbooks.open(lcfile)
loexcel.activeworkbook.saveas("c:\test.xls",6) && output will be comma delimited
loexcel.activewindow.close(.t.)
loexcel.quit
append from c:\test.xls delimited
browse normal
close databases all

or this:

create cursor junk (name c(10), age i, address c(20))
local lcfile
lcfile = getfile("xlsx")
loexcel = createobject("excel.application")
loexcel.workbooks.open(lcfile)
loexcel.activeworkbook.saveas("c:\test.xls",39)
loexcel.activewindow.close(.t.)
loexcel.quit
append from c:\test.xls type xls && or xl5
browse normal
close databases all

in case of importing data, you can do this:

local lcfile
lcfile = getfile("xlsx")
loexcel = createobject("excel.application")
loexcel.workbooks.open(lcfile)
loexcel.activeworkbook.saveas("c:\test.xls",39)
loexcel.activewindow.close(.t.)
loexcel.quit
import from c:\test.xls type xls && or xl5
browse normal
close databases all

to make it more generic, you can do this:

local lcfile, lctemp lcfile = getfile("xlsx")  lctemp = addbs(getenv("temp"))+sys(3)+".xls" loexcel = createobject("excel.application")loexcel.workbooks.open(lcfile)loexcel.activeworkbook.saveas(lctemp,39)  && place it in the temp folderloexcel.activewindow.close(.t.)loexcel.quitimport from (lctemp) type xls  && or xl5browse normal close databases all

* delete temporary file used
delete file (lctemp)

in that way, you are cleaning your harddisk immediately of the temporary file  used for above importing.

and there you are, easy ways of appending/ importing data from an xlsx format without the need of going inside that excel file and saving it into another format manually.

and here are the possible formats you can save your excel xlsx file into (thanks to cetin basoz for sharing this)

*** constant group: xlfileformat
xladdin                                           18
xlcsv                                             6
xlcsvmac                                          22
xlcsvmsdos                                        24
xlcsvwindows                                      23
xldbf2                                            7
xldbf3                                            8
xldbf4                                            11
xldif                                             9
xlexcel2                                          16
xlexcel2fareast                                   27
xlexcel3                                          29
xlexcel4                                          33
xlexcel5                                          39
xlexcel7                                          39
xlexcel9795                                       43
xlexcel4workbook                                  35
xlintladdin                                       26
xlintlmacro                                       25
xlworkbooknormal                                  -4143
xlsylk                                            2
xltemplate                                        17
xlcurrentplatformtext                             -4158
xltextmac                                         19
xltextmsdos                                       21
xltextprinter                                     36
xltextwindows                                     20
xlwj2wd1                                          14
xlwk1                                             5
xlwk1all                                          31
xlwk1fmt                                          30
xlwk3                                             15
xlwk4                                             38
xlwk3fm3                                          32
xlwks                                             4
xlworks2fareast                                   28
xlwq1                                             34
xlwj3                                             40
xlwj3fj3                                          41
xlunicodetext                                     42
xlhtml                                            44
xlwebarchive                                      45
xlxmlspreadsheet                                  46

enjoy!