Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Using excel through vbscript
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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!!!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 16
Default Using excel through vbscript
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!!!
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Using excel through vbscript
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!!!
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default Using excel through vbscript
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default Using excel through vbscript
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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run VBScript from Excel Amy M Excel Discussion (Misc queries) 4 September 19th 08 09:07 PM
vbscript - export to excel ??? amy_mgfe Excel Programming 0 January 16th 05 01:49 PM
VBScript code behind Excel jjjjj Excel Programming 2 November 23rd 04 10:36 PM
embed excel in ie using vbscript balaji kandan Excel Programming 0 November 22nd 03 09:47 AM
how to kill excel using a vbscript Harald Staff[_4_] Excel Programming 0 July 17th 03 10:02 PM


All times are GMT +1. The time now is 04:44 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement