CODEWITHSUNDEEP
vbaexcel
Ziroxx
Ziroxx

Reputation: 1

Assist in getting file name when running a macro file

I have a file named: abcde_SN_179371_15_06_2016_09_28_45__ I want to access the 179371 part and the date (if it's possible to get it in dd/mm/yy or mm/dd/yy it's even better, does not matter between the two)

Can anyone help me in getting these two into variables, so I will be able to paste right at my file?

Thanks in advance!

Upvotes: 0

Views: 78

Answers (3)

CLR
CLR

Reputation: 12254

Assuming the filename is stored in string called filnm and underscores can not form part of the abcde or SN parts.. the below splits the string into parts separated by an underscore and then picks the 2nd element (remember, these start at zero) for the code fnCode, and builds a date from the 3rd, 4th and 5th which could be used for any date calculations you might have - fnDate. It then finally creates a string in case you actually want a string in your given date format - fnDateStr.

Dim fnDate As Date
Dim fnCode As String
Dim fnDateStr As String

fileparts = Split(filnm, "_")
fnCode = fileparts(2)
fnDate = DateSerial(fileparts(5), fileparts(4), fileparts(3))
fnDateStr = Format(fnDate, "mm/dd/yy")

Debug.Print fnCode, fnDate, fnDateStr

Upvotes: 4

Smithy7876
Smithy7876

Reputation: 344

Try this

Sub Test()

'Gets first section
ValuetoSplit = Range("A1").Value 'Assumed location of file name string
SplitFrom = InStr(InStr(ValuetoSplit, "_") + 1, ValuetoSplit, "_") + 1
Value = Mid(ValuetoSplit, SplitFrom, 6)

'Gets date
NextSplit = InStr(SplitFrom + 1, ValuetoSplit, "_") + 1
FileDate = Mid(ValuetoSplit, NextSplit, 10)
'Changes underscores to slashes to create date
FileDate = Replace(FileDate, "_", "/")
ValueAsDate = Format(FileDate, "dd/mm/yy")

MsgBox Value & " " & ValueAsDate

End Sub

Upvotes: 0

Plagon
Plagon

Reputation: 3138

Here you go: 

Sub Extract()
Dim val1, val2, val3 As String
val1 = "abcde_SN_179371_15_06_2016_09_28_45__"
val2 = Right(val1, Len(val1) - InStr(val1, "_"))
val2 = Right(val2, Len(val2) - InStr(val2, "_"))
val3 = Right(val2, Len(val2) - InStr(val2, "_"))
val2 = Left(val2, InStr(val2, "_") - 1) 'SerialNumber
val3 = Replace(Left(val3, 10), "_", "/") 'Date

MsgBox val2 & " " & val3
End Sub

Upvotes: 0

Related Questions