VBA - Copy as Path

Question

I need help with a coding requirement that I've not previously experienced. I just browsed a similar issue raised here a couple of years ago - VBA to Copy files using complete path and file names listed in Excel Object. My issue is similar but somewhat simpler than the OP.

I have a number of folders that each contain about 100 small .csv files; for each folder I need to copy the path for each file to an open worksheet. Each folder of .csv files has its own associated workbook.

As one example, the open workbook is F:\SM\M400AD.xlsm and the active worksheet is CSV_List. The folder containing the .csv files is F:\SM\M400AD. Doing it manually, my sequence is then:

Open folder F:\SM\M400AD

Select all

Copy path

Paste to Range("B11") of worksheet CSV_List

When I do it manually, as described above, I get a list that looks like:

"F:\SM\M400AD\AC1.csv"
"F:\SM\M400AD\AC2.csv"
"F:\SM\M400AD\AE.csv"
"F:\SM\M400AD\AF.csv"
"F:\SM\M400AD\AG.csv"
"F:\SM\M400AD\AH1.csv"
"F:\SM\M400AD\AH2.csv"
"F:\SM\M400AD\AJ.csv"

and on down the page until I have a list of 100 paths. This single column list is then pasted into worksheet CSV_List, starting at Range("B11").

I need to automate this and would be grateful if a VBA guru could kindly code this for me.

_if a VBA guru could kindly code this for me._ that's not how SO works. Please familiarise yourself with the [help] — chris neilsen, Apr 10 '16 at 08:59

score 1 · Answer 1 · edited May 23 '17 at 12:07

Such of question has been asked before, for example:

Loop through files in a folder using VBA?
List files in folder and subfolder with path to .txt file

The difference is you want to "automate" it, which means you want to execute code on workbook Open event.

How to achieve that?

Open F:\SM\M400AD.xlsm file.
Go to Code pane (ALT+F11)

Insert new module and copy below code

Option Explicit

Sub EnumCsVFilesInCurrentFolder()

    Dim sPath As String, sFileName As String
    Dim i As Integer

    sPath = ThisWorkbook.Path & "\"
    i = 11
    Do
        If Len(sFileName) = 0 Then GoTo SkipNext
        If LCase(Right(sFileName, 4)) = ".csv" Then
            'replcae 1 with proper sheet name!
            ThisWorkbook.Worksheets(1).Range("B" & i) = sPath & sFileName
            i = i + 1
        End If

SkipNext:
        sFileName = Dir(sPath)
    Loop While sFileName <> ""

End Sub

Now, go to ThisWorkbook module and insert below procedure:

Private Sub Workbook_Open()
    EnumCsVFilesInCurrentFolder
End Sub

Save and close workbook

The workbook is ready to use. Whenever you open it, EnumCsVFilesInCurrentFolder macro will be executed.

Note: you have to change above code to restrict the number of records.

@TobyAllen, Thank you very much for improving my answer. – Maciej Los Apr 10 '16 at 09:18 — Maciej Los, Apr 10 '16 at 09:18

VBA - Copy as Path

1 Answers1