Batch file (.bat) to Open and Save an Excel file

Question

I have an Excel file on server (.xls) format. I think this is generated in some higher version of Microsoft Office Excel.

I have some SSIS packages which are failing in reading these files.

When I open and save these files as 97-2003 workbook (extension .xls) and run the SSIS packages, it executes successfully.

I can not make any changes in SSIS so I was thinking of writing a Batch (.bat) which I can schedule before my SSIS run to open and save the Excel file in required format (which I am currently doing manually) so as to make my SSIS run successfully.

How could I implement this?

Is there some better way?

Answer 1

For server task automation, give it a try with PowerShell. It's really easy to understand and use.

Resources:

convert your ‘old’ XLS files to the new XLSX format

Powershell script to convert all xls documents to xlsx in a folder recursively

Excel Convert .xls to .xlsx

Answer 2

Create a vbs script.

set xlapp = createobject("Excel.Application")
xlapp.Workbooks.Open "C:\Users\User\Documents\Super.xls"
'43 is 95/97 look up xlExcel9795 in object browser to see other options
xlapp.ActiveWorkbook.SaveAs "C:\Users\User\Documents\Super.xls", 43