Copy as Path

OldFella · Apr 10th 2016

I need help with a coding requirement that I've not previously experienced. 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 as path
Paste to Range("B11") of worksheet CSV_List

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

**pike** · Apr 10th 2016

Re: Copy as Path

Hello
What are you trying to?
Make a list of all the file names in the folder?
The workbook M400AD.xlsm is in folder F:\SM\
and the csv files are in the folder F:\SM\M400AD\

maybe

Code

strFolder = ThisWorkbook.Path & "\" & Split(ThisWorkbook.Name, ".")(0)

OldFella · Apr 10th 2016

Re: Copy as Path

Hi pike

Yes, you're partially correct; the workbook M400AD.xlsm is in folder F:\SM\ and the csv files are in the folder F:\SM\M400AD\

But no, I don't want a list of the file names in folder F:\SM\M400AD\; I need a list of the paths. This where I'm lost - even more lost than usual! I've never previously used VBA to copy paths. 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"). Just FYI, once I have that list of paths pasted into CSV_List I then import the csv files into the workbook - hence the need for paths, not just file names.

**pike** · Apr 10th 2016

Re: Copy as Path

thought so ..

Code

Option Explicit


Sub List_All_File()
     
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
     
    Set objFSO = CreateObject("Scripting.FileSystemObject")


    Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\" & Split(ThisWorkbook.Name, ".")(0))
    Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"


    For Each objFile In objFolder.Files
        Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Value = objFile.Path
    Next
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
     
End Sub

Display More

OldFella · Apr 10th 2016

Re: Copy as Path

Thanks pike - but you're losing me slightly. At which point do I give the coding the detail of my folder that contains the csv files? Will it give me output that can then be copied into CSV_List?

**pike** · Apr 10th 2016

Re: Copy as Path

Hmmmm
I don't follow as the path will always be F:\SM\M400AD\

OldFella · Apr 10th 2016

Re: Copy as Path

No matter where I run the code from I get a Run-time error '76' pop-up - Path not found.

Debug highlights the line:
Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "" & Split(ThisWorkbook.Name, ".")(0))

I figured from that that I need to input the folder path detail somewhere

**pike** · Apr 10th 2016

Re: Copy as Path

Hello
How do you intend to initialise the code? do you need a folder picker?

sktneer · Apr 10th 2016

Re: Copy as Path

You may try something like this.....

Code

Const mFolderPath As String = "F:\SM\M400AD"          'Folder path of csv files


Sub GetCSVFilesPath()
Dim ws As Worksheet
Dim fso As Object
Dim mFolder As Object
Dim mFile As Object
Dim r As Long


On Error Resume Next
Set ws = Sheets("CSV_List")
On Error GoTo 0


If ws Is Nothing Then
   MsgBox "The sheet CSV_List doesn't exist in the workbook.", vbExclamation, "Sheet Not Found!"
   Exit Sub
End If


Set fso = CreateObject("Scripting.FileSystemObject")


If Not fso.FolderExists(mFolderPath) Then
   MsgBox "The Folder " & mFolderPath & " doesn't exist.", vbExclamation, "Folder Not Found!"
   Exit Sub
End If


Set mFolder = fso.GetFolder(mFolderPath)


r = 11
For Each mFile In mFolder.Files
   If fso.GetExtensionName(mFile) = "csv" Then
      ws.Range("B" & r).Value = mFile.Path
      r = r + 1
   End If
Next mFile
End Sub

Display More

OldFella · Apr 10th 2016

Re: Copy as Path

Hi pike

When I'm trialing code I initialise it from the code module. Once it's working I assign it to a button on the relevant worksheet.

I've no idea what a folder picker is, therefore have no idea whether I need one - or not.

**pike** · Apr 10th 2016

Re: Copy as Path

you do Ill write it up

**pike** · Apr 10th 2016

Re: Copy as Path

run this code to pick a folder to copy the full path of the files within that folder

Code

Option Explicit


Sub List_All_File()
     
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim sOutputPath As String
      
      With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = ThisWorkbook.Path
        .Title = "Please Select a Folder to Save Files"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub
        sOutputPath = .SelectedItems(1)
    End With


    Set objFSO = CreateObject("Scripting.FileSystemObject")


    Set objFolder = objFSO.GetFolder(sOutputPath)
    Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"


    For Each objFile In objFolder.Files
        Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Value = objFile.Path
    Next
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
     
End Sub

Display More

OldFella · Apr 11th 2016

Re: Copy as Path

pike / sktneer_1
Sorry to be so long responding. I'd been at it yesterday for close on 20hr and it was doing my head in! I'm very much a novice programmer and what's simple to you guys is bewilderment to me.

Anyway - the outcome is all I could have wished for. I've tested both your last posted coding using test workbooks and data and the results are perfect.

Thank you both so much for your assistance with this query and pike, thank you for your patience and help with this and my various previous queries.

sktneer · Apr 11th 2016

Re: Copy as Path

You're welcome. Glad we could help.

**pike** · Apr 12th 2016

Re: Copy as Path

no problem sand groper

Copy as Path

Participate now!

Share