Solved

# VB Script to open a delimited text file in Microsoft Excel

Posted on 2013-12-09
4,710 Views
Hello Experts!

I am trying to do exactly what the title says.... Take File.txt which has columns separated/delimited by the character "|" .

When using excel manually, I have to open excel and then browse to File.txt, then choose "Text" as the file type then since I'm opening a text document with Excel the "Text Import Wizard" comes up... I choose 'Delimited' as the 'original data type' , accept all other defaults, then on step 2 of the wizard I select 'Other' as the delimiter and | in the 'Other:' field. The next page I leave default as General, and click finish.

Now my text file data is open in Excel.....

There must a way to automate this!!

cscript ThanksExperts.vbs!!
0
Question by:zequestioner
• 9
• 7
• 5

LVL 1

Author Comment

0

LVL 51

Expert Comment

Here's a small VBS script that should do the job. Save as a VBS file, then run like below from a command line, or just drag the txt file onto the VBS in Explorer.

cscript EE28314307.vbs in.txt

' Define constants
Const xlDelimited = 1

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
strInFile = WScript.Arguments(0)
Else
WScript.Echo "No input filename specified."
WScript.Quit
End If

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists
If Not objFSO.FileExists(strInFile) Then
WScript.Echo "Input file does not exist."
WScript.Quit
End If

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

' Open delimited file
objExcel.Workbooks.OpenText objFSO.GetAbsolutePathName(strInFile),,,xlDelimited,,,,,,,True,"|"

~bp
0

LVL 32

Expert Comment

do you want this to run externally to Excel ?  if so, then BillP's routine is the way to go.
note that the fie.txt is coded into the script.

but if this is to run with Excel already open (or from a master workbook) and then do some further processing, then you want to use VBA, which is slightly different.  Try starting the macro recorder before doing your manual process.
0

LVL 1

Author Comment

ouch, the text output is coming from a java app, which dumps a huge text file w/ the above mentioned 'Column1 |Column2 | Etc.' format.

This is for a scheduled job, so i was hoping I could do this without opening excel, I just want to import the txt data to excel, save the file, then my plan was to automatically email this out via blat. I don't want to touch the data at all if possible.
0

LVL 1

Author Comment

this doesn't seem to be working... at least on my office 2013, i will try office 2010 and can go back to 2007 if needed.... running this script opens excel, but none of the imported data is there... it's just a blank worksheet.

It would also be great to save and close the excel file so I can email it off with an smtp tool.
0

LVL 32

Expert Comment

have to ask... what is the advantage of creating an excel file of just the text data if not doing any processing ?

BillP method seems like the most appropriate as a starting point but I dont know why it would not be loading the data correctly.

myinputfile = objFSO.GetAbsolutePathName(strInFile)
outputxlsfilename = "c:\ee\Test1.xlsx"

wscript.echo "1>>" & myinputfile & "<< "

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True  <<<comment out this when working

' Open delimited file
objExcel.Workbooks.OpenText myinputfile,,,xlDelimited,,,,,,,True,"|"

objExcel.ActiveWorkbook.SaveAs outputxlsfilename, 51

objExcel.ActiveWorkbook.Close False


are you able to supply a test text file with say top 20 lines of real data ?  So we can check that it is processing correctly.
0

LVL 1

Author Comment

My apologies, there was an issue with the source txt file i was testing with. BillP's method works perfectly. The last example however, which should save the file to c:\ee\test1.xlsx throws an error:

1, 1) Microsoft VBScript runtime error: Object required: 'objFSO'

I updated the script to this:

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

myinputfile = myinputfile = objFSO.GetAbsolutePathName(strInFile)
outputxlsfilename = "C:\ee\Test1.xlsx"

wscript.echo "1>>" & myinputfile & "<< "

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")

'objExcel.Visible = True  <<<comment out this when working

' Open delimited file
objExcel.Workbooks.OpenText myinputfile,,,xlDelimited,,,,,,,True,"|"

objExcel.ActiveWorkbook.SaveAs outputxlsfilename, 51

objExcel.ActiveWorkbook.Close False


But now it gives this error:

1>>False<<
C:\ee\convert.vbs(15, 1) Microsoft Excel: OpenText method of
Workbooks class failed
0

LVL 51

