automating Excel
Mark Wieder
mwieder at ahsoftware.net
Fri Sep 12 21:47:09 EDT 2008
All-
I had to automate importing a tab-delimited csv file into Excel
yesterday and I went through a lot of trial-and-error (lots of error)
in the process of figuring out enough of Excel's object model and how
to use vbscript. I figured I'd save someone else some effort.
And I've got a question - does anyone have any vbscript resources they
use? IDEs, object model documentation, anything? Microsoft's examples
don't work (I wasted several hours on that) since they rely on being
in a VBA environment for Office automation: "ActiveSheet", for
instance, doesn't seem to exist as an object in vbscript run
externally. And while TextFileTabDelimiter seems to work as a property
of a QueryTable, xlDelimiter doesn't.
----
Here's the calling code (pFile is the path to the csv file as returned
by "answer file"):
/*--------------------------------------------------------
SendToExcel
Launch Excel if possible
the VBScript is stored as a custom property of the stack
--------------------------------------------------------*/
on SendToExcel pFile
local tScript
put the ExcelImport of this stack into tScript
-- put the actual file location into the script
replace "$FILELOCATION" with quote & pFile & quote in tScript
try
do tScript as "VBScript"
catch e
end try
end SendToExcel
Here's the vbscript stored as custom property "ExcelImport" of the
stack:
set objExcel= CreateObject("Excel.Application")
objExcel.Visible = True
dbString= $FILELOCATION
connString = "TEXT;" + dbString
objExcel.Workbooks.Add
Set shCSV = objExcel.Workbooks(1).Worksheets(1)
Set qtCSVData = shCSV.QueryTables.Add(connString,shCSV.Cells(1,1))
with qtCSVData
.TextFileTabDelimiter = True
.Refresh
end with
set objExcel = Nothing
--
-Mark Wieder
mwieder at ahsoftware.net
More information about the use-livecode
mailing list