×
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!

*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

Need help with Escll 2007 and VBS script

Need help with Escll 2007 and VBS script

Need help with Escll 2007 and VBS script

(OP)
hello all I have a report that is run from our monitoring software. I have a batch file that converts the report from a txt file to a CSV file. the number of items in the report may change but not the headers. my goal is after the batch file converts the file it will run the VBS script to sort the CSV file by two columns say column A from A to Z then column F A to Z. then save and close the work book.

on a side note I would then need to move the file to two different locations. I am sorry but I am new to VBS script and I do not know if it is possible to use the VBS script to also copy the file to the two different locations. or will I have to figure out some way to use the batch file to call the VBS script wait till its done the copy the file.

RE: Need help with Escll 2007 and VBS script

Hi,

You have a batch file that converts a .txt to a .csv: BOTH TEXT FILES!

Then you batch file will, from what I deduce from "then save and close the work book," OPEN the .csv text file using EXCEL, sort, save & close.

Is that what's happening?

RE: Need help with Escll 2007 and VBS script

(OP)
@SkipVought
yes that is correct I got it working using VBS to call excel. (see code below) my issue is that the company has removed excel from the servers for lic. reasons is there a way to do the same thing with out calling excel. I have been looking at PowerShell as well. basically need to do the following.
1. convert from txt to csv
2. sort the file by column A then Column C both from A to Z
3. save the file in xls or xlsx format for the web server.

all without having excel installed on the server.


Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = _
objExcel.Workbooks.Open("C:\test\3.csv")

Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A1")
Set objRange3 = objExcel.Range("C1")

objRange.Sort objRange2, xlAscending, objRange3, , xlAscending, , , xlYes
' objWorkbook.save
objWorkbook.SaveAs "C:\test\new.xlsx", 51
objWorkbook.SaveAs "C:\test\new2.xlsx", 51
objExcel.quit

RE: Need help with Escll 2007 and VBS script

CSVs are just text files. If you need this in xls/xlsx format, and can't do this via the web application, and dont have XL installed. Yes I think Powershell is the best approach for this.
Import the CSV in PoSh, or get content, sort-object by said column headers, export csv or out file or something then copy file to the desired locations

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