Parse File Name

Topic

New Reply

Using Scripting.FileSystemObject. I would also like to automatically retrieve the FULL PATH and FILE NAME (as you can see from my code I have manually input the full path and file name). Once the code determines the FULL PATH and FILE NAME, I want to extract ONLY the first 4 (left) char’s of the file name then call this function in a cell in the worksheet. I am using Excel 2010 and the code below is what I currently have:

Code:

Function strWhatIsMyName()Dim strFName As String
Dim FSO As FileSystemObject
Dim objFile As File




Set objfso = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objfso.GetFile(“C:NameOfFile.xls”)


Wscript.Echo “Base name: ” & objfso.getbasename(objFile)


strFName = objfso.getbasename(objFile)




End Function

Thanks in advance.

Reply | Quote

Replies

How do you get the file in the first place? Are you running the code on a file already opened?

cheers, Paul

Reply | Quote

Paul,

Yes, I want the name of the spreadsheet that is currently open. I only want a portion of the name, the first left 4 characters. Since my post however, I put a function together to capture the current open spreadsheet and is below:

Function GetMyName() As StringDim sAgency As String

GetMyName = ActiveWorkbook.Name
sAgency = Left(GetMyName, 4)

End Function

This will give return the value I’m looking for but what I want to do next is place that value in a cell in the spreadsheet if another cell IS NOTNULL. I understand that a Function can’t return values to cells so I need an IF THEN ELSE statement and trigger it with an OnEvent action; something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If IsNull(Me.ActiveSheet!L3) Then
Me.ActiveSheet!b3 = “”
Else
Me.ActiveSheet!b3 = sAgency
End If

End Sub

So this is my newest delima, it doesn’t work! If you could offer a suggestion, I would be most grateful.

Reply | Quote

Hi

Try this instead:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If IsNull(Me.ActiveSheet!L3) Then
Me.ActiveSheet!b3 = “”
Else
Me.ActiveSheet!b3 = Left(GetMyName, 4)
End If

End Sub

zeddy

Reply | Quote

Roberta,

Give this a try:

Code:

Option Explicit

Function GetMyName() As String

  Application.Volatile

  If Trim([L3].Value) = "" Then
    GetMyName = ""
  Else
    GetMyName = Left(ActiveWorkbook.Name, 4)
  End If

End Function

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG

You picked up the problem with the Function not returning sAgency.
What about cell B3????

zeddy

Reply | Quote

RG

You picked up the problem with the Function not returning sAgency.
What about cell B3????

zeddy

Zeddy,

We don’t need no stinkin’ B3. 😆

You just put the function into B3 or any other cell for that matter:
[noparse]=GetMyName()[/noparse] works just fine on my Excel 2010. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Here’s a new and improved version that isn’t tied to L3.
Just put [noparse]=GetMyName(“L3”)[/noparse] in the cell where you want the answer. Of course substituting what ever cell it should check for L3.

Code:

Option Explicit

Function GetMyName(zChkCell As String) As String

  Application.Volatile

  If Trim(Range(zChkCell).Value) = "" Then
    GetMyName = ""
  Else
    GetMyName = Left(ActiveWorkbook.Name, 4)
  End If

End Function

In the sample attached try putting/clearing data in the Yellow cells and watch A1 & B3:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank all of you very much. Each suggestion works, however, the one that ultimately will provide what I want (since I eventually want to use it on any given cell) is RG’s. You guys rock.

Reply | Quote

RG

I was wondering if we could take your code a step further and am attaching a sample spreadsheet for clarification purposes.

Again, thanks in advance.

Reply | Quote

Roberta,

Be glad to help but the workbook wasn’t attached. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..and one that doesn’t need any Function or VBA is to just use this formula..

=MID(CELL(“filename”,$A$1),FIND(“[“,CELL(“filename”,$A$1))+1,4)

zeddy

Reply | Quote

Zeddy,

Nice! :clapping: But you didn’t do the test…
How about: [noparse]=IF(ISBLANK(L3),””,MID(CELL(“filename”,$A$1),FIND(“[“,CELL(“filename”,$A$1))+1,4))[/noparse]

How many more ways do you think we can find to skin this cat :evilgrin:
:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Aha! Amigo!

I knew you were going to say that.
I have started to see the future, and as I said to you next Thursday, “Well done RG for spotting my deliberate mistake”

zeddy

Reply | Quote

Zeddy,

Looks like we have 2 Dr. Who’s in the Lounge! 😆 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

WOW!!!! Now that’s what I’m talking about….it does exactly what I was looking for….RG, you rock.

Sorry about not uploading the sample spreadsheet, however, with your solution, it turns out I didn’t need it; with that said, I’m still curious if what you applied could work with your original post and my adaption? The sample spreadsheet is attached this time.

However, I understand if you don’t have time and others need your help.

Again, thanks a bunch for everyone’s time and effort, it’s greatly appreciated.

Reply | Quote

Hi Roberta

See attached file.

I changed RG’s function so that you just pointed to a cell instead of including a cell address in double quotes.

i.e. you use
=getmyname(D1)
..instead of =getmyname(“D1”)
..so when you copy the formula down it automatically adjusts e.g.
=getmyname(D7)

zeddy

Reply | Quote

Zeddy,

Between the two of us we make a pretty fair programmer. 😆

One last tweak, keep the Trim function in the test {If Trim(zChkCell) = “” Then} otherwise a check cell with a blank in it will cause the name to be displayed even though it looks like it shouldn’t! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You guys are AWESOME!!!! Thanks for your time and effort. Have a great day.

Reply | Quote