Avatar of MrTV
MrTV
Flag for Thailand asked on

How can I add file name into colum h automatics excel

I have a bout 30 excel files How can I add file name to column H automatics I want to prepare data to insert into database
Microsoft Excel

Avatar of undefined
Last Comment
als315

8/22/2022 - Mon
als315

Do you like to add file names from some folder or it should be file name of current file?
Joe Howard

Here's one way:
Sub Demo()

    Dim StrFile As String
    Dim strNames As String
    Dim i As Long
    Dim arr() As String

    StrFile = Dir(ActiveWorkbook.Path & "\*.xls*") ' use this line if all files reside in the same folder as the active workbook
    'StrFile = Dir("C:\Demo\*.xls*") ' Use this line (change path to reflect your path) if files to list are not in the same folder as the active workbook

    Do While Len(StrFile) > 0
        strNames = strNames & ";" & StrFile
        StrFile = Dir
    Loop

    arr = Split(strNames, ";")

    For i = LBound(arr) To UBound(arr)
        Range("H" & i + 1).Value = arr(i)
    Next

End Sub

Open in new window

MrTV

ASKER
In the same current file
Thank you
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Joe Howard

Then the code as I posted it will do what you want.
MrTV

ASKER
Hi MacroShadow

I try your code it work but I want to put the filename in  colum h to everyfile in folder   such as file name is  AA  put  AA in colum H  and if the file  name is ZZ put the ZZ in colum H    If ZZ have 30 row put zz into 30 rows

Thank
als315

If in each file should be own name, you can use:
=CELL("filename")
for file name with path or
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Open in new window

for name only
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Joe Howard

Do you only want the file name and not the whole path?
MrTV

ASKER
only file name with out extension     zz  not xx.xls thank you
ASKER CERTIFIED SOLUTION
Joe Howard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Joe Howard

Better yet, use this:
Sub Demo()

    Dim StrFile As String
    Dim strNames As String
    Dim i As Long
    Dim arr() As String

    StrFile = Dir(ActiveWorkbook.Path & "\*.xls*")

    Do While Len(StrFile) > 0
        strNames = IIf(Len(strNames) > 0, strNames & ";" & Split(StrFile, ".")(0), Split(StrFile, ".")(0))
        StrFile = Dir
    Loop

    arr = Split(strNames, ";")

    For i = LBound(arr) To UBound(arr)
        Range("H" & i + 1).Value = arr(i)
    Next

End Sub

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.