How can I import file information from external documents?

**zeppelin150** · 05-31-2014, 07:06 AM

I have an external folder of video files that I'm creating a spreadsheet for. I've used vlookup functions before, but am not quite sure how to reference non-Excel documents for specific data.

What I'd like to do is specify a folder where my video files are located. Then I'd like Excel to retrieve and display data on each file that exists such as Filename, Data created, Hyperlink (to the file, with either filename displayed or just the word "Link"), and any other information (such as file size, etc.). I'm assuming this will require some programming, but am not quite sure how to go about it to display each value in it's own column.

I'm also curious if I created another column, named "Updated Filename", if it is possible to enter text in that column that would change the corresponding "Filename" to the newly entered value. Essentially, it would be using Excel to change file information in the video file folder.

Thanks in advance for any input!

**RudiS** · 05-31-2014, 10:29 AM

Hi,

I can get you started with some code I have on my PC to collect file properties from all files in a selected folder. Either you can tweak the code to provide additional details like a link to the file/video or you can supplement it with additional macros to process the data once this macro has retrieved it.

Here is the code: All you do is run the macro called RunDir. (The recursive one is called from RunDir)

Please Login or Register  to view this content.

**zeppelin150** · 05-31-2014, 12:03 PM

Hi Rudi,

Thanks for the post! I'm running Excel for Mac 2011, and I haven't worked with macros yet. My file directory is /Users/myname/Documents/Videos with sub folders inside it at /Users/myname/Documents/Videos/Subfolder1 and /Users/myname/Documents/Videos/Subfolder2 and so on.

Is there any way to give me a quick explanation on what each bit of code is doing so I can familiarize myself with the macro? Also, since I am running Mac, are any of these tasks PC specific? Thanks a lot for your help and I'm looking forward to getting this working correctly!

**RudiS** · 05-31-2014, 12:31 PM

Hi,

First, I am a PC user without much experience on MAC's. I will not know if the code I posted is compatible with a MAC (and/or its filing system). The best is to give it a try and see?

To use the macro (if its compatible)

In Excel, press ALT+F11 (to open the VBA window)
From the VBA windows menus, click on Insert and choose Module
Paste the code onto the module
Press ALT+Q to close the VBA window
In Excel, press ALT+F8 to open the Macro's dialog,
Select RunDir and choose Run

Hold thumbs that it runs

The RunDir macro will prompt you for a folder. It will open a Browse dialog allowing you to browse for any folder on your PC
When you browse and select a folder it will process all sub folders and all files within it.
The data processed is listed in the active sheet of the Excel workbook

The recursive macro collects the file properties of the file being processed and stores them in variables
The variable values are written to the worksheet one row at a time within the loop.
Just reading through the code will provide a good picture of what the code is doing.

Hope this provides a better glimpse into what you questioned.