Need help with Escll 2007 and VBS script

[dpierce4776]

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.

 

[SkipVought]

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?

 

[dpierce4776]

@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

 

[VulcanJedi]

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