automating Excel

Ken Ray kray at sonsothunder.com
Sun Sep 14 00:20:51 EDT 2008


> 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.

What your code has done is to create a data link between Excel and the text
file so that whenever the text file is changed, you can switch to that
workbook and do a "refresh" and it will retrieve the then-current data from
the file. Was this what you wanted to do? Or did you just want to open a
tab-delimited text file in Excel as a new document without the data link?

If it's just opening it as a document, you can use either the "Open" method
(for comma-delimited data):

  Set objXL = CreateObject("Excel.Application")
  objXL.Visible = True
  objXL.Workbooks.Open "C:\MyFolder\MyFile.csv"

or the "OpenText" method (for tab-delimited data):

  Set objXL = CreateObject("Excel.Application")
  objXL.Visible = True
  objXL.Workbooks.OpenText "C:\MyFolder\MyFile.txt",437,1,1,1,False,True

(BTW, it is usually best to have *comma*-delimited data in .csv files, and
*tab*-delimited data in .txt files (Excel can get confused sometimes with
tab-delimited data in .csv files).)

> 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.

OK, let me show you how I work with stuff like this, which might help in
tracking this stuff down...

Looking at the example for OpenText, how did I know the "437", or the "1"s,
or the False/Trues? I did the same thing as Mark (recorded a simple macro),
and when you go to edit the macro, the VBA command is actually this (VB uses
"_" as a continuation char like we use "\"):

  Workbooks.OpenText Filename:="C:\MyFolder\MyFile.txt", Origin:=437, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,1), _
    Array(2,1), Array(3,1)), TrailingMinusNumbers:=True

If you try to paste this into a VBScript file, it won't work because
VBScript can't use (AFAIK) the <ParamName:=Value> construct. Also, any value
that is not a recognizable type (i.e. integer, boolean, etc,) and starts
with letters that correspond to the application itself (in this case "xl")
are almost always constants. The VBScript will choke on the constants
"xlDelimited" and "xlDoubleQuote" because although these are known to Excel
when running a macro, they are unknown to VBScript when it tries to execute
the script since they aren't defined anywhere in the script file.

So to determine just what we *can* do, you need to look at the Object
Browser, which is lets you see the VBA Object Model for Excel (and other
currently running relevant apps). To do this, you go into the VB script
editor (either by going to Tools > Macro > Macros.. (or typing Alt+F8) and
find your macro you recorded and edit it, or just type Alt-F11 to open the
Script Editor to the last script you were editing). Choose View > Object
Browser (or type F2) and when it comes up, choose "Excel" from the popup
menu at the top - this will limit what you see in the Object Browser to just
what Excel provides.

The first thing to check out is the actual syntax for the Workbooks.OpenText
command, so choose "Workbooks" from the Classes list, and "OpenText" from
the "Members of 'Workbooks'" list. This shows you the syntax:

  Sub OpenText(Filename As String, [Origin], [StartRow], [DataType],
    [TextQualifier As XLTextQualifier = xlTextQualifierDoubleQuote],
    [ConsecutiveDelimiter], [Tab], [Semicolon], [Comma], [Space],
    [Other], [OtherChar], [FieldInfo], [TextVisualLayout],
    [DecimalSeparator], [ThousandsSeparator], [TrailingMinusNumbers],
    [Local])

As you can see, everything but the Filename parameter is optional, and since
you can't use the <ParamName:=Value> construct, you'll have to provide
values for all params up to the last one your care about (so long as the
defaults for the other params work out).

OK, so that means we have to provide this syntax:

  Sub OpenText(Filename As String, [Origin], [StartRow], [DataType],
    [TextQualifier As XLTextQualifier = xlTextQualifierDoubleQuote],
    [ConsecutiveDelimiter], [Tab])

The Origin ("437") and StartRow ("1"), can be extracted from the macro, but
what about the DataType? The macro shows it as "xlDelimited". Even if you
knew this was a constant, you don't know what the value of it should be. So
you need to look it up. To do this, you can enter a value in the the
*second* popup menu at the top of the Object Browser window, to the left of
the binoculars button. This is the "Find" field, so if you type
"xlDelimited" and click the binoculars, you get a search results list that
shows:

   Library     Class                           Member
   Excel        XLTextParsingType    xlDelimited

If you click on the line in the search results, at the bottom of the Object
Browser it shows:

  Const xlDelimited = 1
     Member of Excel.XLTestParsingType

OK... so the value for "xlDelimited" is "1". Now to find the value for the
TextQualifier, which is "xlDoubleQuote" according to the macro. Perform the
same search, but with "xlDoubleQuote" as your search string, and you'll get
one hit, which when clicked on shows this at the bottom of the Object
Browser:

  Const xlDoubleQuote = 1
     Member of Excel.Constants
  
All the rest of the parameters have fixed values that can be extracted from
the macro, just like Origin and StartRow.

> 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. 

Actually, it *does* work, but you either need to reference a currently
running instance of Excel through using "GetObject" instead of
"CreateObject":

  Set objXL = GetObject(,"Excel.Application")
  MsgBox objXL.ActiveSheet.Name

(and "yes", the comma in front of "Excel.Application" MUST be there)

or as a property of a workbook in a newly opened instance of Excel (which is
what "CreateObject"

  Set objXL = CreateObject("Excel.Application")
  objXL.Visible = True
  Dim wb
  Set wb = objXL.Workbooks.Add
  MsgBox wb.ActiveSheet.Name

My recommendation is to review the Excel Object Model, which is usually in
the Excel help system, or if not, you can use this page to tell you where to
look: http://support.microsoft.com/kb/222101, or downloading a specific
language reference at:
http://www.microsoft.com/technet/scriptcenter/topics/office/vba.mspx.

In addition to that, *I* use a couple of VBA books I got at Borders, one for
"VBA for the 2007 Microsoft Office System" and the "Microsoft Windows 2000
Scripting Guide".

HTH, 

Ken Ray
Sons of Thunder Software, Inc.
Email: kray at sonsothunder.com
Web Site: http://www.sonsothunder.com/





More information about the use-livecode mailing list