Avatar of zequestioner
zequestioner asked on

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

Avatar of undefined
Last Comment
zequestioner

8/22/2022 - Mon
ASKER
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,"|"

Open in new window

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

Open in new window


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

Open in new window


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

Open in new window

~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.
ASKER
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
ASKER
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
Copyright (C) Microsoft Corporation. All rights reserved.

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.
ASKER CERTIFIED SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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.
See how we're fighting big data
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.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
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.
Unlimited question asking, solutions, articles and more.
ASKER
zequestioner

also for you to knowI  have another open question here:

https://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!