Excel VBA In ActiveX Help

Excel VBA In ActiveX Help

Post by <Brian> » Fri, 13 Sep 2002 23:06:52



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

 
 
 

Excel VBA In ActiveX Help

Post by Allan Mitchel » Sat, 14 Sep 2002 03:47:39


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

 
 
 

Excel VBA In ActiveX Help

Post by Trey Johnso » Sat, 14 Sep 2002 04:40:00


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

>.

 
 
 

1. Please Help - VBA DAO in EXCEL 7.0

Hi;

I am tring to program in Excel 7.0 to retrieve data from an Access 2.0
database.  It seems trivial in the books but when I followed the book
step by step, I got run time error "424": object required.  Could anyone
help me out here?

Thanks

Meng Li

P.S.  Here is the code I was running:

Sub Sec3()
Dim dbs As Database, rst As Recordset, tbl As TableDef
strFilename = Application.GetOpenFilename(filefilter:="Database Files
(*.mdb), *.mdb; ", filterindex:=1)
Set dbs = OpenDatabase(strFilename)
If strFilename = False Then
    Exit Sub
ElseIf Err Then
    MsgBox "Could not open " & strFilename & "as a database file."
    Exit Sub
End If
End Sub

2. SQL SERVER 6.5

3. Please help - VBA DAO for Excel 7.0

4. SQL Server 7 $99 upgrade

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

6. Data warehouse job descriptions

7. Help with Shell command from VBA in Excel

8. Call for Papers: Testing Computer Software Conference (TCS200

9. Need Help with VBA reading Excel using ADO

10. Controlling ADP from Excel with VBA

11. Mixing Excel, VBA and SQL server

12. DTS / VBA Excel