I am using this VBScript to delete column A in Excel:
Set objExcel = CreateObject("Excel.Application")
Set wy = xl.Workbooks.Open("D:\XX\Historical_data_extract.xlsx")
objExcel.Visible = True
objWorkbook.Worksheets("Sheet1").Range("A:A").Delete
objWorkbook.Save
objWorkbook..Close
The script is just opening the file but not deleting anything Why is it so?
Also how can I delete multiple column A and J?
Try changing your code to
Set objexcel = CreateObject("Excel.Application")
Set wy = objexcel.Workbooks.Open("D:\XX\Historical_data_extract.xlsx")
objexcel.Visible = True
wy.Worksheets("Sheet1").Range("A:A").EntireColumn.Delete
wy.Save
wy.Close
Your code were attempting to delete a range in an other workbook than the workbook that you had opened.
Columns E and G can be deleted by
wy.Worksheets("Sheet1").Range("G:G").EntireColumn.Delete
wy.Worksheets("Sheet1").Range("E:E").EntireColumn.Delete
wy.Worksheets("Sheet1").Range("A:A").EntireColumn.Delete
wy.Worksheets("Sheet1").Range("E:G").EntireColumn.Delete
Nothing is deleted because the workbook is opened into a variable wy, but you try to delete the column from a workbook in the variable objWorkbook. Which you didn't initialize.
You delete another column by specifying another column instead of A:A. Beware that deleting a column decrements the index of all columns right of the deleted column (B becomes A, C becomes B, and so on), so I'd suggest to delete them in reverse order.
wy.Worksheets("Sheet1").Range("J:J").Delete
wy.Worksheets("Sheet1").Range("A:A").Delete