Link to home
Avatar of MrTV
MrTVFlag 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
Avatar of als315
als315
Flag of Russian Federation image

Do you like to add file names from some folder or it should be file name of current file?
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

Avatar of MrTV

ASKER

In the same current file
Thank you
Then the code as I posted it will do what you want.
Avatar of 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
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
Do you only want the file name and not the whole path?
Avatar of MrTV

ASKER

only file name with out extension     zz  not xx.xls thank you
ASKER CERTIFIED SOLUTION
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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

SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.