Listing 1: The XLImportLookUpMatchIndex Subroutine

Sub XLImportLookUpMatchIndex
posit=instr(sourcecode,"Sub XLImportLookUpMatchIndex")
endposit = instr(sourcecode,"Sub XLSum")
textareabox1.innerText = mid(sourcecode,posit,endposit-posit)
Const ForReading = 1 : Const xlRight = -4152 : Const xlFillDefault = 0

Set fso = CreateObject("Scripting.FileSystemObject")
tf=txtPath & "C:\~~XLimport1~~.txt"
Set TextFile = fso.CreateTextFile(tf,True)
'Tab delimited file
TextFile.WriteLine "90" & vbtab & "Jack"	: TextFile.WriteLine "81" & vbtab & "Ben"
TextFile.WriteLine "12" & vbtab & "Elizabeth"	: TextFile.WriteLine "33" & vbtab & "Renee"
TextFile.WriteLine "14" & vbtab & "Molly"	: TextFile.WriteLine "55" & vbtab & "Jacob"
TextFile.WriteLine "96" & vbtab & "Robert"	: TextFile.WriteLine "37" & vbtab & "Beth"
TextFile.WriteLine "28" & vbtab & "Jill"	: TextFile.WriteLine "19" & vbtab & "Julie"
TextFile.WriteLine "24" & vbtab & "Bert"	: TextFile.WriteLine "64" & vbtab & "Kelly"
TextFile.WriteLine "74" & vbtab & "Katherine"	: TextFile.WriteLine "44" & vbtab & "Michael"
TextFile.WriteLine "83" & vbtab & "Alfred"	: TextFile.WriteLine "76" & vbtab & "Stephanie"
TextFile.WriteLine "92" & vbtab & "Zelda"	: TextFile.WriteLine "17" & vbtab & "Ed"
textfile.close

'Fixed Length delimited
tf2=txtPath & "C:\~~XLimport2~~.txt"
Set TextFile2 = fso.CreateTextFile(tf2,True)
TextFile2.WriteLine "90" & Space(3) & "Jack        T"	: TextFile2.WriteLine "81" & Space(3) & 
"Jake        D"
TextFile2.WriteLine "12" & Space(3) & "Elizabeth   R"	: TextFile2.WriteLine "33" & Space(3) & 
"Renee       III"
TextFile2.WriteLine "14" & Space(3) & "Molly       W"	: TextFile2.WriteLine "55" & Space(3) & 
"Jacob       A"
TextFile2.WriteLine "96" & Space(3) & "Robert      A"	: TextFile2.WriteLine "37" & Space(3) & 
"Beth        D"
TextFile2.WriteLine "28" & Space(3) & "Jill        S"	: TextFile2.WriteLine "19" & Space(3) & 
"Julie       K"
TextFile2.WriteLine "24" & Space(3) & "Bert        D"	: TextFile2.WriteLine "64" & Space(3) & 
"Kelly       N"
TextFile2.WriteLine "74" & Space(3) & "Katherine   V"	: TextFile2.WriteLine "44" & Space(3) & 
"Michael     J"
TextFile2.WriteLine "83" & Space(3) & "Alfred      F"	: TextFile2.WriteLine "76" & Space(3) & 
"Stephanie   R"
TextFile2.WriteLine "92" & Space(3) & "Zelda       L"	: TextFile2.WriteLine "17" & Space(3) & 
"Ed          E"
textfile2.close

Const xlDelimited = 1
Const xlFixedWidth = 2
Const xlNormal = -4143
Set XL = CreateObject("Excel.Application")
XL.workbooks.Add
XL.Visible = TRUE

XL.sheets.add.name = "ImportFixedLenth"
XL.ActiveWorkbook.Sheets("ImportFixedLenth").Tab.ColorIndex = 5
With XL.ActiveSheet.QueryTables.Add(_
 "TEXT;" & tf2, xl.Range("A1"))
 .FieldNames = False
 .TextFileStartRow = 1
 .TextFileParseType = xlFixedWidth
 .TextFileTabDelimiter = False
 .TextFileFixedColumnWidths = Array(5, 12)
 .TextFileSemicolonDelimiter = False
 .TextFileCommaDelimiter = False
 .TextFileSpaceDelimiter = False
 .Refresh BackgroundQuery = False
