Using excel through vbscript

[Guest]

Hi,

I am trying to export a text file into excel using vbscript I am facing some
problem in running OpenText mathod. The code is

Dim oXl, strFile2Open, strFile2Save
'File to open.
strFile2Open = "H:\TEAM Members files\Herbert
Abana\Txts\RentSCMoto0000011.Txt"
'The opened file will be saved with a different extension.
strFile2Save = "H:\TEAM Members files\Herbert
Abana\Txts\RentSCMoto0000011.xls"
'Initialize Excel instance.
Set oXl = WScript.CreateObject("Excel.Application")
'Make Excel visible.
oXl.Visible = True

oxl.Columns("A:A").Select
Call oxl.Workbooks.OpenText( _
strFile2Open ,2 , 1,2 , (Array(Array(0, _
1), Array(6, 1), Array(12, 1), Array(21, 1), Array(31, 1),
Array(39, 1), Array(56, 1), Array _
(63, 1), Array(72, 4), Array(82, 1), Array(83, 1), Array(85, 1),
Array(91, 1), Array(108, 1) _
, Array(118, 1), Array(127, 1), Array(132, 4)))


It is giving me sytax error on OpenText mathod. I have tried all sorts of
things but I am unable to resolve. Can someone help!!!

 

[Guest]

The correct syntax would be

Dim oXl, strFile2Open, strFile2Save
'File to open.
strFile2Open = "H:\TEAM Members files\Herbert
Abana\Txts\RentSCMoto0000011.Txt"
'The opened file will be saved with a different extension.
strFile2Save = "H:\TEAM Members files\Herbert
Abana\Txts\RentSCMoto0000011.xls"
'Initialize Excel instance.
Set oXl = WScript.CreateObject("Excel.Application")
'Make Excel visible.
oXl.Visible = True
oxl.Workbooks.OpenText strFile2Open

 

[Guest]

Hi,
Thanks for the reply but the rest of the part in openText mathod is used to
format the text file into different col.
If I open file the way you told me It simply opens the text file but it does
not format it.
Any help on formatting the file through vbscript would be appriciated.

Thanks once again for replying

 

[Dave Peterson]

I think you have to use all those positional parameters in your script, too.

This worked ok for me...

Dim oXl, strFile2Open, strFile2Save, wkbk

'File to open.
strFile2Open = "H:\TEAM Members files\Herbert Abana\Txts\RentSCMoto0000011.Txt"
'my test file
'strFile2Open = "c:\my documents\excel\book3.txt"

'The opened file will be saved with a different extension.
strFile2Save = "H:\TEAM Members files\Herbert Abana\Txts\RentSCMoto0000011.xls"
'my test file
'strFile2Save = "c:\my documents\excel\book3.xls"

'Initialize Excel instance.
Set oXl = CreateObject("Excel.Application")

'Make Excel visible.
oXl.Visible = True

oXl.Workbooks.OpenText _
strFile2Open, 2, 1, 2, _
,,,,,,,, _
Array(Array(0, 1), Array(6, 1), _
Array(12, 1), Array(21, 1), Array(31, 1), Array(39, 1), _
Array(56, 1), Array(63, 1), Array(72, 4), Array(82, 1), _
Array(83, 1), Array(85, 1), Array(91, 1), Array(108, 1), _
Array(118, 1), Array(127, 1), Array(132, 4))

Set wkbk = oxl.activeworkbook

oxl.displayalerts = false
wkbk.saveas strfile2Save, -4143
oxl.displayalerts = true

wkbk.close false
oxl.quit

Set wkbk = nothing
Set oxl = nothing

===============
If you look at VBA's help for OpenText, you'll see all the things that you have
to keep track of:

From xl2002's help:
expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar,
FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator,
TrailingMinusNumbers, Local)

That's what this did: ,,,,,,,, _

 

[Guest]

Thanks Dave,

It is working perfectly fine now. If you don't mind I want to ask about the
syntax

oXl.Workbooks.OpenText _
strFile2Open, 2, 1, 2, _
,,,,,,,, _
Array(Array(0, 1), Array(6, 1), _
Array(12, 1), Array(21, 1), Array(31, 1), Array(39, 1), _
Array(56, 1), Array(63, 1), Array(72, 4), Array(82, 1), _
Array(83, 1), Array(85, 1), Array(91, 1), Array(108, 1), _
Array(118, 1), Array(127, 1), Array(132, 4))

the only change I can see is addition of ,,,,,, in the params. So can I
ask.. Why ? and What?

 

[Dave Peterson]

You have to match all the parms that .opentext wants to see.

From xl2002's help:
expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier,
ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar,
FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator,
TrailingMinusNumbers, Local)

The first parm is filename (you have it in your code).

But you're missing the parms starting with TextQualifier through OtherChar.

The commas are just separators for the "nothings" for those missing parms.


In other words...

Your array(array())) was the 5th parm--which meant that excel saw that it should
be used for the TextQualifier parm.

 

[Guest]

Thanks a million dave you are a star...
Silly me I should have thought of that.