automating Excel

[Mark Wieder]

Ken-

Saturday, September 13, 2008, 9:20:51 PM, you wrote:

Thank you. I may have to have this framed.

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

You're right - a one-time import was what I had in mind.

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

Well, my experience with csv files in databases is just the opposite,
getting hugely confused with items such as "2140 Shattuck, Suite 2420"
or "Sammy Davis, Jr". But that's a different subject and I don't have
much experience with the way Excel handles csv files. I'm rarely
surprised when Microsoft apps get confused...

> 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

Interesting. That's not at all the VBA command I got. Possibly this is
due to different versions (2003 vs 2007).

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

I could swear I tried that while experimenting but the Add method
without parameters gave me errors.

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

I've got the Scripting Guide, but it didn't help that much when I
needed to look at the internals of Excel. Thanks again for all this.

-- 
-Mark Wieder
 mwieder at ahsoftware.net