Extract from Filename - Excel
Extract from Filename - Excel
(OP)
I need a macro to extract a number from the filename to use in it's calculation. Some measuring machine is programmed to return x-y coordinates at a constant cutting plane z with the z given in the filename. I need to transform the coordinates and output x-y-z. Here are examples of the filenames.
Y1.5000-Pre Main-1.txt
Z-2.7000-Pre Main-1.txt
Thanks,
Roger
Y1.5000-Pre Main-1.txt
Z-2.7000-Pre Main-1.txt
Thanks,
Roger





RE: Extract from Filename - Excel
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Extract from Filename - Excel
Method 1 if you are not strong:
Write into a cell the sheetname.
Then use text to columns to strip out the common elements using fixed widths. This will delete X and Y
Then repeat with text to columns using delimited width where it will look for -P and split again
Hide the columns with the redundant components.
Method 2:
Usig right(), left() or trim() in the macro on sheetname.
I will upload examples later if you want.
RE: Extract from Filename - Excel
Thanks
RE: Extract from Filename - Excel
If file name is in cell A3
=LEFT(RIGHT(A3,LEN(A3)-1),FIND("-Pre",RIGHT(A3,LEN(A3)-1))-1)
Will give you the number
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Extract from Filename - Excel
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Extract from Filename - Excel
RE: Extract from Filename - Excel
Try replacing "FIND" with "SEARCH" in that formula.
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Extract from Filename - Excel
tf = ActiveWorkbook.Path & "\ImpellerFiles\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(tf)
Set fc = f.Files
iFile = 1
cIndex = 6
For Each f1 In fc
Workbooks.OpenText Filename:=tf & f1.Name _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma _
:=False, Space:=True, Other:=False, OtherChar:="|", FieldInfo:=Array( _
Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
BladeWorkbook = ActiveWorkbook.Name
Range("k1").Activate
ActiveCell.Value = BladeWorkbook
With Worksheets(1).Range("k1")
Cut = Left(Right(k1, Len(k1) - 1), .Find("-Pre", Right(k1, Len(k1) - 1)) - 1)
End With
If (ActiveCell.Value <= Z) Then
Z = Mid(BladeWorkbook, 3, 5)
Else
Y = Mid(BladeWorkbook, 2, 5)
End If
RE: Extract from Filename - Excel
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Extract from Filename - Excel
RE: Extract from Filename - Excel
In a MACRO, assuming all the files have "-Pre Main-1.txt" as the final text (or at least the same number of characters) Then it is simply
MID(k1,2,LEN(k1)-16)
... assuming the last bit is always the same.
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Extract from Filename - Excel
MID(k1,2,LEN(k1)-16) and the other times
MID(k1,3,LEN(k1)-16)
I have yet to make it descern betweent the two. See my If statement at the end of the macro. It works to extract the number in the filename in only one of the cases. I've got a niggly "-" before the number in one type of file.
Y1.5000-Pre Main-1.txt
Z-2.7000-Pre Main-1.txt
RE: Extract from Filename - Excel
Still... no problem, just makle it ABS(MID(k1,2,LEN(k1)-16)) ... that should strip the -ve sign ... I am pretty sure that ABS is still a macro function if not, just square and then squareroot the result.
Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies
RE: Extract from Filename - Excel
RE: Extract from Filename - Excel
If you don't need to open the file then I wouldn't even do that. I did the same thing in your For loop without having to open the file and use the cell K1 for swaping values. I did (and recommend) that you just stick with manipulating varibles in VB and only grab cells for input or final output. Trying to go back and forth between them seems too confusing. If you do need to open the file, the below code will still work.
This is assuming these are actually filenames:
Y1.5000-Pre Main-1.txt
Z-2.7000-Pre Main-1.txt
Work for you?
Ken
CODE
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(tf)
Set fc = f.Files
iFile = 1
cIndex = 6
For Each f1 In fc
BladeWorkbook = f1.Name
Axis = Left$(BladeWorkbook, 1)
Value = Mid$(BladeWorkbook, 2, InStr(BladeWorkbook, "-Pre") - 2)
If (Left$(Value, 1) = "-") Then
AbsValue = Right$(Value, (Len(Value) - 1))
Else
AbsValue = Value
End If
Next
RE: Extract from Filename - Excel
Thanks so much for your help,
Roger