how to lmport a text file and save it as an excel file using vb s.

[Guest]

I am trying to import a text file and save it as a normal excel file in VB
Script. I first recorded a macro that has steps to open the text file parse
it into columns and save it into a excel 2003 file. However when I copy and
past the macro with changes necessary for the VB script , vb script either
fails in the save-as step or brings up an interactive diaglog box for saving.
What is the way todo with out bringing up the diaglog box. Below is my simple
code..

Dim oXcel
Set oxcel = CreateObject("excel.Application")
oxcel.workbooks.add
oxcel.workbooks.OpenText "MyFile.txt"
oxcel.Rows("1:1").Select
oxcel.Selection.Font.Bold = True
oxcel.Columns("A:J").Select
oxcel.Range("J1").Activate
oxcel.Selection.Columns.AutoFit

ON Error Resume Next
oxcel.workbooks.SaveAs "MyFile.xls",4
if err.number <> 0 then
wscript.echo "SaveAs error:",err.description
end if

On Error goto 0
oxcel.quit
Set oxcel = Nothing

=====
thanks
Newbie Scriptor

 

[Lonnie M.]

I'm a Newbie too,
but I think you need to run text-to-columns after you open the file.
When import a text file it all gets dumped into the first column.
Then you can format, autofit, and save.

See the examples below (Text-to-columns):
Workbooks.Open FileName:=Fname

'Method I
Set mySheet = ActiveSheet
myShtName = ActiveSheet.Name
R = mySheet.UsedRange.Rows.Count
mySheet.Range(Cells(1, 1), Cells(R, 1)).Select
Application.Dialogs(xlDialogTextToColumns).Show
Columns.AutoFit

'Method II
Application.Dialogs(xlDialogOpen).Show Arg1:=Fname
Set mySheet = ActiveSheet
myShtName = ActiveSheet.Name
R = mySheet.UsedRange.Rows.Count
Columns.AutoFit

See the examples below (Saving file as 'xls'):
In VBA the code for saving the file would look like this, where Fname
is a string; I.E: Fname = ThisWorkbook.Path & Application.PathSeparator
& "MyFile.xls":
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Fname, FileFormat:=xlNormal
Application.DisplayAlerts = True

HTH, Lonnie

 

[Guest]

Lonnie,
Thanks for your reply. I guess your code works only after opening up an
excel instance and importing and selecting the text into a column. However
what I am trying to do is in a batch mode with no manual interaction, create
a new excel file, read into it a text file (which is tab delimited , so
opentext method should automatically be able to handle the parsing into
multiple columns) and then save it as a normal formatted Excel file. My
sample code does correctly upto formatting the imported text. However whne
trying to save it as an Excel file it brings up the "SaveAs" dialog box. I am
running the script using "cscript.exe" not as a macro from within Excel
workbook.

Thanks and hope you can help me further
Newbie scriptor

 

[Lonnie M.]

Newbie,
The following example is one that I used for bringing in seven columns
of data with no wizard interaction, and could easily be looped to do a
batch of text files.
'Batch Method
' The Array(X,X) in field info represents the column and data type
'Where X represents the column, Y represents the data type
' 1 = General; 2 = Text; 3 = Date (MMDDYY)
Workbooks.OpenText FileName:=Fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2),
Array(5, 1), _
Array(6, 3), Array(7, 3))
Columns.AutoFit

Saving the file:
I would think that you would have to turn off display alerts and use
the SaveAs function, passing it the directory and file name. See
example below:

xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName,
FileFormat:=xlNormal
xlApp.DisplayAlerts = True

HTH--Lonnie M.

 

[Lonnie M.]

Newbie,
The following example is one that I used for bringing in seven columns
of data with no wizard interaction, and could easily be looped to do a
batch of text files.
'Batch Method
' The Array(X,Y) in field info represents the column and data type
'Where X represents the column, Y represents the data type
' 1 = General; 2 = Text; 3 = Date (MMDDYY)
Workbooks.OpenText FileName:=Fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2),
Array(5, 1), _
Array(6, 3), Array(7, 3))
Columns.AutoFit

Saving the file:
I would think that you would have to turn off display alerts and use
the SaveAs function, passing it the directory and file name. See
example below:

xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName,
FileFormat:=xlNormal
xlApp.DisplayAlerts = True

HTH-Lonnie M.

 

[Lonnie M.]

(Sorry, if there are multiple posts, the server says that it is not
able to post, and try again)
Newbie,
The following example is one that I used for bringing in seven columns
of data with no wizard interaction, and could easily be looped to do a
batch of text files.
'Batch Method
' The Array(X,Y) in field info represents the column and data type
'Where X represents the column, Y represents the data type
' 1 = General; 2 = Text; 3 = Date (MMDDYY)
Workbooks.OpenText FileName:=Fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2),
Array(5, 1), _
Array(6, 3), Array(7, 3))
Columns.AutoFit

Saving the file:
I would think that you would have to turn off display alerts and use
the SaveAs function, passing it the directory and file name. See
example below:

xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName,
FileFormat:=xlNormal
xlApp.DisplayAlerts = True


The following are examples that I found in other groups; the source's
name precedes the example:
Michael Bednarek

Set myApp = WScript.CreateObject("Excel.Application")
Set myBook = myApp.WorkBooks.Open("d:\path\workbook.xls")
myApp.Run("MyMacro")
myBook.Save ' if you want/need to
Set myBook=nothing
myApp.Quit
Set myApp = Nothing


Anthony Borla

}
function convert(fromFile, fromDelimiter, fromPlatformCRLF,
fromPlatform,
toXLSFile)
{
// Open the desired file using specified conversion filter
Excel.Workbooks.Open(fromFile, 0, true, fromDelimiter, "", "", false,
fromPlatformCRLF,
"", false, false, fromPlatform, false);

// Save as desired file type using specified conversion filter
Excel.ActiveSheet.SaveAs(toXLSFile, xlWorkbookNormal, "", "", false,
false);


// Close Document
Excel.Workbooks.Close();


HTH-Lonnie M.

 

[Lonnie M.]

Newbie,
The following example is one that I used for bringing in seven columns
of data with no wizard interaction, and could easily be looped to do a
batch of text files.
'Batch Method
' The Array(X,Y) in field info represents the column and data type
'Where X represents the column, Y represents the data type
' 1 = General; 2 = Text; 3 = Date (MMDDYY)
Workbooks.OpenText FileName:=Fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2),
Array(5, 1), _
Array(6, 3), Array(7, 3))
Columns.AutoFit

Saving the file:
I would think that you would have to turn off display alerts and use
the SaveAs function, passing it the directory and file name. See
example below:

xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName,
FileFormat:=xlNormal
xlApp.DisplayAlerts = True


The following are VBS examples that I found in other groups; the
source's name precedes the example:

Michael Bednarek

Set myApp = WScript.CreateObject("Excel.Application")
Set myBook = myApp.WorkBooks.Open("d:\path\workbook.xls")
myApp.Run("MyMacro")
myBook.Save ' if you want/need to
Set myBook=nothing
myApp.Quit
Set myApp = Nothing


Anthony Borla

}
function convert(fromFile, fromDelimiter, fromPlatformCRLF,
fromPlatform,
toXLSFile)
{
// Open the desired file using specified conversion filter
Excel.Workbooks.Open(fromFile, 0, true, fromDelimiter, "", "", false,
fromPlatformCRLF,
"", false, false, fromPlatform, false);

// Save as desired file type using specified conversion filter
Excel.ActiveSheet.SaveAs(toXLSFile, xlWorkbookNormal, "", "", false,
false);

// Close Document
Excel.Workbooks.Close();


HTH-Lonnie M.

 

[Lonnie M.]

Newbie,
The following example is one that I used for bringing in seven columns
of data with no wizard interaction, and could easily be looped to do a
batch of text files.
'Batch Method
' The Array(X,Y) in field info represents the column and data type
'Where X represents the column, Y represents the data type
' 1 = General; 2 = Text; 3 = Date (MMDDYY)
Workbooks.OpenText FileName:=Fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2),
Array(5, 1), _
Array(6, 3), Array(7, 3))
Columns.AutoFit

Saving the file:
I would think that you would have to turn off display alerts and use
the SaveAs function, passing it the directory and file name. See
example below:

xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.SaveAs Filename:=FileDirectoryName,
FileFormat:=xlNormal
xlApp.DisplayAlerts = True


The following are VBS examples that I found in other groups; the
source's name precedes the example:

Michael Bednarek

Set myApp = WScript.CreateObject("Excel.Application")
Set myBook = myApp.WorkBooks.Open("d:\path\workbook.xls")
myApp.Run("MyMacro")
myBook.Save ' if you want/need to
Set myBook=nothing
myApp.Quit
Set myApp = Nothing


Anthony Borla

}
function convert(fromFile, fromDelimiter, fromPlatformCRLF,
fromPlatform,
toXLSFile)
{
// Open the desired file using specified conversion filter
Excel.Workbooks.Open(fromFile, 0, true, fromDelimiter, "", "", false,
fromPlatformCRLF,
"", false, false, fromPlatform, false);

// Save as desired file type using specified conversion filter
Excel.ActiveSheet.SaveAs(toXLSFile, xlWorkbookNormal, "", "", false,
false);

// Close Document
Excel.Workbooks.Close();


HTH-Lonnie M.

 

[Lonnie M.]

Hi,
I realize that I forgot to change the 'X' to a 'Y' in my example:

' The Array(X,Y) in field info represents the column and data type
'Where X represents the column, Y represents the data type
Sorry about that--Lonnie M.