VBA Batch Action on Folder - CSV lists to fill an XLS template

Question

I'm new to VB/VBA. Trying to get this to work.

I have found this: Looping a code through a folder of workbooks with VBA?

But it doesn't quite address what I'm trying to do. I have ~60 .CSV files that are all clean and conformed, and I want to take these and put them onto an Excel template using a VBA. I was able to get one working using the "Record Macro" Function:

Range("A2:A33").Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\rs\Desktop\F15-Template.xlsx"
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Windows("List01.csv").Activate
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("F15-Template.xlsx").Activate
Range("H2:J2").Select
ActiveSheet.Paste
Windows("List01.csv").Activate
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("F15-Template.xlsx").Activate
Range("H3:J3").Select
ActiveSheet.Paste

But as you can tell this is for one list onto the template.

The things I'd like to do in addition to copy/pasting from list to XLS template are:

Make the above code work with a directory
Save As ListName01.xls after the copy/paste merge
If possible, apply a batch "Protect Sheet" to these files.

The lists live in C:\Users\rs\Desktop\lists and the template is on the bare Desktop -- Whatever you can do to help would be well appreciated.

If VB/VBA is the wrong tool for the job, please point me in the right direction. Thanks in advance!

What's the relation to [tag:batch-file]? Please update the tags appropriately! (Perhaps you meant [tag:batch-processing]?) — aschipfl, Dec 03 '15 at 02:03
if it is vba or excel-vba, it is not VB.NET. The tags have words on them telling you when to use them what they apply to etc. — Ňɏssa Pøngjǣrdenlarp, Dec 03 '15 at 02:06
Why do you specifically save as an **XLS** and not an **XLSX**? Is there some proprietary requirement for a 97-2003 XL format? — , Dec 03 '15 at 02:13

D_Bester · Answer 1 · 2015-12-03T02:22:25.720

This will get you the list of CSV files in the folder.

Sub test()

    Dim FileArray() As String
    Call GetFileArray(FileArray, "C:\Users\rs\Desktop\lists\")

    Dim x As Integer
    For x = LBound(FileArray) To UBound(FileArray)
        'action here
        Debug.Print FileArray(x)
    Next
End Sub


Sub GetFileArray(ByRef FileArray() As String, path)

    Dim fileName
    Dim fileCt
    Dim rowNum

    If Right(path, 1) <> "\" Then path = path & "\"
    'path = "C:\clients\06TAX\"

    fileCt = 0
    rowNum = 0

    fileName = Dir(path)
    ReDim Preserve FileArray(fileCt)
    FileArray(fileCt) = fileName

    Do While fileName <> ""
        fileName = Dir
        If Right(fileName, 3) = "WK3" Then
            fileCt = fileCt + 1
            ReDim Preserve FileArray(fileCt)
            FileArray(fileCt) = fileName
        End If
    Loop
End Sub

I'll leave it to you to fill in the rest of the code.

This was useful, but did not work in the way I wanted. Thank you for your answer! — postphotos, Dec 09 '15 at 22:23

postphotos · Accepted Answer · 2015-12-10T18:44:23.483

I ended up doing a variety of things. The best code block I could find was from Kent Finkle.

    $comments = @'
    Script name: DerpExcelGoodness.ps1
    Thanks to Kent Finkle
    Purpose: How can I use Windows Powershell to Open All the_
    Excel Spreadsheets in a Folder 
    and Run a Specified Macro Found on those Spreadsheets?
    Store Macro Excel Sheet in ~/Documents Folder
    6 - csv; 51 - xlsx
    '@
    #-----------------------------------------------------
    function Release-Ref ($ref) {
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
    [System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers() 
    }
    #-----------------------------------------------------
    $files = dir("E:\<path>\*.csv")

    $xl = new-object -comobject excel.application
    $xl.Visible = $True

    $xl.DisplayAlerts = $False

    foreach ($f In $files) { 
        $wb = $xl.Workbooks.Open($f.FullName)
        $ws = $wb.Worksheets.Item(1)
    #    Daisy chain is totally possible. Just uncomment.
    #    $a = $xl.Run("macro.xlsb!clean")
    #    $a = $wb.SaveAs($f.FullName + "-clean.csv", 6)
    #    $a = $xl.Run("macro.xlsb!gradesheet")
    #    $a = $xl.Run("macro.xlsb!protect")
    #    $a = $xl.Run("macro.xlsb!unprotect")
    #    $a = $wb.SaveAs($f.FullName + "-gradesheet.xlsx", 51)
        $a = $wb.Close()
    }
    $a = $xl.Quit()

    $a = Release-Ref($ws)
    $a = Release-Ref($wb)
    $a = Release-Ref($xl)

I simply uncommented when necessary and was able to run (from Powerscript) external Macros on a folder, which is pretty awesome and useful.

I stashed these in GitHub as well: here.

VBA Batch Action on Folder - CSV lists to fill an XLS template

2 Answers2