*** 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!
Great tip – except that it REQUIRES Excel be installed on the machine.
One approach I’ve used successfully for importing is to use the ODBC driver for Excel and grabbing the data from there. Craig Boyd did a quick tool up here – http://www.sweetpotatosoftware.com/spsblog/PermaLink,guid,6b9d4c6f-76bb-4444-8d5b-9e321c605534.aspx
But both solutions work well. Thanks for the tip.
#Thanks for the tip and the link. Maybe when I am done learning automation and RTF controls which where my interest lay as of now, I will start learning ODBC.
Your site is without a doubt full of terrific detail and is rather interesting to look at.
Very well carried out.