Excel VBA In ActiveX Help 
Author Message
 Excel VBA In ActiveX Help

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



Mon, 28 Feb 2005 22:06:52 GMT
 Excel VBA In ActiveX Help

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



Tue, 01 Mar 2005 02:47:39 GMT
 Excel VBA In ActiveX Help
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

Quote:
>-----Original Message-----
>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
Quote:
>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
Quote:
>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
Quote:
>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,
Quote:
>ConsecutiveDelimiter:=True, Tab:=False,

Semicolon:=False, Comma:=False,
Quote:
>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

>.



Tue, 01 Mar 2005 03:40:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Please Help - VBA DAO in EXCEL 7.0

2. Please help - VBA DAO for Excel 7.0

3. Need Help Manipulating Excel 2000 from Access 2000 using VBA

4. Help with Shell command from VBA in Excel

5. Need Help with VBA reading Excel using ADO

6. Controlling ADP from Excel with VBA

7. Mixing Excel, VBA and SQL server

8. DTS / VBA Excel

9. How to save a Excel worksheet interactively to SQL from VBA/DTS

10. Connecting to an SQL ODBC source from Excel VBA

11. MDX vs. Excel PivotTable API VBA


 
Powered by phpBB® Forum Software