CODEWITHSUNDEEP
excelvbscript
Henry Sumner
Henry Sumner

Reputation: 21

Open corrupt Excel file in VBScript

I am attempting to convert around 600 excel files in a directory into CSV, so that they are in a standardised format for SAS Enterprise guide.

To do this, I have a batch file that performs a VBScript on each .xlsx file in the directory. This works for almost all of the excel files, except for the files that are 'corrupted'.

I'm not sure why they're corrupted, but they have all been converted from Google Sheets to Excel, so it's no surprise.

My problem is that the syntax that is supposed to force the workbooks.open() method to open a corrupted file is not working.

Please see my vbs code below:

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open ("Z:\ATR_Track\0108~ASHFORD TP~AT-ATv2~18-06-2018.xlsx", CorruptLoad:= xlRepairFile)

oBook.Sheets(WScript.Arguments.Item(0)).Select

set deleterange = oExcel.Range("A1", "A3").EntireRow

deleterange.AutoFilter()

deleterange.Delete

oBook.SaveAs "Z:\ATR_Track\test.csv" &"_ATR", 
csv_format

oBook.Close False
oExcel.Quit

The error that is returned reads "Expected ')'" at line 9, character 129.

Can anyone tell me what's wrong with my syntax?

Upvotes: 0

Views: 1674

Answers (1)

Sam H
Sam H

Reputation: 362

There are a couple of issues here.

Firstly - you need to define the constant that is xlRepairFile, you can do this by adding the following line at the top of your code (link to values [https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlcorruptload-enumeration-excel]):

CONST xlRepairFile = 1

Secondly - VBScript does not support named arguments. You will need to provide empty parameters limited with commas to complete the function (up to the argument you are looking to change, which in this instance happens to be the last one), such as the below (link to the WorkBooks.Open method that details all args [https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbooks-open-method-excel]:

Set oBook = oExcel.Workbooks.Open ("Z:\ATR_Track\0108~ASHFORD TP~AT-ATv2~18-06-2018.xlsx", , , , , , , , , , , , , , xlRepairFile)


I am not currently aware of any way around this, however Angus W may know + provide a better answer.

Complete new code:

csv_format = 6
CONST xlRepairFile = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open ("Z:\ATR_Track\0108~ASHFORD TP~AT-ATv2~18-06-2018.xlsx", , , , , , , , , , , , , , xlRepairFile)

oBook.Sheets(WScript.Arguments.Item(0)).Select

set deleterange = oExcel.Range("A1", "A3").EntireRow

deleterange.AutoFilter()

deleterange.Delete

oBook.SaveAs "Z:\ATR_Track\test.csv" &"_ATR", 
csv_format

oBook.Close False
oExcel.Quit

Upvotes: 1

Related Questions