Expert Comment

It worked under 2010 here, at least on a test file I built.  Let me know what you find out relative to 2013, I haven't made the jump there yet.

Yes, we can save the file.

~bp
0

LVL 1

Author Comment

same with office 2010, are you x86 or x64?
0

LVL 51

Expert Comment

I'm x86.

~bp
0

LVL 51

Expert Comment

Give this a try.

' Define constants
Const xlDelimited = 1

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
strInFile = WScript.Arguments(0)
Else
WScript.Echo "No input filename specified."
WScript.Quit
End If

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists
If Not objFSO.FileExists(strInFile) Then
WScript.Echo "Input file does not exist."
WScript.Quit
End If

' Build the full paths to the input and output files
strInPath = objFSO.GetAbsolutePathName(strInFile)
strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & GetBaseName(strInPath) & ".xlsx"

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

' Open delimited file
Set objWorkbook = objExcel.Workbooks.OpenText(strInPath,,,xlDelimited,,,,,,,True,"|")

' Save as an Excel file, close Excel
objWorkbook.SaveAs strOutPath, 51
objWorkbook.Close False
objExcel.Quit

~bp
0

LVL 32

Expert Comment

bills script should fix the error.

which was
myinputfile = myinputfile = objFSO.GetAbsolutePathName(strInFile)

should have been
myinputfile = objFSO.GetAbsolutePathName(strInFile)
0

LVL 1

Author Comment

Office 2010/2013 are both generating the same error:

c:\ee\convert3.vbs(23, 1) Microsoft VBScript runtime error: Ty
pe mismatch: 'GetBaseName'
0

LVL 32

Expert Comment

change line 23...

strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & objFSO.GetBaseName(strInPath) & ".xlsx"
0

LVL 51

Expert Comment

Thanks 'rob'.

~bp
0

LVL 1

Author Comment

Weird, in both versions it now opens the excel spreadsheet with the text delimited correctly, but it does not save and close excel. The command window shows this:

Microsoft (R) Windows Script Host Version 5.8

C:\ee\convert3.vbs(30, 1) Microsoft VBScript runtime error: Ob
ject required: 'objExcel.Workbooks.OpenText(...)'

i think that means line 30 is a little off.
0

LVL 51

Accepted Solution

Bill Prew earned 350 total points
Okay, this works for me here now.

' Define constants
Const xlDelimited = 1

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
strInFile = WScript.Arguments(0)
Else
WScript.Echo "No input filename specified."
WScript.Quit
End If

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists
If Not objFSO.FileExists(strInFile) Then
WScript.Echo "Input file does not exist."
WScript.Quit
End If

' Build the full paths to the input and output files
strInPath = objFSO.GetAbsolutePathName(strInFile)
strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & objFSO.GetBaseName(strInPath) & ".xlsx"

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

' Open delimited file
objExcel.Workbooks.OpenText strInPath,,,xlDelimited,,,,,,,True,"|"
Set objWorkbook = objExcel.ActiveWorkBook

' Save as an Excel file, close Excel
objWorkbook.SaveAs strOutPath, 51
objWorkbook.Close False
objExcel.Quit

~bp
0

LVL 32

Assisted Solution

Robberbaron (robr) earned 150 total points
try changing
' Open delimited file
Set objWorkbook = objExcel.Workbooks.OpenText(strInPath,,,xlDelimited,,,,,,,True,"|")

to
' Open delimited file
objExcel.Workbooks.OpenText(strInPath,,,xlDelimited,,,,,,,True,"|")
set objWorkbook = objExcel.Activeworkbook


My reading of MSDN is that OpenText does not return a object (where Open does) which is why i used Activeworkbook when doing the saving.
0

LVL 51

Expert Comment

Great minds thinking alike there?

:-)
0

LVL 1

Author Closing Comment

Thanks guys!!!!!!!!!!! Works perfectly in Excel 2010 as well as 2013!

Thanks again!
0

LVL 1

Author Comment

also for you to knowI  have another open question here:

http://www.experts-exchange.com/Programming/Misc/Q_28316758.html

I know it is not best practice to ask multiple questions on a single thread.. plus you get extra points!
0

## Featured Post

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This is an explanation of a simple data model to help parse a JSON feed
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …