File Explorer into Excel 1

[shytott]

From Windows File Explorer, is it possible to transfer file names held within a given Folder into a spreadsheet?

Many Thanks

 

[MakeItSo]

One possible way would be:
- write a c# or vb.net console application that takes the folder path as argument
- use ClosedXml NuGet package or a similar library to manipulate your Excel file from within that console application
- follow the instructions in this SO post to add a custom context menu item to Windows Explorer that will pass the folder path to your application

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[shytott]

Thanks MakeItSo, but that sounds way beyond my technical ability – I wouldn’t have a clue how to write an application. It was a (very) long shot, I was hoping that there might already be an existing program that might do this, seems there probably isn’t! Thanks for replying though, much appreciated.

 

[Andrzejek]

How about a little macro in Excel's VBA...

Option Explicit

Sub ShowFilenames()
Dim strName As String

strName = Dir("[blue]C:\TEMP\[/blue]")

Do While strName <> ""
    MsgBox strName
    strName = Dir
Loop

End Sub

Replace [blue]this path[/blue] with yours


---- Andy

There is a great need for a sarcasm font.

 

[mintjulep]

Or if this is a one-time need, pipe the DOS dir command output to a text file and import. Just requires a bit of simple clean up.

dir > files.txt

 

[shytott]

Hi Andy

Many thanks for your reply. I have tried your routine by attaching it to a command button and this results in a small window being opened headed ‘Microsoft Excel’ and underneath, the file name in question along with an ‘OK’ button which must be pressed to clear it from desktop. Clicking OK the presents the next file in sequence until it reaches the last file. I was hoping to have the files names to be placed in, eg, Col A of a spreadsheet whereafter I could manipulate the data. Is it possible to direct the output to a specified spreadsheet location?

Thanks

 

[Andrzejek]

Sure, try this:

Option Explicit

Sub ShowFileNames()
Dim strName As String
Dim intR As Integer

intR = 2
Cells(1, 1).Value = "File Names"

strName = Dir("C:\TEMP\")

Do While strName <> ""
    Cells(intR, 1).Value = strName
    strName = Dir
    intR = intR + 1
Loop

End Sub

Since this is a VBA solution, it should be in forum707


---- Andy

There is a great need for a sarcasm font.

 

[shytott]

Outstanding Andy!
Thats doing exactly what I was hoping for – apart from one tiny detail, its also listing the file extensions. As Im dealing with folders of many 100’s of files, is there away the final 4 characters can be stripped off? I can repost this in Forum707 if you'd prefer – sorry, I didn’t realise the solution would require VBA
Cheers

 

[Andrzejek]

If you are sure the extension is ALWAYS 4 characters (you may have something like [tt].xlsx, .xlsm, .xlsb[/tt], etc. - 5 characters - and that is just Excel), you may drop last 4 characters.

Or you may want to take the file name before the period - if you don't have any other periods in the file name:

Do While strName <> ""[blue]
    strName = Split(strName, ".")(0)[/blue] [green]' Add this line to your code [/green]
    Cells(intR, 1).Value = strName
    strName = Dir
    intR = intR + 1
Loop

---- Andy

There is a great need for a sarcasm font.

 

[shytott]

They are all 3 char, so I'm confident dropping the final 4 chars will do the trick.

Thanks

 

[shytott]

Yes, that has worked!

Thank you so much Andy, that has been of enormous help, really do appreciate your time on this.

Shytott, aka, Andy!!

 

[Andrzejek]

Just FYI, if you want to drop the last 4 characters, instead of Split() function (blue line of code above), use this:
[tt]
strName = Left(strName, Len(strName) - 4)
[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[shytott]

Gotcha - I've included both for future reference.

Thanks Again