×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBScript converts xls to txt but the txt files are still open in Excel and cannot be deleted
3

VBScript converts xls to txt but the txt files are still open in Excel and cannot be deleted

VBScript converts xls to txt but the txt files are still open in Excel and cannot be deleted

(OP)
Forgive me, I dont know vbs, I searched and searched for what I have so far and feel like I am very close to a solution on this.

All files are in the same folder, currently on my laptop.
The task at hand is...

1.Take 3 xls files and convert them to txt
2.Combine the txt files into a single file - I've got a simple batch file handling this
3.Load the combined txt file into my ERP system(Epicor Vision) -
4.Then the cleanup part of this would be to delete the xls and txt files before the next time we run this process, which would likely be done on a weekly basis.

Mr Google led me to this solution for converting the xls to txt, which works great BUT, when I try to delete the txt files, I get the message that "The action cannot be completed because the file is open in Excel".
Or if I try to delete at command line I get "The process cannot access the file because it is being used by another process."

I think all I am missing is something to close Excel but I have searched for a while and have yet to find something that works with this.
My script...

CODE --> vbs

Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.Visible = False
Set wb = ExcelObject.Workbooks.Open("C:\csv_combine\FUTURE_900_INCREASE_CODING.xls")
wb.SaveAs "C:\csv_combine\FUTURE 900 INCREASE CODING .txt", -4158
Set wb = ExcelObject.Workbooks.Open("C:\csv_combine\FUTURE_004_INCREASE_CODING.xls")
wb.SaveAs "C:\csv_combine\FUTURE 004 INCREASE CODING .txt", -4158
Set wb = ExcelObject.Workbooks.Open("C:\csv_combine\FUTURE_901_INCREASE_CODING.xls")
wb.SaveAs "C:\csv_combine\FUTURE 901 INCREASE CODING .txt", -4158 


Thanks in advance for all the your forthcoming brilliance!

RE: VBScript converts xls to txt but the txt files are still open in Excel and cannot be deleted

After doing your SaveAs to make text files,
Do a final SaveAs to save as an Excel file.

Now you can delete your text files.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: VBScript converts xls to txt but the txt files are still open in Excel and cannot be deleted

Afrer each Wb.SaveAs execute Wb.Close, otherwise the workbook pointing to text file stays open in excel.

combo

RE: VBScript converts xls to txt but the txt files are still open in Excel and cannot be deleted

Is the excel object closed before the script exits?

CODE

ExcelObject.Quit
set ExcelObject=Nothing 

Check task manager - how many excel instances are there running when the script exits?

RE: VBScript converts xls to txt but the txt files are still open in Excel and cannot be deleted

(OP)
Thank you gentlemen, stars for all of you!

Quote (xwb)

Check task manager - how many excel instances are there running when the script exits?
XWB, to answer your question, there was a single instance of Excel open in task mgr after running the script.


After exploring the options/suggestions, this is what I ended up with, which seems to do what I need.

CODE --> vbs

Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.Visible = False
Set wb = ExcelObject.Workbooks.Open("C:\csv_combine\FUTURE_900_INCREASE_CODING.xls")
wb.SaveAs "C:\csv_combine\FUTURE 900 INCREASE CODING.txt", -4158
Wb.Close SaveChanges = False 
Set wb = ExcelObject.Workbooks.Open("C:\csv_combine\FUTURE_004_INCREASE_CODING.xls")
wb.SaveAs "C:\csv_combine\FUTURE 004 INCREASE CODING.txt", -4158
Wb.Close SaveChanges = False
Set wb = ExcelObject.Workbooks.Open("C:\csv_combine\FUTURE_901_INCREASE_CODING.xls")
wb.SaveAs "C:\csv_combine\FUTURE 901 INCREASE CODING.txt", -4158
Wb.Close SaveChanges = False 

Thanks again!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close