How to Add Specific Cell in Excel document using VBA

Question

This is my scenario, I have a path that contains all excel files. Now, I want to implement a solution that will insert the current "Filename" in specific cell for that file.

For Example:

sample.xls
Sample2.xls
Sample3.xls

Inside that sample.xls, I want to inset the filename "sample" without the extension on cell "E7" and "E8" then save this file.

I currently have this formula to get the filename without the extension:

=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH(".",CELL("filename",A1))-1-SEARCH("[",CELL("filename",A1)))

Is it correct that you have the full path in there? And if you can already get the name without the extension, what exactly do you want to do with it after? (PS: you're missing an "r" in "I want to INSET the filename") — Kathara, Jan 26 '16 at 08:22
@Kathara Since I have a lists of excel file, I want to implement this in macro. I already tried pasting this formula in a cell and it works fine. — PeterS, Jan 26 '16 at 08:24
ok, you know that you can add formulas to cells by vba? Then you can make your cell-range dynamic, for example working with .offset. — Kathara, Jan 26 '16 at 08:27
@Kathara Im new with the VBA. Im trying to implement a script that can insert the filename in a defined cell without extension — PeterS, Jan 26 '16 at 08:29
on Stackoverflow normally noone will just give you the answer to your problem, you have to show, what you have already tried, the more the better. I'm quite new to vba as well. Have you tried ANYTHING in vba yet? — Kathara, Jan 26 '16 at 08:31
In which column do you have the full path normally? in which column do you want the name of the file? in which column do you want the extension? Is it always the same on each list? — Kathara, Jan 26 '16 at 08:39
@Kathara I've already try this code Sub Place1() Dim FileName As String FileName = ThisWorkbook.Name FileName1 = Replace(FileName, ".xls", "") Range("BL2:BL3").Value = FileName1 End Sub Now, i want all excel files located in a path to perform this then save — PeterS, Jan 26 '16 at 08:50
Found a link for looping through a folder, you'll have to adapt it of course: http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba — Kathara, Jan 26 '16 at 08:56

Kathara · Answer 1 · 2016-01-26T09:21:59.710

Here are a few codes that might help you on the way:

For the last row in a workbook:

LastRow = ActiveWorkbook.ActiveSheet.Cells(ActiveWorkbook.ActiveSheet.Rows.Count, "a").End(xlUp).Row

ActiveWorkbook can be replaced by "Workbooks(NameofWorkbook)".

ActiveWorksheet can be replaced by "Sheets(NameofSheet)".

For adding the paths of the excel files to column a:

Sub LoopThroughFiles()
    Dim MyObj As Object, MySource As Object, file As Variant
    file = Dir("c:\testfolder\")

    While (file <> "")
       ActiveWorkbook.ActiveWorksheet.Cells((LastRow+1), 1).Value = file
    Wend
    file = Dir
End Sub

Not too sure this works, you'll have to test it.

****EDIT_1****

For the file extensions the following might be useful (found on another question):

FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1)))

wb.name can later be replaced by the cells holding the path (by WB.WS.Cells(x, y).Value)

How to Add Specific Cell in Excel document using VBA

1 Answers1