extract info from filename and fill cells

[solidENM]

Hello,
I am trying to setup a macro that will use the filename to populate the worksheet. Our system auto creates a filename that has a QT#, then customer name, job name and random notes after that. I am looking for help to fill cell A1 with the filename (without filepath) and use that to fill in B1 (QT#), C1(Customer), D1(job). The length of each varies, but the dash [-] is consistent and auto created.

sample filename: QT12345-customer-job-etc-etc

I am going to use these to format the worksheets header and footer, and as a reference when this sheet gets reformatted for another department.

Any help would be appreciated.

 

[WaterGypsy]

Something like this should help :

Dim bits

Range("a1") = ThisWorkbook.Name


bits = Split(Range("a1"), "-")
Range("b1") = bits(0)
Range("c1") = bits(1)
Range("d1") = bits(2)
Range("e1") = bits(3)

 

[solidENM]

Hello,
I was able to get a little further. Right now Im looking to extract -customer- and -job from the filepath. posting my current code so people dont have to start from scratch if they know how to extract the info after the 2nd and 3rd instance of "-"



Sub Macro3()
'
' Macro3 Macro
'


' insert new row as ref row.
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' insert filepath into A1
Dim myPath As String
Dim myOutput As String


'Retrieve ActiveWorkbook's File Path (Displayed in Immediate Window [ctrl + g])
myPath = ActiveWorkbook.FullName
Debug.Print "File Path: " & myPath
'Retrieve File Name without Extension (Displayed in Immediate Window [ctrl + g])
myOutput = Mid(myPath, InStrRev(myPath, "") + 1, InStrRev(myPath, ".") - InStrRev(myPath, "") - 1)
Debug.Print "File Name (w/o ext): " & myOutput
Range("A1") = myOutput


' split a1 filename into b1 QT#, c1 Cust, d1 job
' QT#
Range("B1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],FIND(""-"",RC[-1])-1)"




End Sub

 

[solidENM]

wow! thats awesome. I was excited to make some progress with my code, but yours is simple and right to the point! Thanks a ton.

Something like this should help :

Dim bits

Range("a1") = ThisWorkbook.Name


bits = Split(Range("a1"), "-")
Range("b1") = bits(0)
Range("c1") = bits(1)
Range("d1") = bits(2)
Range("e1") = bits(3)

 

[Fluff]

How about

Sub Macro3()
'
' Macro3 Macro
'
Dim Fname As String

' insert new row as ref row.
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Fname = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
Range("A1:D1").Value = Split(Fname, "-")
End Sub

This assumes that your files are new style (ie xlsx,xlsm etc)

 

[solidENM]

thanks Fluff. I ended up going with your code becauce i got a weird error from the previous code. it pulled the filepath from another open workbook. im thinking i could fix that by dimming the main workbook, but your code was nice and simple.

Thanks again (youve helped me in the past!)

 

[Fluff]

Glad we could help & thanks for the feedback