parse file path

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
Dear All,
I am using excel 2007. Is it possible to a parse a file path within vba so that I start with
<table style="border-collapse: collapse; width: 320pt;" width="427" border="0" cellpadding="0" cellspacing="0"><col style="width: 320pt;" width="427"><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 320pt;" width="427" height="20">C:\Users\Peter Taylor\Documents\testdata\WIC_WASL1_2003A.txt</td> </tr></table>
And endwith <table style="border-collapse: collapse;" width="427" border="0" cellpadding="0" cellspacing="0" height="17"><col style="width: 320pt;" width="427"><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 320pt;" width="427" height="20">WIC_WASL1_2003A.txt

I have several hundred to deal with.

Thank you
Peter Taylor

</td> </tr></table>
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Pete

Chek ouyt the Split function:

Code:
'assume strFile contains your filename & path
 
'filename alone is:
 
MsgBox Split(strFile,"\")(Ubound(Split(strFile,"\")))
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
This will extract just the file name if that's what you're asking.

Code:
Sub Macro1()
    sPathAndFile$ = "C:\Users\Peter Taylor\Documents\testdata\WIC_WASL1_2003A.txt"
    
    sFile$ = Right(sPathAndFile$, Len(sPathAndFile$) - InStrRev(sPathAndFile$, "\"))
    
    MsgBox "File name is " & sFile
    
End Sub
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,212
Messages
5,509,864
Members
408,757
Latest member
Jamarr123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top