Using excel through vbscript

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!!!

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

"ashtom1" wrote:

> 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!!!

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

"Hayeso" wrote:

> 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
>
> "ashtom1" wrote:
>
> > 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!!!

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: ,,,,,,,, _


ashtom1 wrote:
>
> 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
>
> "Hayeso" wrote:
>
> > 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
> >
> > "ashtom1" wrote:
> >
> > > 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!!!

--

Dave Peterson

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" wrote:

> 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: ,,,,,,,, _
>
>
> ashtom1 wrote:
> >
> > 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
> >
> > "Hayeso" wrote:
> >
> > > 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
> > >
> > > "ashtom1" wrote:
> > >
> > > > 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!!!
>
> --
>
> 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.



ashtom1 wrote:
>
> 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" wrote:
>
> > 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: ,,,,,,,, _
> >
> >
> > ashtom1 wrote:
> > >
> > > 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
> > >
> > > "Hayeso" wrote:
> > >
> > > > 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
> > > >
> > > > "ashtom1" wrote:
> > > >
> > > > > 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!!!
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

"Dave Peterson" wrote:

> 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.
>
>
>
> ashtom1 wrote:
> >
> > 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" wrote:
> >
> > > 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: ,,,,,,,, _
> > >
> > >
> > > ashtom1 wrote:
> > > >
> > > > 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
> > > >
> > > > "Hayeso" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > "ashtom1" wrote:
> > > > >
> > > > > > 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!!!
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>