Forumla to put filename (with no [ or ]) in a cell

[Claire_Brummell]

Hey,

I've tried the standard method for this.

I want the filname, without path and .xls in the cell.

But there's only one sheet so the full path is

S:\Brand\Third Party channels\Getty\Reports\06-10-17 - October Reports\Hallmark Folder - October Report.xls

As such the formulae using search functions and the "[" "]" as the tags to search for don't work.

I'm sure it's simple, just need help pulling out the right info!

Thanks,

Claire

 

[texasalynn]

Audry -- does Cell("filename") return a path+name+sheetname with or without the brackets? For me it returns with brackets, even when the workbook only contains one worksheet. Eg: this is what I get on a test workbook:<ul>[*]C:\Documents and Settings\trubyg\My Documents\[book34.xls]Sheet1[/list]

This works for me:<ul>[*]=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND(".",CELL("filename"))-FIND("[",CELL("filename"))-1)[/list]

yes Greg I get brackets also but Claire said she needed it to find the answer without brackets. ..... not sure what is different with hers

 

[marzman]

Alerion what a legend, after a little tweaking its worked...!!

For anyone who's interested, here's the finished code:

Option Explicit
Sub test()
Dim FilePath    As String
Dim DestWb      As Workbook
Dim i           As Integer
Dim SourceWb    As Workbook
Dim SourceSht   As Worksheet
Dim CopyRng     As Range
Dim DestRng     As Range
Dim StartRowOfTheRecordsAdded     As Integer

Application.ScreenUpdating = False
Application.EnableEvents = False

FilePath = "C:\Test Import Merge\" 'change to suit
Set DestWb = ThisWorkbook
Set DestRng = DestWb.Worksheets(1).Range("A2")
StartRowOfTheRecordsAdded = 2

    With Application.FileSearch
        .NewSearch
        .LookIn = FilePath
        .FileType = msoFileTypeAllFiles
    
        
        If .Execute > 0 Then
            For i = 1 To .FoundFiles.Count
                Set SourceWb = Workbooks.Open(Filename:=.FoundFiles(i), updatelinks:=0)
                Set SourceSht = SourceWb.Worksheets(1) 'adjust the sheet
                Set CopyRng = SourceSht.Range("A2:M500") 'adjust the range
                
                CopyRng.Copy Destination:=DestRng
                
                    Do While DestWb.Worksheets(1).Cells(StartRowOfTheRecordsAdded, 1).Value <> ""
                        DestWb.Worksheets(1).Cells(StartRowOfTheRecordsAdded, 14).Value = Left(SourceWb.Name, Len(SourceWb.Name) - 4)
                        StartRowOfTheRecordsAdded = StartRowOfTheRecordsAdded + 1
                    Loop
                
                SourceWb.Close False
                Set DestRng = DestWb.Worksheets(1).Range("A65536").End(xlUp).Offset(1)
                
            Next i
        End If
    End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Many thanks, and all the best!
Chris.

 

[Greg Truby]

I'm assuming someone is drinking [heavily] and posting??? For I cannot see a connection to this post and its code and the issue covered in this thread.

 

[texasalynn]

and what's even more weird - I was looking at this post today, when suddenly it shows a new post. Thought I had done it, but phew wasn't I