Re: Using excel through vbscript

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