Grab File Name Only From Full Path

Question

In VBA how can I grab the file name only from the full path that I have below? When used in my syntax below it imports the full file path (sans the .xlsx) as the table name which is not what I am after.

Public Function importExcelSheets() As Long
Dim Directory As String, TableName As String, strDir As String, strFile As String, I As Long
Directory = "C:\Test"
On Error Resume Next
 I = 0
 If Left(Directory, 1) <> "\" Then
     strDir = Directory & "\"
 Else
     strDir = Directory
 End If
 strFile = Dir(strDir & "*.XLSX")
 TableName = strDir & strFile
 While strFile <> ""
     I = I + 1
     strFile = strDir & strFile
     DoCmd.TransferSpreadsheet acImport, , TableName, strFile, True
     strFile = Dir()
 Wend
 importExcelSheets = I
End Function

This might work for you: https://stackoverflow.com/questions/27923926/file-name-without-extension-name-vba — Leonardo Fernandez, Oct 02 '17 at 15:15
Look at the first answer here: https://stackoverflow.com/questions/1743328/how-to-extract-file-name-from-path — , Oct 02 '17 at 15:20

Erik A · Accepted Answer · 2017-10-02T15:21:34.650

2

You're specifying here: TableName = strDir & strFile that the tablename is the full path. Just remove the directory component: TableName = strFile

You can remove the .xslx by just removing the last 5 characters: TableName = Left(TableName, Len(TableName) -5 )

edited Oct 02 '17 at 15:21

answered Oct 02 '17 at 15:16

Erik A

31,639
12
42
67

Grab File Name Only From Full Path

1 Answers1