Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Parse File Name

  • Thread Tools
  1. 4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parse File Name

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Gold Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,147
    Thanks
    5
    Thanked 184 Times in 176 Posts
    How do you get the file in the first place? Are you running the code on a file already opened?

    cheers, Paul

  4. 4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,447
    Thanks
    20
    Thanked 162 Times in 158 Posts
    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
    Last edited by zeddy; 2012-04-10 at 08:23.

  6. Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,539
    Thanks
    176
    Thanked 663 Times in 604 Posts
    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
    Last edited by RetiredGeek; 2012-04-10 at 11:14.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,447
    Thanks
    20
    Thanked 162 Times in 158 Posts
    RG

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

    zeddy

  8. Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,539
    Thanks
    176
    Thanked 663 Times in 604 Posts
    Quote Originally Posted by zeddy View Post
    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:
    =GetMyName() works just fine on my Excel 2010.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,539
    Thanks
    176
    Thanked 663 Times in 604 Posts
    Here's a new and improved version that isn't tied to L3.
    Just put =GetMyName("L3") 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
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-04-10 at 13:17.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. 4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. 4 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    597
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,539
    Thanks
    176
    Thanked 663 Times in 604 Posts
    Roberta,

    Be glad to help but the workbook wasn't attached.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  13. Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,447
    Thanks
    20
    Thanked 162 Times in 158 Posts
    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

  14. Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,539
    Thanks
    176
    Thanked 663 Times in 604 Posts
    Zeddy,

    Nice! But you didn't do the test...
    How about: =IF(ISBLANK(L3),"",MID(CELL("filename",$A$1),FIND( "[",CELL("filename",$A$1))+1,4))

    How many more ways do you think we can find to skin this cat
    Last edited by RetiredGeek; 2012-04-10 at 18:31.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  15. Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,447
    Thanks
    20
    Thanked 162 Times in 158 Posts
    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

  16. Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,539
    Thanks
    176
    Thanked 663 Times in 604 Posts
    Zeddy,

    Looks like we have 2 Dr. Who's in the Lounge!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •