ࡱ> _a^q h;bjbjt+t+ nAAh7] (vxxxxxx$g[Rtttt vvttvv`,!: : vSummary: The following is a small part of a larger modular program designed reduce repetitive typing. The actual full program is currently around 30 pages or so with 8 point font, to large and confusing to list it all here. A little background. A LRN is local routing number used by phone companies to know where to route ported (or LNP, local number portability) numbers. Once a customer submits orders to have their phone numbers routed to a new phone company, their phone number gets associated to a LRN. To ensure that this database work has gone correctly, local switch technicians can use the QLRN command (DMS type central offices) to verify (do a database dip) whether or not the LRN has been changed. Since we dont live in a perfect world, we often have to run this query on every one of a customers numbers (sometimes several hundred). So you can see why this script comes in handy. (Can you say carpal tunnel?) The idea of use here is, run the qlrntoolstart, which fills in sample data. Replace the sample phone number with the list of numbers to be checked (cut and paste, gotta love it.) Copy the first and third columns down to fill in. The click the command button. Once this is complete, go to Procomm, (log in and all of that good stuff.) Compile/run the script. Go back to Excel, click the command button, enter customer name, and watch the results. The files involved are, a blank spreadsheet to run the VBA script in, a spreadsheet of 10 digit phone numbers to check, the QLRN.WAS file built by VBA, and qlrnresults.txt (a capture file built by the ASPECT script.) And of course your final spreadsheet, built by Sub fileprocessor(). Any questions or comments, feel free to e-mail me at  HYPERLINK mailto:ksmith08@comcast.net ksmith08@comcast.net Sub qlrntoolstart() - This sub is the beginning, it formats the spreadsheet and inserts sample data, and adds the command button to start the ASPECT script building process (command button calls Sub QLRN). Sub QLRN() - This sub builds the actuall script, inserts ASPECT keywords, deletes the previous command button, and inserts a new button to process the results once the actuall ASPECT script has been run in Procomm (sub calls Scriptor, command button calls fileprocessor). Sub Scriptor() - Checks for the old file, deletes it, and saves the new script in the ASPECT directory. This is the core of the larger program I use, its designed to be modular, passing only the ASPECT script name, so it can be reused by many of my VBA scripts. Sub fileprocessor() - Takes the resulting capture file, and processes it, outputing a summary of valid queries vs. invalid queries. Actual VBA scripts involved. Sub QLRN() ' This script is designed to run qlrn query's and log the response to a pre-determined capture ' file from procomm plus. ' Designer: Kevin Smith Start Date: 4/24/01 ' Beta version 1a Rev. Date: 4/30/01 ' Revision 1a (4/30/01) ' Added code to include command buttons. Code added to workbook open event, and this module. This program is provided free of charge. The author makes no warranty/guaranty. Use at your own risk. You may modify any part of this, at your own discretion. Please leave the following attached: QLRN Tool (a subset of the DMS tool kit, currently still in beta testing.) Developed by :Kevin Smith  HYPERLINK mailto:ksmith08@comcast.net ksmith08@comcast.net Redistributing/Selling the code for this program without prior written consent is expressly forbidden. Dim scrptName As String scrptName = "qlrn" Application.ScreenUpdating = False Application.StatusBar = "Working, hold your pant's on." Call bckupdata ' End save data addition Range("a1").Select Do If ActiveCell.FormulaR1C1 = "" Then Exit Do ActiveCell.Offset(0, 1).Select ActiveCell.Value2 = " " + ActiveCell.FormulaR1C1 ActiveCell.Offset(0, 1).Select ActiveCell.Value2 = " " + ActiveCell.FormulaR1C1 ActiveCell.Offset(1, -2).Select Loop Range("A1").End(xlDown).Offset(1, 0).Select ActiveCell.FormulaR1C1 = "eof" Range("a2").Select ' inserts blanks lines between tuples Do If ActiveCell.FormulaR1C1 = "eof" Then Exit Do ActiveCell.EntireRow.Select Selection.Insert shift:=x1down ActiveCell.Offset(2, 0).EntireRow.Select Loop Range("a2").Select ' inserts waitfor scripting Do If ActiveCell.FormulaR1C1 = "eof" Then Exit Do ActiveCell.FormulaR1C1 = "waitfor " + String(1, 34) + ">" + String(1, 34) ActiveCell.Offset(2, 0).Select Loop ' inserts final waitfor scripting ' deletes the eof tag ActiveCell.FormulaR1C1 = "waitfor " + String(1, 34) + ">" + String(1, 34) ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "capture off" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "beep" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "set capture file none" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "endproc" Range("a1").Select ActiveCell.EntireRow.Select Selection.Insert shift:=x1down Range("a1").Select ActiveCell.FormulaR1C1 = "proc main" ' turn logging on, and off Range("a2").Select ActiveCell.EntireRow.Select Selection.Insert shift:=xlDown Range("a2").Select ActiveCell.FormulaR1C1 = "string Name = " + String(1, 34) + "qlrnresults.txt" + String(1, 34) Range("a3").Select ActiveCell.EntireRow.Select Selection.Insert shift:=xlDown Range("a3").Select ActiveCell.FormulaR1C1 = "set capture file NAME" Range("a4").Select ActiveCell.EntireRow.Select Selection.Insert shift:=xlDown Range("a4").Select ActiveCell.FormulaR1C1 = "capture on" Range("a4").Select ActiveCell.EntireRow.Select Selection.Insert shift:=xlDown Range("a4").Select ActiveCell.FormulaR1C1 = "clear" Range("a5").Select ActiveCell.EntireRow.Select Selection.Insert shift:=xlDown Range("a5").Select ActiveCell.FormulaR1C1 = "waitquiet 1" Range("a4").Select ActiveCell.EntireRow.Select Selection.Insert shift:=xlDown Range("a4").Select ActiveCell.FormulaR1C1 = "set capture overwrite on" ' Delete QLRN button ActiveSheet.Buttons.Delete ' Add Process button ActiveSheet.Buttons.Add(450.75, 48.75, 77.25, 32.25).Select Selection.OnAction = "fileprocessor" ActiveSheet.Shapes.SelectAll Selection.Characters.Text = "Process" With Selection.Characters(start:=1, Length:=4).Font .Name = "arial" .FontStyle = "regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = False .ColorIndex = False End With With Selection .Placement = xlFreeFloating .PrintObject = False End With Call Scriptor(scrptName) Cells.Select Selection.Delete shift:=xlUp Range("a1").Select Application.ScreenUpdating = True Application.StatusBar = "Done." End Sub Sub fileprocessor() ' purpose of this script is to process the qlrnresults.txt file ' Change date 5-3-01 added code to include date and time analysis was run. Dim strQNUM(1500) As String ' Number sent out in Query Dim strGOODBAD(1500) As String ' Result, good, bad, or error Dim strRETNUM(1500) As String ' Number returned from a successful Query Dim intIndex As Integer ' Index to reference array's Dim intMaxIndex As Integer ' Used to hold place of last intIndex Dim intNumPort As Integer ' Number of ported numbers Dim intNumNotPort As Integer ' Number of non-ported numbers Dim intNumError As Integer ' Number of errors Dim intI As Integer ' Misc index Dim message, Title, strCustName As String Title = "QLRN Tool" message = "Enter Customer Name:" strCustName = InputBox(message, Title) ' Open the capture file g:\Procomm4.8\Capture\qlrnresults.txt Workbooks.OpenText FileName:= _ "g:\Procomm4.8\Capture\qlrnresults.txt", Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _ , Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array _ (3, 1), Array(4, 1), Array(5, 1)) Range("b1").Select intIndex = 0 ' Set intIndex to 0, never use zero again, always start at 1 intNumPort = 0 intNumNotPort = 0 intNumError = 0 Do While ActiveCell.FormulaR1C1 <> "" ' Loop until end of file intIndex = intIndex + 1 strQNUM(intIndex) = ActiveCell.FormulaR1C1 '********* Look for failed query ************* ActiveCell.Offset(1, -1).Select If ActiveCell.FormulaR1C1 = "LNP" Then strGOODBAD(intIndex) = "ERROR" ActiveCell.Offset(2, 1).Select intNumError = intNumError + 1 Else ' Successfull query ActiveCell.Offset(2, 2).Select strRETNUM(intIndex) = ActiveCell.FormulaR1C1 '********* Compare sent number to Company X LRN 8885551212 ********** If strRETNUM(intIndex) = "8885551212" Then strGOODBAD(intIndex) = "Ported" ' Number matches LRN ActiveCell.Offset(3, -1).Select intNumPort = intNumPort + 1 Else strGOODBAD(intIndex) = "Not Ported" ' Number not match LRN ActiveCell.Offset(3, -1).Select intNumNotPort = intNumNotPort + 1 End If End If Loop ' *********** Prepare Report ************** Cells.Select Cells.EntireColumn.AutoFit Selection.Delete shift:=xlUp Range("a1").Select intMaxIndex = intIndex For intIndex = 1 To intMaxIndex ActiveCell.FormulaR1C1 = strQNUM(intIndex) ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = strGOODBAD(intIndex) ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = strRETNUM(intIndex) ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = intIndex ActiveCell.Offset(1, -3).Select Next ' Add Date and time Cells.Select Cells.EntireColumn.AutoFit For i = 1 To 10 Range("a1").Select Selection.EntireRow.Insert shift:=xlDown Range("a1").Select Next i Range("a1").Select ActiveCell.FormulaR1C1 = "*************************" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "*************************" ActiveCell.Offset(1, -1).Select ActiveCell.FormulaR1C1 = "Prepared on: " ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = Now() ActiveCell.Offset(1, -1).Select ActiveCell.FormulaR1C1 = "Number of Queries:" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = intIndex - 1 ActiveCell.Offset(1, -1).Select ActiveCell.FormulaR1C1 = "Number of Errors:" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = intNumError ActiveCell.Offset(1, -1).Select ActiveCell.FormulaR1C1 = "Number of Ports:" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = intNumPort ActiveCell.Offset(1, -1).Select ActiveCell.FormulaR1C1 = "Number of Non Ports:" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = intNumNotPort ActiveCell.Offset(1, -1).Select ActiveCell.FormulaR1C1 = "*************************" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "*************************" Range("a10").Select ActiveCell.FormulaR1C1 = "Queried Number" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "Status" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "Returned LRN" ActiveCell.Offset(0, 1).Select Cells.Select Cells.EntireColumn.AutoFit Range("a8").Select ActiveCell.FormulaR1C1 = "Customer Name:" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = strCustName End Sub Sub qlrntoolstart() Cells.Select Selection.NumberFormat = "@" Selection.ClearContents Range("a1").Select ActiveCell.FormulaR1C1 = "transmit " + String(1, 34) + "qlrn " ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "2488271461" ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "^m" + String(1, 34) Cells.Select Cells.EntireColumn.AutoFit ActiveSheet.Buttons.Add(450.75, 48.75, 77.25, 32.25).Select Selection.OnAction = "qlrn" ActiveSheet.Shapes.SelectAll Selection.Characters.Text = "qlrn" With Selection.Characters(start:=1, Length:=4).Font .Name = "arial" .FontStyle = "regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = False .ColorIndex = False End With With Selection .Placement = xlFreeFloating .PrintObject = False End With 'UserForm1.Hide Call QLRNInput End Sub Sub Scriptor(scrptName) ' ********************************** ' Written By: Kevin Smith ' This script saves the Aspect ' script to the correct directory. ' ********************************** Dim strLine As String ' Variable to hold information to be copied to Procomm Dim intColCount As Integer ' Variable that counts the number of column moves ' Set file name and location scrptLoc = "g:\Procomm4.8\Aspect\" & scrptName & ".was" Range("a1").Select If Len(Dir(scrptLoc)) > 1 Then Kill scrptLoc ' Check to see if file exists Open scrptLoc For Output As #1 ' Open file for output Do While ActiveCell.FormulaR1C1 <> "" ' Trap on end of fil intColCount = 0 strLine = "" Do While ActiveCell.FormulaR1C1 <> "" ' Trap on end of line strLine = strLine & ActiveCell.FormulaR1C1 ActiveCell.Offset(0, 1).Select intColCount = intColCount + 1 Loop strLine = strLine & ActiveCell.Offset(0, 1).FormulaR1C1 strLine = strLine & ActiveCell.Offset(0, 2).FormulaR1C1 strLine = strLine & ActiveCell.Offset(0, 3).FormulaR1C1 Print #1, strLine ' Write line of data to file If intColCount > 0 Then ActiveCell.Offset(1, (-intColCount)).Select Else ActiveCell.Offset(1, 0).Select End If Loop Close #1 ' Close file End Sub  q()PQRfgh;jCJU5CJ0JjCJU jCJUCJ  k l   pq  k l   pqNOy^h"Zij0W#KN./23f$nCbeNOy^h"Zij0WW#KN./23f$nCb +,?[z +,?[z9L}3Re!=\o,Qn!:Qj~  #$1NObcTUV ^ $!Y!!!!!!e9L}3Re!=\o,Qn!:Qj~  #$1NNObcTUV ^ $!Y!!!!!!"!""N"O"o""#R######6$E$W$g$h$$$$$$$)%*%N%O%z%%%%%%&"&I&~&&&&&'C'k''''''(:(C(H(S(X(_(`(e(f(((((((())$)%)T)w)))) *F*j*k*p*q********* +!+"+)+*+=+r++++,e""N"O"o""#R######6$E$W$g$h$$$$$$$)%*%N%O%z%%%%%%%&"&I&~&&&&&'C'k''''''(:(C(H(S(X(_(`(e(e(f(((((((())$)%)T)w)))) *F*j*k*p*q********** +!+"+)+*+=+r++++,.,M,m,,,,---L-q----. .P.,.,M,m,,,,---L-q----. .P.o.... /?/S/}////0$0%020M0`000000000000012171N1111232D2c2h222233M3e333333 4#4>4Z4g4l444444444555 5!5"5M5q555555I66667eP.o.... /?/S/}////0$0%020M0`0000000000000012171N1111232D2c2h222233M3e333333 4#4>4Z4g4g4l444444444555 5!5"5M5q555555I6666777777"7v7{7778 8%8:8C88888 999F9O999::S:d::::::;;;#;,;5;T;Y;^;_;g;h;+7"7v7{7778 8%8:8C88888 999F9O999::S:d:::::::;;;#;,;5;T;Y;^;_;g;h; / =!"#$%DyK ksmith08@comcast.netyK 8mailto:ksmith08@comcast.netDyK ksmith08@comcast.netyK 8mailto:ksmith08@comcast.net [$@$NormalmH <A@<Default Paragraph Font(U@( Hyperlink>*B*h7nh;!WN"%e(*P.0g47:h;"$%&()*,-./12356!,7h;#'+04( Q f h7XXKPyz 6 ?  "7_h8IDJ#>EnCT|?Z[kly*+823CDQ !<=MN[,>BOQmnVa!$01ABMc}Za#+bm(3]a}Oabl #%/1ACO\giuw 6 @ E R W b .!?!!!!!!!!!!! ""*";"Q"Z"["c""""" ####K#\#s#}########$$%$($5$$$$$$$$$$$$%%%%#%B%I%J%R%X%i%%%%%%%%%%%&&=&E&J&[&&&&&&&&&& '''('r''''( (M(^(((((((-)>)e)p)q))))))**P*a*******}++++,,%,1,2,L,,,,,,,,,,--1-------7.C.H.b.l........./ ///2/^/c/n/w//0G0Q0000000 111111A2H2Q2\22222-3031393J3R333444 4-4444444445 555W5^5a5h5k5|5555555555555*616o6z6666666j7S$2y   0 > ` h  8J'. JQnCU,2qsz029?} IKRXSU\b %1>HUanuGIOUZb?H~ DNyjlxyac6 @ E R W b ! !.!@!!!!!!!""*"<"Q"Z""""" ##K#]#s#}####$$%$$$$$$$B%J%X%j%%%%%%%&&J&\&&&''''*'0'r''''(!(G(K(M(_(((((-)?)q))))**P*b*****?+E+}++++,,M,S,,,,,;-A-y------%.,.l../3/U/Z/m/w//////////000+050F0Q00000 11_1j11122i2u222 33)3-333334 4-444s4{444445 5W5^5555546=66666E7N7j7ksmith*C:\TEMP\AutoRecovery save of Document1.asdksmithC:\Summary of qlrn toolset.docksmithC:\Summary of qlrn toolset.doc@''x}**''h7@@GTimes New Roman5Symbol3& Arial"qhY2r2r2r@-a#208Summary:ksmithksmithOh+'0  4 @ L Xdlt| Summary: ummksmith:smismi Normal.dotksmithd2miMicrosoft Word 8.0@@x) @ه@6-՜.+,D՜.+,H hp  Communications Corp.@a81  Summary: Title(RZ _PID_GUID _PID_HLINKSAN{DF511A49-39DE-11D7-9118-0050DACF5858}A uTmailto:ksmith08@comcast.netuTmailto:ksmith08@comcast.net  !"#$%&'()*+,-./012345679:;<=>?ABCDEFGHIJKLMOPQRSTUWXYZ[\]`Root Entry F`<4bData 81Table@WordDocumentnSummaryInformation(NDocumentSummaryInformation8VCompObjjObjectPool<4<4  FMicrosoft Word Document MSWordDocWord.Document.89q