transfering data from .csv to .xls

Hi,

I am using Activex scripts in SQL Server DTS Packages to
read from a .csv file and write it to a Excel file. The
actual problem is the leading zeroes are being truncated
in the XL file.

Hence, I use ActiveSheet.QueryTables.Add method to define
the formating properties that I require. It creates the
Excel file successfully but it neither transfers the data
nor does it format the cells. Below is my code. Pls.
let me know if I am going with the right approach. VERY
URGENT.

If any of you can suggest me an alternate way on how I
can transfer the data from .csv to .xls without loosing
the leading zeroes, I would be really grateful.

with regards,
Ram


Function Main()

Dim Xcl
Dim strDestinationFileName
Dim strSourceFileName

'strDestinationFileName
= "D:\_Clients_Data\Monument\Order\vipul2.xls"
strSourceFileName = DTSGlobalVariables
("Data_File_Path")&DTSGlobalVariables("Data_File_Name")
strDestinationFileName = DTSGlobalVariables
("Schema_File_Path")&DTSGlobalVariables
("Schema_File_Name")


Set Xcl = CreateObject("Excel.Application")
Xcl.Visible = true
Set newBook = Xcl.Workbooks.Add

newBook.Worksheets(1).Name="My First WorkSheet"
newBook.Worksheets(2).delete
newBook.Worksheets(2).delete
newbook.Worksheets(1).Activate


'importing the .csv file
With ActiveSheet.QueryTables.Add
(Connection:= "TEXT; D:\_Clients_Data\Monument\Order\09-
03_Order_Monument.csv", Destination:= Range ("A1") )
.Name = strDestinationFileName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier =
xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1)
.Refresh BackgroundQuery:= False
End With



newBook.SaveAs strDestinationFileName
Xcl.Visible = false

Set Xcl = nothing

Main = DTSTaskExecResult_Success

End Function

Ram
Tue Aug 26 11:53:55 CDT 2003

workbook.ActiveSheet.QueryTables.Add() does not seem to
be working in any VBScript editors.

VB application gives the right desired output for the
above code. Running it as Excel macro also does as
expected.

What is the alternative for the QueryTables.Add() in
VBScript?

I am really running out of time and resources.. someone
kindly help..

with regards,
Ram

Ram
Tue Aug 26 14:20:40 CDT 2003

Hi Tom,

Thanks for the reply. See my first posting. I just use
ActiveSheet.QueryTables.Add(). I also tried using
newbook.ActiveSheet.... and Xcl.workbook.ActiveSheet...

When I put this code in the VBA, I just comment out the
DTS global variables and hard code the source and
destination file name as its just for testing.

Now, When I do the same in VBScript, Windows Script Host
throws a VBScript compilation error; Expected ")" ;
800A03EE. The line number indicates
ActiveSheet.QueryTables.Add() method.

Did I make sense now? or am i still sounding cloudy?

with regards,
Ram


>-----Original Message-----
>Do you mean ...
>
> newbook.ActiveSheet.QueryTables.Add
>
>(per your previous post)?
>
>Or possibly ...
>
> Xcl.workbook.ActiveSheet.QueryTables.Add
>
>Your previous script segment seemed to have an error in
>the With statement ...
>
> With ActiveSheet.QueryTables.Add(...)
>
>Though I can't follow all you are doing here, I would
have
>expected that to throw an 'Object required' error in a
>script (but not in an Excel macro). Is error trapping
>turned off in your actual script. If so, comment out
the
>On Error line. I suspect there is one and its hiding
the
>real problem(s).
>
>Tom Lavedas
>===========
>
>>-----Original Message-----
>>workbook.ActiveSheet.QueryTables.Add() does not seem to
>>be working in any VBScript editors.
>>
>>VB application gives the right desired output for the
>>above code. Running it as Excel macro also does as
>>expected.
>>
>>What is the alternative for the QueryTables.Add() in
>>VBScript?
>>
>>I am really running out of time and resources.. someone
>>kindly help..
>>
>>with regards,
>>Ram
>
>.
>

