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

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!!
VB ScriptMicrosoft ExcelProgramming

Last Comment
zequestioner

8/22/2022 - Mon
zequestioner

Bill Prew

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
Robberbaron (robr)

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
zequestioner

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

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.
Robberbaron (robr)

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.
Get an unlimited membership to EE for less than $4 a week. Unlimited question asking, solutions, articles and more. ASKER zequestioner 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 Bill Prew 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 ASKER zequestioner same with office 2010, are you x86 or x64? Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it! James Murphy Bill Prew I'm x86. ~bp Bill Prew 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 Robberbaron (robr) bills script should fix the error. which was myinputfile = myinputfile = objFSO.GetAbsolutePathName(strInFile) should have been myinputfile = objFSO.GetAbsolutePathName(strInFile) Get an unlimited membership to EE for less than$4 a week.
Unlimited question asking, solutions, articles and more.
zequestioner

Office 2010/2013 are both generating the same error:

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

change line 23...

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

Thanks 'rob'.

~bp
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
zequestioner

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

Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
SOLUTION
Get an unlimited membership to EE for less than $4 a week. Unlimited question asking, solutions, articles and more. Bill Prew Great minds thinking alike there? :-) ASKER zequestioner Thanks guys!!!!!!!!!!! Works perfectly in Excel 2010 as well as 2013! Thanks again! Get an unlimited membership to EE for less than$4 a week.