Processing Excel files

Question

I have some 40-50 excel files that have useful information in the first 20x25 cells and then junk beyond these cell. What is the easiest way to clean up the junk from all the files in one fell swoop, so that only the first 20x25 cells remain? Can I do this using Powershell on Windows?

You could create a macro that loops through all the excel files in a folder, and then clears the data in all cells *outside* your pre-defined range, then save. — BruceWayne, Oct 15 '15 at 16:39
Do these workbooks have on one sheet each, or are there multiple sheets that need to be scrubbed? — Excel Hero, Oct 15 '15 at 16:42
If it can be done in VBA, it can be done in Powershell. VBA is nothing more than a programming language that accesses the Excel object model; Powershell can do this just as well, and perhaps better (correct me if I am wrong, but Powershell has far superior facilities for working with collections, for example). Not knowing Powershell, I can't advise you on the specifics of porting a VBA program to Powershell, but for an example of the similar task of porting VBA to Python see [here](http://stackoverflow.com/a/13509702/111794). — Zev Spitz, Oct 15 '15 at 17:05
Are all these files in the same folder, or are they identified by lines in a text file, or some other type of list? — Zev Spitz, Oct 15 '15 at 17:11

score 2 · Answer 1 · edited Oct 17 '15 at 21:58

2

To answer your question, yes this can be done with PowerShell and VBA. A macro that opens each workbook in a folder, then each worksheet in a workbook, then deletes data outside a selected range, and then saves, should do the trick.

edited Oct 17 '15 at 21:58

Zev Spitz

13,950
6
64
136

answered Oct 15 '15 at 17:08

BruceWayne

22,923
15
65
110

Do you mean it can be done in Powershell or in VBA? The VBA part is not quite relevant to the question. Also, I suggest you provide more detail -- either a complete Powershell program, or at least the methods and objects from the Excel object model being used. – Zev Spitz Oct 15 '15 at 21:58
@ZevSpitz - That's not what OP asked for. OP asked if it can be done in Powershell - yes it can. If he wants a whole program to be made for him, he could hire someone. Otherwise, he could provide some further detail, such as what has he tried? What has and hasn't worked, and what is any code that he may have so far. – BruceWayne Oct 16 '15 at 14:08

gtwebb · Answer 2 · 2015-10-15T17:00:58.317

Here is some vba code I had that I tweaked slightly. It loops through all files in a folder (make sure it is just excel files since there is no check), opens then runs process_workbook which takes the first sheet, deletes everything outside the first 20x25 cells and resaves (without warning).

Please run these on copies of the files in case it doesn't run properly.

Sub process_folder()
Dim book_counter As Integer
Dim folder_path As String
Dim pWB As Workbook, sWB As Workbook, sWB_name As String
Dim pWS As Worksheet

    book_counter = 0
    first_sheet = True

    folder_path = "C:\"

    folder_path = verify_folder(folder_path)
    If folder_path = "NULL" Then
        Exit Sub
    End If

    'Get first file to open
    sWB_name = Dir(folder_path, vbNormal)

    'Loop through files
    Do While sWB_name <> ""

        'Open each file
        Workbooks.Open Filename:=folder_path & sWB_name
        Set sWB = Workbooks(sWB_name)

        Call process_workbook(sWB)

        'close file increment counter
        sWB_name = Dir()
        book_counter = book_counter + 1
    Loop

    'Number of files processed
    MsgBox ("Number of Fragment Files processed: " & book_counter)


End Sub

Sub process_workbook(sWB As Workbook)
     With sWB.Sheets(1)
       .Range(Cells(21, 1), Cells(.Rows.Count, Columns.Count)).ClearContents
       .Range(Cells(1, 26), Cells(20, .Columns.Count)).ClearContents
    End With
    sWB.Activate
    ActiveWorkbook.Close SaveChanges:=True

End Sub

Function verify_folder(path As String) As String

    If path = "" Then
        MsgBox ("Enter the Directory of the Fragment simulation files to process")
        verify_folder = "NULL"
        Exit Function
    End If

    If Not PathExists(path) Then
        MsgBox ("Directory does not exist")
        verify_folder = "NULL"
        Exit Function

    End If

    If Right(path, 1) <> "\" Then
            verify_folder = path & "\"
    End If

End Function

Function PathExists(pName) As Boolean
On Error Resume Next
    PathExists = (GetAttr(pName) And vbDirectory) = vbDirectory
End Function

You're correct its vba, I assumed since it was tagged as excel as well that vba would also work. — gtwebb, Oct 15 '15 at 16:58

Processing Excel files

2 Answers2