Tom
Tue Aug 26 16:20:49 CDT 2003

I noticed after I posted that last time that the With
statement used named arguments, which are not supported in
scripting. All of the arguments must be provided
positionally as they are required in the default
definition of the procedure call. For example ...

With newbook.ActiveSheet.QueryTables.Add_
("TEXT; D:\_Clients_Data\Monument\Order\09-" _
& 03_Order_Monument.csv", Range ("A1") )

The error message you describe would be consistent with
this issue since the first colon encountered would
terminate the line early. I would expect the character
count given in the error message to mark this colon.

See if that fixes the With line.

Also remove any other named arguments, such as in the line

.Refresh BackgroundQuery:= False

In addition, the xl constants (xlWindows, xlDelimited,
etc.) are not defined in scripting, unless a <resource>
tag is used in an *.WSF (XML) scripting implementation (I
don't use this, so I can't give you a sample - google for
more info). Therefore, you will need to define them in
CONST statements or insert them as literals if using a
*.VBS script.

Hope that's of some help.

Tom Lavedas
===========

>-----Original Message-----
>Hi Tom,
>
>Thanks for the reply. See my first posting. I just use
>ActiveSheet.QueryTables.Add(). I also tried using
>newbook.ActiveSheet.... and Xcl.workbook.ActiveSheet...
>
>When I put this code in the VBA, I just comment out the
>DTS global variables and hard code the source and
>destination file name as its just for testing.
>
>Now, When I do the same in VBScript, Windows Script Host
>throws a VBScript compilation error; Expected ")" ;
>800A03EE. The line number indicates
>ActiveSheet.QueryTables.Add() method.
>
>Did I make sense now? or am i still sounding cloudy?
>
>with regards,
>Ram
{rest snipped}

Ram
Tue Aug 26 19:11:24 CDT 2003

Thanks Tom,

I finally completed the task.

I moved major part of the script into Excel Macro.
Created a Excel Template. Using some string functions I
derived the source file name.

In the activex script, I created the excel application
and while adding the workbook I passed the created
template as a parameter. Then I executed the Macro in
the workbook and saved it as a xls file.

This works like a charm! Thanks for your immediate
responses.

with regards,
Ram.


>-----Original Message-----
>I noticed after I posted that last time that the With
>statement used named arguments, which are not supported
in
>scripting. All of the arguments must be provided
>positionally as they are required in the default
>definition of the procedure call. For example ...
>
>With newbook.ActiveSheet.QueryTables.Add_
> ("TEXT; D:\_Clients_Data\Monument\Order\09-" _
> & 03_Order_Monument.csv", Range ("A1") )
>
>The error message you describe would be consistent with
>this issue since the first colon encountered would
>terminate the line early. I would expect the character
>count given in the error message to mark this colon.
>
>See if that fixes the With line.
>
>Also remove any other named arguments, such as in the
line
>
> .Refresh BackgroundQuery:= False
>
>In addition, the xl constants (xlWindows, xlDelimited,
>etc.) are not defined in scripting, unless a <resource>
>tag is used in an *.WSF (XML) scripting implementation
(I
>don't use this, so I can't give you a sample - google
for
>more info). Therefore, you will need to define them in
>CONST statements or insert them as literals if using a
>*.VBS script.
>
>Hope that's of some help.
>
>Tom Lavedas
>===========
>
>>-----Original Message-----
>>Hi Tom,
>>
>>Thanks for the reply. See my first posting. I just
use
>>ActiveSheet.QueryTables.Add(). I also tried using
>>newbook.ActiveSheet.... and Xcl.workbook.ActiveSheet...
>>
>>When I put this code in the VBA, I just comment out the
>>DTS global variables and hard code the source and
>>destination file name as its just for testing.
>>
>>Now, When I do the same in VBScript, Windows Script
Host
>>throws a VBScript compilation error; Expected ")" ;
>>800A03EE. The line number indicates
>>ActiveSheet.QueryTables.Add() method.
>>
>>Did I make sense now? or am i still sounding cloudy?
>>
>>with regards,
>>Ram
>{rest snipped}
>.
>