×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Extract from Filename - Excel

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

RE: Extract from Filename - Excel

Can you get the file name into a variable in your macro? If so it will be a fairly simple matter to use a combination of the string manipulation functions to extract the number ...

Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies  

RE: Extract from Filename - Excel

How well do you know macros?
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

(OP)
You gave me the key.  What I was doing wrong was setting the filename into a variable.  I couldn't get the string functions to work with it in that form.  I'll put it in a cell and use an If Then Else to decide what to do to strip away the excess using the Mid function.  I can do one thing where the string starts with Z and the Else for everything else.

Thanks

RE: Extract from Filename - Excel

(OP)
I tried the =LEFT and got an error.  It says the Sub or Function is not defined and it hilites "FIND".

RE: Extract from Filename - Excel

(OP)
Same problem.  I tried surrounding it using a With and .FIND but got another error.   Invalid procedure call or argument.  Here is what I'm running.  My If statement sort of returns the numbers I want but the check always goes to ELSE so it only works half the time.

    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

As I said, try replacing the FIND function with the SEARCH function, Their input syntax is identical and they are almost identical in function (one is case sensetive (FIND) and the other isnt(SEARCH)), that should work.

Read the Eng-Tips Site Policies at FAQ731-376: Eng-Tips.com Forum Policies  

RE: Extract from Filename - Excel

(OP)
I did that and addressed it with my 1st line to which "Same problem." It says the Sub or Function is not defined and it hilites "FIND" or "SEARCH".

RE: Extract from Filename - Excel

Oh sorry, I thought you were putting this in a spreadsheet cell, NOT in a MACRO?!

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

(OP)
Yep, a macro.  I have the same number of ending characters but I don't have the same number of beginning characters so it has to be

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

I thought you would want to keep the -ve sign as it it clearly a position?

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

(OP)
I wouldn't have thought ABS would work on a string.  I'll give it a try.  I have another idea too.  Instead of coming in from the left or mid, I should grab my stuff by coming from the right side of the filename since all the files have the same length after that "-".

RE: Extract from Filename - Excel

rnordquest,

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

    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
        
        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

(OP)
Yes those are shortened forms of the filenames.  They use some machine to measure the surface contour of the impeller in a plane (constant y or z) with the cutting plane's dimension in the filename.  So I have to open the file to replace the bogus y or z with the real one then transform to a different coordinate system for output.  I also have to string several files together to get the complete surface.  There are 3 files to describe each cut.

Thanks so much for your help,

Roger

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources