Use VBA to get excel file name into a cell

Hi everyone,

I am trying to write a VBA to get the file name of the active workbook in worksheets(1). Split that file name in sections with the hyphens that separate each section. then paste each part of the array into specific cells of the active workbook worksheets(1).

In the example file the file has been named

name - color - weight - clothes

I want the vba to take this name and separate each part by where the hyphens are and place each one in the cells below

"name" into cell B2
"color" into cell B3
"weight" into cell B4
"clothes" into cell B5

This VBA will be part of a bigger VBA where I will be looping through multiple excel files in a folder to extract the information from the file name and place them in specific cells. Below is the code I tried and I keep getting an "object required" error and the debugger does not tell me anything.

Sub excelfilepath()

Dim filename() As String
Dim fname1 As String, fname2 As String, fname3 As String, fname4 As String
Dim fpath As String

With ActiveWorkbook.Worksheets(1)

'get file path with file name
fpath = aplication.ActiveWorkbook.FullName

'split by hyphens into array
filename() = Split(fpath, " - ")

'set each part of array equal to variable
fname1 = filename(0)
fname2 = filename(1)
fname3 = filename(2)
fname4 = filename(3)

'put each variable into desired cell range
.Range("B2").Value = fname1
.Range("B3").Value = fname2
.Range("B4").Value = fname3
.Range("B5").Value = fname4
End With

End Sub


All help is appreciated. Thanks in advanced!

Had application misspelled in the original code. Once fixed the code worked as it should.

You might consider setting the 'Require Variable Declaration' setting in the VBE options. That'll catch all misnamed variables (and objects) when the project is compiled before running.

Untitled.png

ETA: The downside is you'll have to declare all variables before using them but that is nothing more than good practise in any case.