Hi all,

I'm trying to import a text file into Excel, manipulate the text file, and
import it into SQL.  I have a VB program that works fine, but DTS is giving
me a headache.  Every time I try to parse the script I get an error that
reads "Expected Statement at line XX".  I've narrowed the problem down to
how Excel takes parameters.  Excel wants the parameter name, a semicolon, an
equal sign, and then the value (ie. Origin:=xlWindows).  I can get the
script to parse if I remove all of the semicolons but then the Excel methods
fail.  Does anyone have any suggestion on how to get past this?

Here is one of the methods I am having difficulties with:

Wookbooks.opentext Filename:="O:\myfile.txt", Origin:=xlWindows,
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False,
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1),
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8,
1), Array(9, 1))

Thanks

Why the extra step to XL.
Can you not import to SQL Server from the Text file.

Even of you have to go to XL from a text file why use the XL object
model ?

This means that you need to have XL on the same server as SQL Server
--

Allan Mitchell
MCSE, MCDBA
www.SQLDTS.com

The reason you are experiencing problems is the value you
are passing isn't defined.

xlWindows is a constant with a numeric value (i.e.
something like 4)

For your code to work you'll either need to pass the
actual number or declare a bunch of constants

CONST xlWindow =  1
CONST xlDelimitted = 8

Note: I don't know the values for the above constants
these are just example.

Also, Excel doesn't require that you call the object
model with named parameters (i.e. Origin:=).  This is a
nicety allowing for you to change the order of the
parameters.  You could call them in the expected order
without this convention.

An example would be:
Wookbooks.opentext "O:\myfile.txt", xlWindows,.....

HTH,

Trey Johnson
----------------
Principal Architect, Business Intelligence
Encore Development
www.encoredev.com
----------------
Vice President of Marketing
Professional Association for SQL Server (PASS)
www.sqlpass.org
----------------

More SQL Server Education than anywhere else....Pre-
Conference Seminars including one on DTS!

Don't Miss the PASS Community Summit November 19-22!
More new SQL Server sessions by MVPs, Microsoft and the
PASS SQL Server User Community.  

Take part in the conference and community run by Users
for Users.

Visit Us at http://www.sqlpass.org

fine, but DTS is giving
name, a semicolon, an
then the Excel methods
TextQualifier:=xlDoubleQuote,
Semicolon:=False, Comma:=False,