Need a Custom Batch File

[upinflamezzz]

I'm trying to simplify my UPS Worldship process. I have an xls file automatically exporting out of my mainframe intoa specified folder. Unfortunately Worldship needs it to be formatting and save as a CSV. I need to write a batch file that will call the excel file, delete the second row, format the zip code column as (Number - Special - Zip) then save the file as a CSV in the same folder. This would happen daily. File kicks out, user clicks on that batch file to modify the file, the the user starts using Worldship by referencing the order number (Column Number). Thanks for any help.

 

[mintjulep]

There is no such thing as column format in a CSV file.

Why not just work in Excel to make the CSV correct when it is created in the first place.

 

[mintjulep]

And WorldShip seems to be able to deal directly with .xls, so why bother with the CSV conversion in the first place?

 

[Hoaokapohaku]

upinflamezzz - If I understand correctly what you want, this should work as long as Excel is running and you have not closed the file where macro "CallCSVConversion" is located.

If you have a "personal.xls(b)" file in your xlstart folder, use the VBA editor put the following code in the "ThisWorkbook" object of the personal.xls(b) file. If you don't have that file in your xlstart folder already, you can create one, or use any macro-enabled excel file in the xlstart folder.

Private Sub Workbook_Open()
    Dim rTime As String
    'This will run at midnight.  Edit time below as needed.
    rTime = TimeValue("00:00:00")
    Application.OnTime rTime, "CSVConversion"
    MsgBox "Excel will update the Worldship CSV file at " & rTime
End Sub

Then, put the following code in any module in the same workbook:

Sub CSVConversion()
    'edit all path and file name references to the correct path and file name of the xls(?) file
    Workbooks.Open Filename:="C:\MyPath\MyBook.xlsm"
    Rows("2:2").Delete Shift:=xlUp
    'edit F:F below to refer to correct column
    Columns("F:F").NumberFormat = "00000-0000"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\MyPath\MyBook.csv", FileFormat _
        :=xlCSV, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
    MsgBox "The Worldship CSV file has was updated " & Now
End Sub

The above needs excel running to work. If it needs to run while unattended and excel is not running, the following may help, though I've never tried it:

http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html.

 

[Hoaokapohaku]

Ooops...my post has part of an earlier version of my solution in it. Where it says, "CallCSVConversion," in the first paragraph, it should be "Workbook_Open event macro". This forum needs a feature for post editing!