Forum

Using Excel to crea...
 
Notifications
Clear all

Using Excel to create directory structure

8 Posts
2 Users
0 Reactions
78 Views
(@david-clegg)
Posts: 4
Active Member
Topic starter
 

I understand with Excel there is a way to create a file directory structure, with use of VBA.  I understand that there is some functionality built into Excel 2019 which makes this easier than previous versions of Excel.  I would like to use this within the school I work in to automate the setting up of a directory structure where there is a lot of repetition of folder structures.  This would save a great deal of time and ensure consistency.  Can anyone offer any guidance please?

 
Posted : 01/05/2020 2:50 pm
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Hi David,

Attached is some code that will create a folder structure based on the contents of the sheet. You can probably work out for yourself how it works by looking at it.

I didn't know how you intended this to work at your end so the code may need some adjustment.

Regards

Phil

 
Posted : 01/05/2020 8:05 pm
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Forgot to say that the code uses FSO (FileSystemObject) so in order for it to work you'll need to set a reference in the VBA Editor to the Microsoft Scripting Runtime Library.

How to do this is described here 

Create Hyperlinked List Of Files from SubFolders

Cheers

Phil

 
Posted : 01/05/2020 8:09 pm
(@david-clegg)
Posts: 4
Active Member
Topic starter
 

Thanks Philip.  Will give this a go asap. I presume you can add further folders in column B infinitum?  David

 
Posted : 02/05/2020 5:58 am
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Hi David,

Yes, it'll keep going until it encounters a blank cell in Col B.

Regards

Phil

 
Posted : 02/05/2020 6:16 am
(@david-clegg)
Posts: 4
Active Member
Topic starter
 

Hi Phil

Thanks - got this to work.

I didn't need to setup the FSO as this had already been done.

I notice that you have to create the folder above before you can create a sub folder.  In other words you have to create the higher order folders first in the column B (above where you want to create a sub folder).

But that's OK as I can setup the folders in column b in an appropriate order

Regards

David

 
Posted : 03/05/2020 3:56 am
(@david-clegg)
Posts: 4
Active Member
Topic starter
 

Incidentally, I tried to upload avatar photo, but struggling to get this to work.  I used Photoshop to try and meet the requirements of size, dimension.  Am i missing a trick?

 
Posted : 03/05/2020 3:57 am
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Hi David,

What exactly is the issue with getting your avatar photo uploaded?

Phil

 
Posted : 03/05/2020 5:38 am
Share: