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