Solved

How do I get the names of each Excel workbook located in a folder with a macro?

Posted on 2016-11-16
6
47 Views
Last Modified: 2016-11-17
Hi,

I have a folder in path G:\Share. In the folder a have a lot of excel workbooks. Is there code that can be run from Excel to capture the name of each workbook without the extension .xlsm? Below is a sample of what is in my folder and what I want.

Folder Contents                               What I Want on Excel

ABC Construction.xlsm                  ABC Construction
River Works.xlsm                            River Works

Thank you.

Conernesto
0
Comment
Question by:Conernesto
6 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 total points
Comment Utility
Try this...

Sub GetWorkbookNames()

    Const FolderPath As String = "G:\Share\"
    Dim filename As String
    filename = Dir(FolderPath)
    Do
        If InStr(filename, ".xls") > 0 And Right(filename, 4) <> "xlsm" Then
            Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Split(filename, ".")(0)
        End If
        filename = Dir()
    Loop While filename <> ""
    
End Sub

Open in new window

0
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
Comment Utility
If I understand your requirement correctly, here is the little correction to the Wayne's code...
Sub GetWorkbookNames()

    Const FolderPath As String = "H:\Excel\"
    Dim filename As String
    filename = Dir(FolderPath)
    Do
        If InStr(filename, ".xl") Then
            Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Split(filename, ".")(0)
        End If
        filename = Dir()
    Loop While filename <> ""
    
End Sub

Open in new window

As per the line#7 in the code, the code will get the names of all the excel workbooks. But if you want to narrow your search only to .xlsm files, change that line to If InStr(filename, ".xlsm") Then
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
Comment Utility
Neeraj, they need all Excel files except *.xlsm.

capture the name of each workbook without the extension .xlsm
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
That's interesting. If you read the example provided in the description, it seems that the requirement is to get the file names without the extension.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
You should not use SPLIT() with the ".", since "." is a legal character in the file name.  Instead, use InStrRev() to find the last ".".  Alternatively, you can instantiate a filesystemobject and use it to give you the basename property, which is the file name without the file extension.
0
 

Author Closing Comment

by:Conernesto
Comment Utility
Thank you very much.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now