return an external file name to a variable

[vtcrob]

I am trying to get an external file name minus the path into a variable for use in a module. I intend to use the file name as the sheet name. So, each file name that is found in a directory is reflected as a sheet name. I would then import the file data to that sheet with the same name.

 

[Jim Cone]

vtcrob,

I assume you have the path and are trying to extract the file name from it.
Following code uses the "Dir" function to return "List Files.xla" from the
provided path...
'---------------------------------
Sub GetFileName()
Dim MyFile As String
MyFile = Dir("C:\Program Files\Microsoft Office\Office\Library\List
Files.xla")
MsgBox MyFile
End Sub
'---------------------------

Regards,
Jim Cone
San Francisco, CA

I am trying to get an external file name minus the path into a variable

for use in a module. I intend to use the file name as the sheet name. So,
each file name that is found in a directory is reflected as a sheet name. I
would then import the file data to that sheet with the same name.