Harold, It sounds like you are preloading the EDI fle into Excel. Have you ever considered just reading the EDI file in as a text file within the UDF? If you use this approach, I think at least two parts of your UDF are unnecessary if you are dealing with X12 because: 1. if you are loading a true (complete) raw EDI X12 file (with all envelopes), you can always determine the EOS (End of Segment) by checking the 106th character of the EDI text string. The ISA segment is always 105 characters in length - fixed length, so you can get the EOS character by checking the 106th character in the EDI text string. This way you don't have to identify the EOS/terminator to the UDF as a parameter. 2. You can also determine the Element separator by checking the 4th character in the EDI X12 text since the first three will always be ISA. This way you don't identify your element separator to the UDF as a parameter. One additional suggestion: Once you have your EOS character, you can use the split VBA function to parse the entire file by segment into an array, then just do quick loop to get to the correct segment (just check the first 2-3 characters in the array element to get to the correct segment), do a secondary split of that segment by element separator into a second array and return the value of the nth (or nth-1 depending on your option base) array element from the secondary array used to hold the segment. By using this approach you can eliminate one or more of your parameters, simplify the UDF and make it function much quicker. Actually I have written an entire pseudo translator in Excel (that pulled EDI data into some custom SQL tables outside of the translator as well as building a custom reconciliation app for a situation where the company was using dual translator during a server migration) that incorporates the above rules. Thanks, Ken Etter [email protected]
From: Harold DeWayne <[email protected]> To: Cc: [email protected] Sent: Sunday, June 26, 2011 7:44 AM Subject: [EDI-L] <OFF-TOPIC> A gift for the group An Excel UDF that I use. Add it to your library of UDFs and treat it like any other function in Excel. Insert a "wrapped" EDI file in one column, then use this function elsewhere to extract individual elements wherever you need them. e.g. =EDIGetElement( {source cell}, {element # you want to extract}, {delimiter if not '*'}, {terminator if not '~'}) Not sure if anyone will want to use this, but _I_ do and I thought I'd share. Harold DeWayne, CECP, RECS ---------------------------------------------------------- Function EDIGetElement(text As Variant, N As Integer, Optional Delimiter As String, Optional Terminator As String) As String ' Written by Harold DeWayne, CECP, RECS If Delimiter = "" Then Delimiter = "*" If Terminator = "" Then Terminator = "~" 'Returns the nth element from a delimited text string Dim txt, str As String Dim Count, i As Integer 'Manipulate a copy of the text string txt = text 'If a space is used as the delimiter, remove extra spaces If Delimiter = Chr(32) Then txt = Application.Trim(txt) 'Check for terminator If (Right(txt, 1) = Terminator) Then txt = Left(txt, Len(txt) - 1) End If 'Add a delimiter to the end of the string If (Right(txt, 1) <> Delimiter) Then txt = txt & Delimiter End If 'Initialize count and element Count = 0 str = "" 'Get each element For i = 1 To Len(txt) If Mid(txt, i, 1) = Delimiter Then Count = Count + 1 If Count - 1 = N Then EDIGetElement = str Exit Function Else str = "" End If Else str = str & Mid(txt, i, 1) End If Next i EDIGetElement = "" End Function ---------------------------------------------------------- [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ------------------------------------ ... Please use the following Message Identifiers as your subject prefix: <SALES>, <JOBS>, <LIST>, <TECH>, <MISC>, <EVENT>, <OFF-TOPIC> Job postings are welcome, but for job postings or requests for work: <JOBS> IS REQUIRED in the subject line as a prefix.Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/EDI-L/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/EDI-L/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
