Searching for matching entries in Excel

[npeiman]

First, I want to use Voiceguide for what appears to be a farily simple application. I will have an Excel table that has Sales Representative Information. The table contains only 3 collums, (Sales Rep No, Phone Number, DID Number) but there may be several hundred records/sales reps. Is there a limit using Excel to 100 records?

 

Also, can you give me a sample of the proper syntax to do the following: I want to get the callerID from the inbound call and then do a dbquery to collum 2, (phone number) of the Excel table. If the phone number exists in the table then I want to pass the data in collum 3, DID to an external program.

 

Is there a mask function? For example a caller will enter either a five digit number or a 10 digit number. If the number is a 5 digit number then I want to do a look up in a differet Excel sheet. Suggestions please!

 

Thanks,

 

N Peiman

[SupportTeam]

If you just want to use one line then a single line Pro+Dialer license is what you need.

 

There is an example in VG Help file which shows how to retrieve info from Excel.

 

This script can be modified to just go through all the entries in a particular spreadsheet column until a matching entry was found... then as you know on which row the match was found you may then retrieve the values from other cells...

 

There is no limit to how many entries you can search though, or how many entries can be returned by the VB Script back to VoiceGuide.

 

The script can check the length of the value entered by caller and choose search column based on the length value.

 

Below should be close to what you need. Just replace 12345 with the Result Variable and replace MsgBoxes with actions you want taken.

Dim xlApp, xlBook, xlSht 
Dim filename, value1, value2, value3, value4

filename = "C:\SalesStaff.xls"

Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.activesheet

strValueEnteredByCaller = "12345"
if len(strValueEnteredByCaller) = 5 then
 iColumn = 1
else
 iColumn = 2
end if

'sacn through all entries until match found or a blank cell is reached
i = 1
while xlSht.Cells(i, iColumn) <> strValueEnteredByCaller and xlSht.Cells(i, iColumn) <> ""
 i = i + 1
wend

if xlSht.Cells(i, iColumn) = "" then
 msgbox "not found"
else
 msgbox xlSht.Cells(i, 3) & ", " & xlSht.Cells(i, 4)
end if

xlBook.Close False
xlApp.Quit

'always deallocate after use...
set xlSht = Nothing 
Set xlBook = Nothing
Set xlApp = Nothing