+ Reply to Thread
Results 1 to 5 of 5

change code to get folder names instead of files

  1. #1
    Registered User
    Join Date
    02-12-2018
    Location
    Nuernberg
    MS-Off Ver
    2013
    Posts
    20

    change code to get folder names instead of files

    Hello everyone,

    I'm so glad I found this forum, so many threads here have helped me to do several tasks. I need a way to get all the folder names in a certain folder into excel workbook. I found this code which successfully retrieves file names but not folder names.

    Option Explicit
    Sub GetFileNames()
    Dim xRow As Long
    Dim xDirect$, xFname$, InitialFoldr$
    InitialFoldr$ = "C:\"
    With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = Application.DefaultFilePath & "\"
    .Title = "Please select a folder to list Files from"
    .InitialFileName = InitialFoldr$
    .Show
    If .SelectedItems.Count <> 0 Then
    xDirect$ = .SelectedItems(1) & "\"
    xFname$ = Dir(xDirect$, 7)
    Do While xFname$ <> ""
    ActiveCell.Offset(xRow) = xFname$
    xRow = xRow + 1
    xFname$ = Dir
    Loop
    End If
    End With
    End Sub


    I need it to retrieve folder names instead and I don't know how. Could someone please help me?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: change code to get folder names instead of files

    Try changing the 7 in Dir$ to 16 or vbDirectory.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    02-12-2018
    Location
    Nuernberg
    MS-Off Ver
    2013
    Posts
    20

    Re: change code to get folder names instead of files

    Thank you! It gets the folders now but it also gets the files, is there a way it could only get the folder names?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: change code to get folder names instead of files

    Forgot, vbDirectory tells Dir to include directories.

    So to only return directories you need to check attributes using GetAttr.

    Try this.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-12-2018
    Location
    Nuernberg
    MS-Off Ver
    2013
    Posts
    20

    Re: change code to get folder names instead of files

    Quote Originally Posted by Norie View Post
    Forgot, vbDirectory tells Dir to include directories.

    So to only return directories you need to check attributes using GetAttr.

    Try this.
    Please Login or Register  to view this content.

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel VBA code for selecting a folder then search & open specified files in that folder
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-27-2021, 03:09 AM
  2. [SOLVED] Loop Through Folder, Create Emails with Sub Folder Names in Subject, Attach files in sub
    By Rschwar23 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2015, 10:06 AM
  3. [SOLVED] Code to Change suffixes of files in a folder -For timestamping, version control, step thro
    By jkjeyakumar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-30-2014, 09:32 AM
  4. Change the workbook names in the new folder through code
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-12-2013, 08:32 AM
  5. Import TXT files from a specific folder into Excel - TXT file names change hourly
    By jeepinjeff in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2013, 04:08 AM
  6. Excel Names in column to text files names to folder
    By excelaron in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2013, 07:22 PM
  7. Change names of Files in a folder
    By TISR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-01-2006, 05:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1