automating Excel

[Jim Ault]

On 9/12/08 6:47 PM, "Mark Wieder" <mwieder at ahsoftware.net> wrote:

> 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.
> 
<full quote below>

As an overview of your task, you might think about a different paradigm for
this or other such projects.

Write the working Excel code as VBA (one or a set of functions), then make
the VB call that procedure in the workbook.

I did this a few years ago with Access building a set of output tables in
two Excel worksheets in a workbook where VB simply called a VBA routine
(handler).

It is far easier to build and debug in VBA.  On another project that needed
3D plotting and a 'fly-over' automation routine, I used the INDIRECT()
function in spreadsheet cells to build references using the incoming data
values, especially when they were 10 not known ahead of time and 2) the
number of them would vary.

The better way to handle messaging for VB to VBA or reverse is to just
write/read tiny text files.  One example is passing the data file name to
Excel VBA using a specific text file, like 'dataFilenameForXLToOpen.txt' and
the VBA routine uses that name.

Another example is detecting success or completion; another is reading the
result of the Excel manipulations that have been writen by VBA.

Hope this helps someone.

Jim Ault
Las Vegas


On 9/12/08 6:47 PM, "Mark Wieder" <mwieder at ahsoftware.net> wrote:

> 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