Solved

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

Posted on 2016-11-16
6
64 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
ID: 41890564
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 29

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41890859
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)
ID: 41891256
Neeraj, they need all Excel files except *.xlsm.

capture the name of each workbook without the extension .xlsm
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41891325
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
ID: 41891365
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
ID: 41891740
Thank you very much.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

821 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