how to use VBscript to save TXT file as Excel File

Monday, June 29, 2009 7:46 PMjeromez

0
Sign In to Vote
I have the following VB script (I saved my VB script file as "open_document.vbs" and use Notepad as my editor):
```
Const xlDelimited  = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\jun_24v.txt",,,xlDelimited,,,,,,,True,"~"

ojbExcel.Save As "C:\documents and settings\jeromez\desktop\jun_24v.xls"
```
----------------------------------------------------------

PURPOSE OF SCRIPT:
once the txt file is open in Excel and parsed, I want the script to save the not as a txt, but as an Excel file using "Save as" file type "Excel" to my desktop

but it seems to save the file as "jun_24.xls" with the fileFormat as "Txt Delimited" and I want it to be "Microsoft Excel"

The file opens as an Excel file if I double-click on the file icon.

However, if I try to open the file from an instance of Excel (i.e. "File > Open") it still thinks the file is a text file and brings up the text box asking me if I want to "Delimted" or Fixed Width"

I would think after "Save As" I should be able to open file as an Excel file from Excel and not have to double-click on the icon

What am I doing wrong?

Any help would be appreciated

Thanks
- Edited byjeromez Tuesday, June 30, 2009 12:14 AM
- Edited byjeromez Tuesday, June 30, 2009 12:04 AM
- Edited byjeromez Tuesday, June 30, 2009 12:05 AM
- Edited byjeromez Monday, June 29, 2009 11:56 PM
- Reply
- Quote

Answers

Tuesday, June 30, 2009 6:50 PM

urkecModerator

Sorry about the confusion, I have Excel 2007 and xlExcel8 was added for it, so the script errors out if you have Excel 2003. Try using xlWorkbookNormal (= -4143) instead. Here is th modified script:

Const xlDelimited  = 1
Const xlWorkbookNormal = -4143

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\jun_24v.txt", _
    , , xlDelimited, , , , , , , True, "~"

objExcel.Workbooks("jun_24v.txt").SaveAs _
    "C:\Scripts\jun_24v.xls", xlWorkbookNormal

objExcel.Workbooks("jun_24v.xls").Close

objExcel.Quit

urkec

Marked As Answer byjeromez Tuesday, June 30, 2009 11:47 PM

All Replies

Tuesday, June 30, 2009 3:56 PMurkecModerator

0
Sign In to Vote
I suppose this is not the actual code, because 'ojbExcel' should be 'objExcel', 'Save As' should be 'SaveAs' which belongs to Workbook object, not Application. To save an open workbook in a different format, specify the format in which you want to save it (for xls files the constant is xlExcel8 = 56). Here is a sample:
```
Const xlDelimited  = 1
Const xlExcel8 = 56

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\jun_24v.txt", _
    , , xlDelimited, , , , , , , True, "~"

objExcel.Workbooks("jun_24v.txt").SaveAs _
    "C:\Scripts\jun_24v.xls", xlExcel8

objExcel.Workbooks("jun_24v.xls").Close

objExcel.Quit
```
urkec
- Reply
- Quote
Tuesday, June 30, 2009 6:15 PMjeromez

0
Sign In to Vote
Hi urkec

thank you for responding!

The first two items were typos - I had the code correct in my .vbs file but simply typed it incorrectly into the forum.

So I tried the code you wrote and now I'm getting the following error message:

-----------------------------------------------------

script: C:\Scripts\Open_document.vbs
Line:    11
Char:   1
Error:   SaveAs method of Workbook class failed
Code:   800A03EC
Source: Microsoft Office Excel
-------------------------------------------------------

I have Microsoft Excel 2003 here at work.

At one point before discovering the forum I did try the constant xlExcel 9795     43

and now it looks like it is constant 56 which is good

Any ideas how I can obviate the error message?

thanks!

Jerome
What I want is a celebration
- Reply
- Quote

Tuesday, June 30, 2009 6:50 PM

urkecModerator

Sorry about the confusion, I have Excel 2007 and xlExcel8 was added for it, so the script errors out if you have Excel 2003. Try using xlWorkbookNormal (= -4143) instead. Here is th modified script:

Const xlDelimited  = 1
Const xlWorkbookNormal = -4143

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\jun_24v.txt", _
    , , xlDelimited, , , , , , , True, "~"

objExcel.Workbooks("jun_24v.txt").SaveAs _
    "C:\Scripts\jun_24v.xls", xlWorkbookNormal

objExcel.Workbooks("jun_24v.xls").Close

objExcel.Quit

urkec

Marked As Answer byjeromez Tuesday, June 30, 2009 11:47 PM

Tuesday, June 30, 2009 7:27 PMjeromez

0
Sign In to Vote
Success!

Thanks! It works perfectly.

What I want is a celebration
- Reply
- Quote
Tuesday, June 30, 2009 11:55 PMjeromez

0
Sign In to Vote
One more question..... (not sure if I should post a new thread) but is it possible to write a VBscript that executes two separate VBscripts? or one script that executes the process above twice?

The only reason I was asking is because I have two text files 'Jun_24v.txt' and 'Jun24m.txt' that I import to Excel and then combine (with each import on a separate worksheet within one Excel file).

I was just trying to automate this process into one step, since I do it each week.

What I want is a celebration
- Reply
- Quote

Wednesday, July 01, 2009 6:32 PM

urkecModerator

You can do it all in one script. The idea is to open both files at the same time (each opens in a different workbook), move a worksheet from one workbook to the other one and save that workbook as an .xls file. Here is the code:

Const xlDelimited  = 1
Const xlWorkbookNormal = -4143

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
'objExcel.DisplayAlerts = False

objExcel.Workbooks.OpenText "C:\Scripts\jun_24v.txt", _
    , , xlDelimited, , , , , , , True, "~"
    
objExcel.Workbooks.OpenText "C:\Scripts\jun_24m.txt", _
    , , xlDelimited, , , , , , , True, "~"

objExcel.Workbooks("jun_24m.txt").Sheets("jun_24m").Move _
    objExcel.Workbooks("jun_24v.txt").Sheets("jun_24v")

objExcel.Workbooks("jun_24v.txt").SaveAs _
    "C:\Scripts\jun_24.xls", xlWorkbookNormal

For Each objWorkbook In objExcel.Workbooks
    objWorkbook.Close
Next

objExcel.Quit

I did not test the script thoroughly, once you are sure it works as expected you can uncomment the 'objExcel.DisplayAlerts = False line. You could also add some error checking.

urkec

Tuesday, November 03, 2009 3:51 PMrgtexas

0
Sign In to Vote
Thanks Advance..

Hey guys! I'm trying to do something similar.
I need to open an excel file and save it with a password, then email to a list users.

Any help with be greatly appriciated..
- Reply
- Quote
Tuesday, November 03, 2009 5:26 PMurkecModerator

0
Sign In to Vote
Hello,

please start a new thread, providing as much information as you can (like the scripting language you are using, the excel file location, where the user list is located etc). Also post your code, or the part that you are having problems with and describe what the problem is. If you haven't done any coding yet, I suggest that you first browse the Hey, Scripting Guy! archive, because it contains many useful articles that you can use as a starting point for you script (like this one or this one).
Uros Calakovic
- Reply
- Quote