End With
XL.Application.CommandBars("External Data").Visible = False
XL.Range("A1").Select

XL.sheets.add.name = "ImportReference"
'*** BEGIN CALLOUT A
With XL.ActiveSheet.QueryTables.Add(_
 "TEXT;" & tf, xl.Range("A1"))
 .FieldNames = False
 .TextFileStartRow = 1
 .TextFileParseType = xlDelimited
 .TextFileTabDelimiter = True
 .TextFileSemicolonDelimiter = False
 .TextFileCommaDelimiter = False
 .TextFileSpaceDelimiter = False
 .Refresh BackgroundQuery = False
'*** END CALLOUT A
End With
XL.Application.CommandBars("External Data").Visible = False
XL.Range("A:B").Select
XL.Selection.Sort xl.Range("B1"),1,,,,,,xlNo,1,False,,1
XL.Range("A1").Select
XL.ActiveWorkbook.Sheets("ImportReference").Tab.ColorIndex = 6

If Fso.FileExists(tf) then
 Set f = fso.GetFile(tf)
 f.delete
 Set f2 = fso.GetFile(tf2)
 f2.delete
End If

XL.sheets.add.name = "LookUpMatchIndex"
XL.ActiveWorkbook.Sheets("LookUpMatchIndex").Tab.ColorIndex = 7
XL.Application.WindowState = xlNormal
XL.Application.Left = 1
XL.Application.Top = 115
XL.Application.Width = 735
XL.Application.Height = 350
XL.Cells(1,1).Value = "Name" : XL.Cells(1,2).Value = "LookUp" : XL.Cells(1,3).Value = "Match"
XL.Cells(1,4).Value = "Index"
XL.Cells(2,1).Value = "Elizabeth"	: XL.Cells(3,1).Value = "Julie"
XL.Cells(4,1).Value = "Molly"		: XL.Cells(5,1).Value = "Renee"
XL.Cells(6,1).Value = "Rudy"		: XL.Cells(7,1).Value = "Katherine"
XL.Cells(8,1).Value = "Jacob"		: XL.Cells(9,1).Value = "Bob"
XL.Cells(10,1).Value = "Stephanie"	: XL.Cells(11,1).Value = "Michael"
XL.Cells(12,1).Value = "Jill"		: XL.Cells(13,1).Value = "Robert"
XL.Cells(13,1).Value = "Joe"		: XL.Cells(14,1).Value = "Ed"
LastRow = XL.ActiveWorkbook.Sheets("LookUpMatchIndex").UsedRange.Rows.Count

XL.Range("B2").Select
XL.ActiveCell.FormulaR1C1 = _
 "=LOOKUP(C[-1],ImportReference!C[0],ImportReference!C[-1])"
XL.Selection.AutoFill XL.Range("B2" & ":" & "B" & lastrow), xlFillDefault

XL.Range("C2").Select
XL.ActiveCell.FormulaR1C1 = "=MATCH(C[-2],ImportReference!C[-1],0)"
XL.Selection.AutoFill XL.Range("C2" & ":" & "C" & lastrow), xlFillDefault
XL.Range("A2").Select

XL.Range("D2").Select
XL.ActiveCell.FormulaR1C1 = _
 "=INDEX(ImportReference!C[-3],MATCH(C[-3],ImportReference!C[-2],0),1)"
XL.Selection.AutoFill XL.Range("D2" & ":" & "D" & lastrow), xlFillDefault
XL.Range("D1").Select
XL.Selection.HorizontalAlignment = xlRight
XL.Range("C1").Select
XL.Selection.HorizontalAlignment = xlRight
XL.Range("B1").Select
XL.Selection.HorizontalAlignment = xlRight
XL.Range("D2").Select
'*** For Query TableObject Property Reference see:
'*** http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/vbaxl11/html/xlobjQueryTable1_HV05204306.asp
End Sub




3