Home/ Questions/Q 11512
Next
Answered
computing
  • 29
Asked: 2021-12-08T04:42:15+00:00 In:

Solved Batch File To Delete The Last Two Lines Of An Excel File

  • 29

I need a Batch File to do two things to an Excel (xls) file, 1) Delete the last two lines, and 2) Select all cells and Un-Merge the cells.

The XLS file is one file in a folder and I need the above two routines executed on the file. I have a macro that works but I want to automate the process using a batch file so those un-familiar with Excel can help with the project.

The XLS file is downloaded data that is unique to a specific person. I routinely download the data, place it (the XLS file) in a unique folder, open the file and preform the above two functions. This needs to be automated to streamline work flow.

Any help is appreciated. Thanks you. Gary

Share

    1 Answer

    1. Best Answer
      I too agree with the guy who agrees with the guy who agrees with my suggestion of VBScript.
      Save this as a .vbs, then drag and drop the workbook onto the resulting file.

      Set fso = CreateObject("Scripting.FileSystemObject")
Set excel = CreateObject("Excel.Application")
excel.DisplayAlerts = False
Set lastCell = Nothing
For Each arg In WScript.Arguments
  If fso.FileExists(arg) Then
    On Error Resume Next
      Set wb = excel.Workbooks.Open(arg, False, False)
    On Error GoTo 0
    If Not wb Is Nothing Then
      With wb.Sheets(1)
        .Cells.UnMerge
        Set lastCell = .Cells.Find("*", , , , 1, 2)
        If Not lastCell Is Nothing Then _
         If lastCell.Row >= 2 Then _
          .Range(lastCell, lastCell.Offset(-1, 0)).EntireRow.Delete
      End With
      wb.Save
      wb.Close
    End If
  End If
Next 'arg
excel.Quit : Set excel = Nothing

      How To Ask Questions The Smart Way

      • 0