How to Parse a string with VBA

[Jerry8989]

I need to parse the path from a "Application.GetOpenFilename"
I need to get just the xls file name. The directory will always be different.

C:\Test\DataImport\Documents\Data\DataTest.xls


Thank You
Jerry

[Bob Phillips]

sFilename = Right$(sFullname, Len(sFullname) - InStrRev(sFullname, "\"))

... or



sFilename = Split(sFullname, "\")(UBound(Split(sFullname, "\")))

[Jerry8989]

Thank for your reply I will try them both.
Do you know how to reference the actual file name of the file i'm in without using any of the active properties. I have to write all of thise without using activeworkbook or activesheet. thank You

[mdmackillop]

ThisWorkbook.Name

[Bob Phillips]

Thank for your reply I will try them both.
Do you know how to reference the actual file name of the file i'm in without using any of the active properties. I have to write all of thise without using activeworkbook or activesheet. thank You

The workbook you are in is Thisworkbook, but why can't you use Activeworkbook? Is this homework?

[Oorang]

sFilename = Mid$(sFullname, InStrRev(sFullname, "\")+1)

[Jerry8989]

xld,
I can't use active because I can't be a 100% guarenteed that the user doesn't click on something and the sheet focus changes. This isn't homework even though I wish it was for school and not work. LOL

Thank you everyone for your replies. I'm getting into the paresing now so I will let you guys know. Thanks again

[Jerry8989]

I'm not sure if I should make this question a new thread but I have another parsing question.

I have data as follows

YearUPCNumUPCName,2008,2009,2010,2011,2012XX14748802" Screw - Flat

With VBA how can I take the Year and make the data look like this

ModelYearPart NumberPart Name2008XX14748802" Screw - Flat2009XX14748802" Screw - Flat2010XX14748802" Screw - Flat2011XX14748802" Screw - Flat2012XX14748802" Screw - Flat

Thank You

[Jerry8989]

Sorry about the last post

I have data as follows

Year: ,2008,2009,2010,2011,2012
UPCNum: XX1474880
UPCName: 2" Screw - Flat

With VBA how can I take the Year and make the data look like this

Year: 2008
UPCNum: XX1474880
UPCName: 2" Screw - Flat

Year: 2009
UPCNum: XX1474880
UPCName: 2" Screw - Flat

Year: 2010
UPCNum: XX1474880
UPCName: 2" Screw - Flat

Year: 2011
UPCNum: XX1474880
UPCName: 2" Screw - Flat

Year: 2012
UPCNum: XX1474880
UPCName: 2" Screw - Flat


Thank You

[Bob Phillips]

On a new topic, better to start a new thread



Dim i As Long
Dim aryYears As Variant
With ActiveSheet

aryYears = Split(.Range("A1").Value, ",")
For i = UBound(aryYears) To LBound(aryYears) + 2 Step -1

.Rows(4).Resize(4).Insert
.Range("A5").Value = aryYears(LBound(aryYears)) & aryYears(i)
.Rows("2:3").Copy .Range("A6")
Next i

.Range("A1").Value = aryYears(LBound(aryYears)) & aryYears(LBound(aryYears) + 1)
End With

[Jerry8989]

XLD,
Thank You for your reply.

I will start a new thread when I have another question.

I will test the code out and reply back with any questions.

Thank You again
Jerry

[Jerry8989]

xld,
Why did ypu put

.Range("A1").Value = aryYears(LBound(aryYears)) & aryYears(LBound(aryYears) + 1)

Outside the for loop and do this "LBound(aryYears) + 2" for the loop. Can't it be "LBound(aryYears) + 1" and loop through all the years?

Thanks

[Bob Phillips]

xld,
Why did ypu put

.Range("A1").Value = aryYears(LBound(aryYears)) & aryYears(LBound(aryYears) + 1)

Outside the for loop and do this "LBound(aryYears) + 2" for the loop. Can't it be "LBound(aryYears) + 1" and loop through all the years?

Thanks

That was to overwrite the original cell data.

And no, it shouldn't be one, because we don't want to duplicate the first instance, the text is the first item, 2008 is the +1, 2009 (the first to create) is +2.