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

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:

  1. Make the above code work with a directory
  2. Save As ListName01.xls after the copy/paste merge
  3. 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!

Answers


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.


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.


Need Your Help

StreamWriter not closing correctly?

c# httpwebrequest streamwriter

I'm having some trouble using the StreamWriter class correctly. I have about 10 objects I need to post, but only a couple at a time at most. However, after 2 posts, the third does not go through and

Waiting Screen In MVC 4

c# asp.net-mvc asp.net-mvc-4 c#-4.0

I want to show a loading screen when the user clicks a button to perform an action because the action takes 10 seconds to run.