Solved Batch File to Delete the last two lines of an Excel File

April 16, 2013 at 04:07:59
Specs: Windows Vista
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


See More: Batch File to Delete the last two lines of an Excel File

Report •


✔ Best Answer
April 17, 2013 at 07:18:15
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



#1
April 16, 2013 at 06:43:23
You will need to set the macro to execute in the workbook's open event, call the macro that you want to run. You can use a batch file to open the workbook by using something similar to this:

@echo off
start excel.exe "C:\Users\Newbie10\Desktop\myspreadsheet.xlsx"
exit

Replace the string after "excel.exe" with the location of your workbook.


Report •

#2
April 16, 2013 at 07:49:45
Perhaps a better way would be to use VBScript. You can either make the VBScript perform the same actions as your macro, or you can run your macro from VBScript.

How To Ask Questions The Smart Way


Report •

#3
April 16, 2013 at 14:35:53
Thank you for the advice but using a MACRO is not an option for the final version. I personally use a MACRO now but need a BATCH file to complete the work of the current macro. The XLS file cannot contain a macro (policy of company) therefore the BATCH file needs to do all the work. As mentioned, the BATCH file needs to:
1. Delete the last two lines of the XLS file and
2. Select ALL cells and UNMERGE them.
Thank you
Gary

Report •

Related Solutions

#4
April 16, 2013 at 14:42:14
If that's the case, VBscript may be your best option. I don't use VBscript as often as I use batch files, so I can't offer you much advice other than "Google is your Friend!"

I just did a quick search for "modify excel file using vbscript" and found tons of helpful information. Good luck and be sure to post back if you need more help!


Report •

#5
April 16, 2013 at 19:26:06
Yes, affirmative Houston. Confirming previous transmissions #2 and #4: Batch can't directly access excel data. You could export to text, work on it with batch, then re-import, but of course that can only delete lines or rows or cells, not merge or unmerge cells (as far as I know, but I'm not savvy). As M2 pointed out recently (paraphrased due to memory deficit on my part): "batch doesn't do excel".
If I'm wrong, (about the concept, not the quote!) I hope to be corrected ;-)

Report •

#6
April 17, 2013 at 07:18:15
✔ 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


Report •

#7
April 17, 2013 at 20:31:25
Razor2.3 you are a genuis. The script works like a charm. WOW. Thank you.
I don't want to ask too much but is there a way to create a file (BATCH or executable) to automate the process of dropping the specific XLS file onto the VBS file?
As you can probably tell, I am not that familiar with VBS script or process.

Again, THANK YOU for the above script. It works flawlessly.
Gary


Report •

#8
April 18, 2013 at 07:02:11
It's possible, but I'd need to know the location of the spreadsheet, and its name. Basically, something to pick it out of the hundreds of spreadsheets you probably have on your system.

How To Ask Questions The Smart Way


Report •

#9
April 18, 2013 at 11:12:16
Dear Razor2.3
The location for the VBScropt and the Excel would both be:
S:\SHARED\Flight\4-TEAM 4 DO NOT DELETE\Excel\

There would only be the one VBScript file in that folder and two excel files:
One Excel is an XLS file and one as an XLSX file.
The names of the XLS file would vary over time (would it be possible to use something as *.xls to allow for any possible name) and the XLSX file is there for another function.

Again, thank you for your help.
Gary


Report •

#10
April 18, 2013 at 11:36:02
So you want *.xls and *.xlsx?

How To Ask Questions The Smart Way


Report •

#11
April 18, 2013 at 19:54:37
Dear Razor2.3
Sorry for my poor description.
No, only the *.xls file needs the be "worked" with the VBScript
Thanks
Gary

Report •

#12
April 21, 2013 at 15:22:18
So something like this?
Set fso = CreateObject("Scripting.FileSystemObject")
Set excel = CreateObject("Excel.Application")
excel.DisplayAlerts = False
For Each file In fso.GetFolder(".").Files
  If UCase(fso.GetExtensionName(file)) = "XLS" Then
    Set lastCell = Nothing
    Set wb = Nothing
    On Error Resume Next
      Set wb = excel.Workbooks.Open(file, 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 'file
excel.Quit : Set excel = Nothing

How To Ask Questions The Smart Way


Report •


Ask Question