importing file name to cell

[MrSak87]

Hi all,

Hopefully this will be an easy one for you guys although I can't find a solution anywhere. I have a simple macro which imports various data from RAW excel files and cleans it into a separate excel sheet.

Sub importpart2()
Application.ScreenUpdating = False

'' SETUP

Dim DestWB As Workbook: Set DestWB = ActiveWorkbook
Dim UserChoice

DestWB.Worksheets("Data").Activate
Range("A3").Select
Dim LastLine As Long
LastLine = Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row


'' OPEN FILE

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="Excel Files *.xlsx (*.xlsx),")


If FileToOpen = False Then
MsgBox "No file specified."
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen
End If

Dim SrcWB As Workbook
Set SrcWB = Application.Workbooks.Open(FileToOpen)

The macro itself works fine. However I want the FILE NAME of the excel sheet i'm importing to be put into a cell. To make things easy just to be placed into cell A1. Is this possible?

Thanks for your time

 

[AkaTrouble]

using a formula in a cell in source workbook (as the workbook has to have been saved for this formula to work)

this will place the filename (only) in the cell

<code>=CELL("filename")</code>

to complete this in the VBA code as you have the variable FieToOpen you could just put this into a cell something like this

.range ("A1") .value = FileToOpen

<<< Untested

here is a function i found elsewhere

<code>Public Function GetMyProp(prop As String) As String
    GetMyProp = ThisWorkbook.BuiltinDocumentProperties(prop)
End Function</code>

 

[MrSak87]

That easy eh!? How embarrassing!

One more thing, is it possible to not have the directory listing - i.e not include C:\Users\etc just the actual name of the file I'm opening?

Cheers for your reply

 

[AkaTrouble]

once you have the filename in a cell try this in a helper cell. there may be a simpler way others will offer

=RIGHT(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)

 

[MrSak87]

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

Cheers you pointed me in the right direction and I got it. Nice one

 

[AkaTrouble]

your solution lots neater than mine

glad to help