Reading from Excel

[TMurdach]

What are the script steps needed to read several rows of two column four-digit numbers?

I currently have a script that prompts for a 4-digit extension, then it prompts for a 2nd 4-digit extension. It takes those 2 numbers and programs an extension and alias on the Octel 250.

I have an excel spreadsheet of about 500 sets of extensions that need programming and I was hoping to automate as much as possible, rather than typing them all in manually.

 

[knob]

You can use DDE to transfer information between Excel and Procomm. I have some example script on my site that show how to do this with Excel.


aspect@aspectscripting.com

http://www.aspectscripting.com

 

[TMurdach]

Thanks for responding to my post. I must admit, I am a novice level scripter but can usually break things apart and take what I need. Unfortunately, I don't know what parts to steal from the scripts on your site. My script below is used on the Octel 250. It is very simple. It asks for input twice and puts them into variables.

The part I can't figure out with importing info from Excel is, how to take data from the first row, columns 1 and 2, and paste that into the variables I currently use. Then, go down to the next row and repeat the process.



proc main
call main5
endproc
proc main5
string old1
string new1
sdlginput "Ext Change" "Enter Extension To Change" old1
if failure
goto stopnow
endif
sdlginput "Ext Change" "Enter New Extension" new1
if failure
goto stopnow
endif
transmit "9"
waitfor "9"
transmit "^M"
transmit "1"
transmit "^M"
transmit old1
transmit "^M"
transmit "^M"

transmit "^M"
transmit "^M"
transmit "^M"
transmit "0"
transmit "^H^M"
transmit "^M"

transmit "^M"
transmit "2"
transmit "^M"
transmit old1
transmit "^M"
transmit new1
transmit "^M"
transmit "^M"
transmit "y^M"
transmit "^M"
transmit "^M"
transmit "9"
transmit "^M"
transmit "1"
transmit "^M"
transmit new1
transmit "^M"
transmit "^M"

transmit "^M"
transmit old1
transmit "^M"
transmit "^M"

transmit "^C"
transmit "^C"
call loop
stopnow:
endproc
proc loop
call main
endproc

Any help would be appreciated. Thanks!!!

 

[knob]

Here is a script that reads columns 1 and 2 from an open Excel spreadsheet and places the information in the string variable szText (you might want to change the second dderequest command to use a different variable if you'll need to access both pieces of information at the same time). The script reads from the spreadsheet until the string exit is found in column 1 of the row being accessed.

proc main
long LinkVar, SystemVar ; Variables containing DDE Id's.
string szText ; Text read from DDE link to Excel.
integer iRow, iCol1, iCol2 ; Row and column variables
string sRowCol ; Holds request for row and column

iCol1 = 1 ; Initialize variables
iCol2 = 2
iRow = 1

; Set up DDE links to Excel's system and a spreadsheet. Excel
; must be running in order for this script to work properly.
if ddeinit SystemVar "excel" "system"
ddeexecute SystemVar "[FULL(TRUE)]" ; Maximize the spreadsheet.
if ddeinit LinkVar "excel" "sheet1" ; Set up link to spreadsheet.
while 1 ; Loop forever.
strfmt sRowCol "R%dC%d" iRow iCol1
dderequest LinkVar sRowCol szText ; Read data from spreadsheet, current row, column 1
if stricmp szText "exit`r`n"
exitwhile ; Exit the while loop.
else
strfmt sRowCol "R%dC%d" iRow iCol2
dderequest LinkVar sRowCol szText ; Read data from spreadsheet, current row, column 2
endif
iRow++ ; Increment row value
endwhile
ddeterminate LinkVar ; Break DDE link to spreadsheet.
ddeterminate SystemVar ; Break DDE link to Excel.
else
errormsg "Couldn't establish DDE link to spreadsheet!"
endif
else
errormsg "Couldn't establish DDE link to Excel!"
endif
endproc

aspect@aspectscripting.com

http://www.aspectscripting.com

 

[TMurdach]

Great tips! Thank you.

I just ran into a snag, which is: I don't have another licensed copy of MS Office to install on this old PC I am using as a terminal.

How can I grab the info from a text file [consisting of several lines of comma separated 4-digit extensions - 2 extensions per line]?

 

[knob]

Here is a quick example of how to open a file:

proc main
string sLine, sTemp1, sTemp2, sTemp3

fopen 0 "foo.txt" READ TEXT ;Open our data file
while not feof 0 ;While the file still has data
fgets 0 sLine ;Read a line of data
endwhile
fclose 0
endproc

After the fgets command, you would do whatever was necessary to get the data in the proper format. Some commands you might use would be substr, strtok, or strextract.


aspect@aspectscripting.com

http://www.aspectscripting.com