IMPORT DATA FROM EXCEL???

[mailbox125]

Here is the script I'm working on, I need this module to get the first line from an Excel spreadsheet and transmit it to the system I'm logged onto. The Excel spreadsheet will just be a list of passwords to be incremented by 1 for every system logged onto. Am I going about this all wrong I already have scripts for each of these sites however they require manual input to get to the Excel DB also. Help How do I get this automated I'd like to start this script before I go home and have it outcall and log into several systems without manual input. Any help would be great.

;*****************************************************************************
;* *
;* Getpass() *
;* The procedure Getpass performs a call to an Excel db for the password *
;* to loggon to the remote system. *
;* *
;* Calls: NONE *
;* Called by: MAIN *
;* Modifies globals: NONE *
;* *
;*****************************************************************************

String Passwfile = " "

Proc Getpass

String F_line
String Passw
Clear
Capture Off
Set Capture File "Current-System.TXT"
Set Capture Overwrite On

Transmit "Set dnldpath `"c:\Passw\Passw.txt`" ^M" ;*Enter Path to Text

;************* Load Text File *****************************************

Waitfor "@PASSWORD:"

If Fopen 0 Passwfile READ TEXT;
While not feof 0
Fgets 0 F_line
Strextract Passw F_line "," 0
Waitquiet 1
Transmit Passw
Transmit "^M"
Waitfor "@"
Transmit "L F^M"
Waitfor "@"
Endwhile
Fclose 0
Else
Errormsg "Invalid Dir File '%s'" Passwfile
Endif

Endproc

 

[mailbox125]

Hi I'm still having issues with the capture file the script will create the file however it doesn't write to it or is it because the script hasn't completed? I'm looking at the file while the script is still running.

 

[knob]

I believe you will see this if the file is still open and hasn't been closed with the fclose command.


aspect@aspectscripting.com

http://www.aspectscripting.com

 

[mailbox125]

Thanks That did the trick!!

 

[mailbox125]

Hey Guys I like useing the Excel and I'd like to get the NumberToDial from the first sheet I have treid this. However the system doesn't dial the number is this because in the last script the system was dialing from the Getnumber module or do I need to put back the switch? then the case? I'm getting very close to my goal with all your help THANK YOU.






;*****************************************************************************
;* GLOBAL VARIABLES *
;*****************************************************************************

string NumberToDial ;holds number to dial
string Passw,Pos,CapLog
integer Row=0,Col=1
Long SystemVar,LinkVar


;*****************************************************************************
;* *
;* MAIN *
;* The Main procedure calls GetNumber to get the phone number to dial, calls *
;* PlaceCall to dial the number, calls Setmodem to set modem to 7,e,1, Calls *
;* Getpass to get the password from a Excel DB, calls RunReports to collect *
;* data from each system, call to CapLog to move data to report files *
;* *
;* Calls: GETNUMBER, PLACECALL, PERFORMCMDS, PRINTLOG *
;* Modifies globals: NONE *
;* *
;*****************************************************************************

proc main
GetNumber() ;gets the current number to dial
PlaceCall() ;calls procedure to make calls
SetModem() ;SETS THE MODEM TO 7,E,1
GetPass() ;Gets password from Excel DB
CapLogFile() ;Moves collected data the reports file
RunReports() ;Collects the reports infromation
set autodnld ON ;this will reset setting set earlier

endproc

;*****************************************************************************
;* *
;* GETNUMBER(CURCALL) *
;* The procedure GetNumber determines the phone number to call. *
;* *
;* NOTE: This procedure will need to be modified to add numbers to call. *
;* This is done simply by adding an additional case statement. For *
;* example: case 4 *
;* NumberToDial = Entry4 *
;* endcase *
;* The Entry4 would need to be defined in the Macro section. *
;* *
;* Calls: NONE *
;* Called by: MAIN *
;* Modifies globals: NUMBERTODIAL *
;* *
;*****************************************************************************

proc GetNumber
Clear
Capture Off
Row++ ; Increment the row


if ddeinit SystemVar "excel" "system" ; Initialize link to Excel
ddeexecute SystemVar "[FULL(TRUE)]" ; Maximize the spreadsheet.
if not ddeinit LinkVar "excel" "sheet1" ; Set up link to spreadsheet.

errormsg "Couldn't establish DDE link to spreadsheet sheet2"
else
strfmt Pos "R%iC%i" Row Col ; Request cell to read
dderequest LinkVar Pos Passw ; Extract cell to NumberToDial
strreplace NumberToDial "`r`n" "" ; Remove C/R from NumberToDial
Ddeterminate LinkVar
endif
else
errormsg "Couldn't establish DDE link to Excel!"
halt
endif
endproc


;*****************************************************************************
;* *
;* PLACECALL() *
;* The procedure PlaceCall dials remote system and loops while dialing. *
;* *
;* Calls: NONE *
;* Called by: MAIN *
;* Modifies globals: NUMBERTODIAL *
;* *
;*****************************************************************************

proc PlaceCall

dialnumber DATA NumberToDial ;dials the number
while $DIALING ;loops while dialing
endwhile

endproc

 

[mailbox125]

All right I got the script to retrieve the number from excel and dial however I have lost the loop to the next number it's like the script is missing the return to main to get the next system number. Any suggestions??

 

[knob]

Is your script getting the value correctly? Try adding:

usermsg "xxx%sxxx" NumberToDial

just before the dialnumber command. The x characters are to help determine if there are any unprintable characters in the string retrieved from the spreadsheet. Does the name in the string exactly match the entry in the Connection Directory? Actually, since you are using dialnumber the string should just be a phone number. Is that what you have in the spreadsheet?




aspect@aspectscripting.com

http://www.aspectscripting.com

 

[mailbox125]

Yes in the spreadsheet I have sheet 1 is a list of phone numbers sheet 2 is a list of corresponding passwords then in sheet 3 I have the name of the capture file I want created.

 

[knob]

Does the usermsg command I mentioned in my last post display the expected value of NumberToDial, or does it display an incorrect value? I haven't been following all of this thread, so I'm not certain where you are in being able to successfully retrieve data from the spreadsheet.


aspect@aspectscripting.com

http://www.aspectscripting.com

 

[mailbox125]

Thanks for checking on me. I have been able to get the information from the spreadsheet however it's not getting incremented correctly. The script does display the correct number to dial and does dial it correctly however when the second number comes up the script will also dial it correctly but the sheet 2 and sheet 3 have not been moved correctly so it issues an incorrect password, The script also doesn't seem to run back through the main I think, If you have the time you could look at this Ive treid the Row ++ everywhere I can think of with little succsess. Thanks for the help I'm almost there!!




;*****************************************************************************
;* *
;* PWHostsXfer.WAS *
;* Copyright (C) 1998 Quarterdeck *
;* All rights reserved. *
;* *
;* Written by: Quarterdeck ASPECT Assist *
;* 12/19/97 *
;* *
;* This script file calls multiple locations and transfers/receives files. *
;* This script assumes that all the Hosts are running Procomm Plus Hosts and *
;* accept Remote ASPECT commands. The script also prints the Connection log. *
;* *
;*****************************************************************************


;*****************************************************************************
;* MACRO DEFINITIONS *
;*****************************************************************************

#DEFINE NUMBEROFCALLS 100

;*****************************************************************************
;* GLOBAL VARIABLES *
;*****************************************************************************

string NumberToDial ;holds number to dial
string Passw,Pos,CapLog
integer Row=0,Col=1
Long SystemVar,LinkVar


;*****************************************************************************
;* *
;* MAIN *
;* The Main procedure calls GetNumber to get the phone number to dial, calls *
;* PlaceCall to dial the number, calls Setmodem to set modem to 7,e,1, Calls *
;* Getpass to get the password from a Excel DB, calls RunReports to collect *
;* data from each system, call to CapLog to move data to report files *
;* *
;* Calls: GETNUMBER, PLACECALL, PERFORMCMDS, PRINTLOG *
;* Modifies globals: NONE *
;* *
;*****************************************************************************

proc main
Integer CurCall

For CurCall = 1 upto NumberOfCalls
GetNumber() ;gets the current number to dial
PlaceCall() ;calls procedure to make calls
SetModem() ;SETS THE MODEM TO 7,E,1
GetPass() ;Gets password from Excel DB
CapLogFile() ;Moves collected data the reports file
RunReports() ;Collects the reports infromation
set autodnld ON ;this will reset setting set earlier

Endfor

endproc

;*****************************************************************************
;* *
;* GETNUMBER(CURCALL) *
;* The procedure GetNumber determines the phone number to call. *
;* *
;* NOTE: This procedure will need to be modified to add numbers to call. *
;* This is done simply by adding an additional case statement. For *
;* example: case 4 *
;* NumberToDial = Entry4 *
;* endcase *
;* The Entry4 would need to be defined in the Macro section. *
;* *
;* Calls: NONE *
;* Called by: MAIN *
;* Modifies globals: NUMBERTODIAL *
;* *
;*****************************************************************************

proc GetNumber
Clear

if ddeinit SystemVar "excel" "system" ; Initialize link to Excel
ddeexecute SystemVar "[FULL(TRUE)]" ; Maximize the spreadsheet.
if not ddeinit LinkVar "excel" "sheet1" ; Set up link to spreadsheet.

errormsg "Couldn't establish DDE link to spreadsheet sheet1"
else

strfmt Pos "R%iC%i" Row Col ; Request cell to read
dderequest LinkVar Pos NumberToDial ; Extract cell to NumberToDial
strreplace NumberToDial "`r`n" "" ; Remove C/R from NumberToDial
Ddeterminate LinkVar
endif
else
errormsg "Couldn't establish DDE link to Excel!"
halt
endif
endproc


;*****************************************************************************
;* *
;* PLACECALL() *
;* The procedure PlaceCall dials remote system and loops while dialing. *
;* *
;* Calls: NONE *
;* Called by: MAIN *
;* Modifies globals: NUMBERTODIAL *
;* *
;*****************************************************************************

proc PlaceCall

dialnumber DATA NumberToDial ;dials the number
while $DIALING ;loops while dialing
endwhile

endproc

;****************************************************************************
;* *
;* SetModem *
;* The procedure to set the modem to 7,e,1 *
;* *
;* Calls: None *
;* Called by: MAIN *
;* Modifies: Modem Settings *
;* *
;*****************************************************************************

proc SetModem

pause 1
SET PORT DATABITS 7
mspause 250
SET PORT PARITY EVEN
mspause 250

endproc

;*****************************************************************************
;* *
;* Getpass() *
;* The procedure Getpass performs a call to an Excel db for the password *
;* to loggon to the remote system. *
;* *
;* Calls: NONE *
;* Called by: MAIN *
;* Modifies globals: NONE *
;* *
;*****************************************************************************


proc Getpass


Waitfor "@PASSWORD:"
if ddeinit SystemVar "excel" "system" ; Initialize link to Excel
ddeexecute SystemVar "[FULL(TRUE)]" ; Maximize the spreadsheet.
if not ddeinit LinkVar "excel" "sheet2" ; Set up link to spreadsheet.

errormsg "Couldn't establish DDE link to spreadsheet sheet2"
else

strfmt Pos "R%iC%i" Row Col ; Request cell to read
dderequest LinkVar Pos Passw ; Extract cell to Passw
strreplace Passw "`r`n" "" ; Remove C/R from Passw
Waitquiet 1
Transmit Passw
Transmit "^M"
Ddeterminate LinkVar
endif
else
errormsg "Couldn't establish DDE link to Excel!"
halt
endif
Endproc

;*****************************************************************************
;* *
;* CapLogFile() *
;* This procedure will setup the capture file for all systems logged onto *
;* and place it into it's own file. *
;* *
;* Calls: ? *
;* Called by:MAIN *
;* Modifies globals: NONE *
;* *
;*****************************************************************************

Proc CapLogFile

Set Capture Path "C:\ReportFiles"
Set Capture Overwrite OFF ;* Don't Overwrite
Set Capture Query OFF ;* Don't Prompt for Capture Query

if not ddeinit LinkVar "excel" "sheet3" ; Set up link to spreadsheet.

errormsg "Couldn't establish DDE link to spreadsheet sheet3"
else
Row ++
strfmt Pos "R%iC%i" Row Col ; Request cell to read
dderequest LinkVar Pos CapLog ; Extract cell to CapLog
ddeterminate LinkVar
ddeterminate SystemVar
strreplace CapLog "`r`n" "" ; Remove C/R from CapLog
Waitquiet 1
Set Capture File CapLog
Capture On
Transmit "^M"
Waitquiet 1
endif
Endproc

;*****************************************************************************
;* *
;* RunReports() *
;* This procedure will run all the requested reports like but limited to *
;* List Features, Disk Report, Ports report Ect... *
;* *
;* Calls: NONE *
;* Called by: MAIN *
;* Modifies globals: NONE *
;* *
;*****************************************************************************


Proc RunReports

Transmit "l h^M"
Waitfor "@"
Transmit "l r 1^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l r 8^M"
Waitquiet 1
Transmit "ALL^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@" forever
Transmit "l r 6^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l r 4^M"
Waitquiet 1
Transmit "ALL^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@" Forever
Transmit "l r 5^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@" Forever
Transmit "l p^M"
Waitquiet 1
Transmit "^M^M^M"
Waitquiet 1

Endproc

 

[knob]

Move the Row++ command in proc CapLogFile to after the waitquiet 1 command. It looks to me like you are incrementing this variable before you request the value from sheet 3 instead of after you have requested the value.


aspect@aspectscripting.com

http://www.aspectscripting.com

 

[mailbox125]

I tried what you have suggested however wihtout the row ++ above the main somewhere the script doesn't find the number to dial, if I move the row ++ to the main then the script will dial the number. How do I make this loop on the main, can't I use an If not eof ( refering to the excell file ) else end on the main, because the first run through works correctly however the second time as we descussed before doesn't incremment the excell file correctly and I'm not going through the script again to set the modem again. This is great fun and I'm learning alot thanks again for your time.

 

[knob]

Where do you have the Row++ statement at the moment? It looks to me like it should be OK at the end of the main procedure since execution will return to main after all of the procedures called by it have finished execution (as long as you have not made more than 100 connections, since NUMBEROFCALLS is set to 100).

There is not a way for Procomm to tell when we are at the end of the Excel spreadsheet (at least not that I am aware of, I could be wrong on this), so we can't check for EOF. However, what you could do is add a string to sheet 1, such as EOF, and the script could check the value of NumberToDial with this code:

if strnicmp NumberToDial "EOF" 3
exit
endif

Adding this code to the GetNumber procedure (after you have retrieved the number from Excel) will cause the script to stop running if EOF is read from the spreadsheet. You could also call a different procedure if you needed to perform some cleanup (such as logging out of the remote system), and then use the exit command in that procedure to close the script.



aspect@aspectscripting.com

http://www.aspectscripting.com

 

[mailbox125]

The only place I currently have the row ++ is twice in the main once above the main and once at the end of the main inoder to get it to move on the next time through. This seems to work however when the script runs through the main the second time it doesn't go through the setmodem again because the second systems modem settings are 8,n,1 instead of the 7,e,1 that it should be set to in the setmodem module. Is there a reason why the CapLogFile won't open the first file but will open the second??

;*****************************************************************************
;* *
;* MAIN *
;* The Main procedure calls GetNumber to get the phone number to dial, calls *
;* PlaceCall to dial the number, calls Setmodem to set modem to 7,e,1, Calls *
;* Getpass to get the password from a Excel DB, calls RunReports to collect *
;* data from each system, call to CapLog to move data to report files *
;* *
;* Calls: GETNUMBER, PLACECALL, SETMODEM, GETPASS, CAPLOG, & RUNREPORTS *
;* Modifies globals: NONE *
;* *
;*****************************************************************************

proc main
Integer CurCall
Row ++
For CurCall = 1 upto NumberOfCalls ;will loop until NumberOfCalls is reached
Waitquiet 1
GetNumber() ;gets the current number to dial from Excel
PlaceCall() ;calls procedure to make calls
SetModem() ;SETS THE MODEM TO 7,E,1
GetPass() ;Gets password from Excel DB
CapLogFile() ;Moves collected data the reports file
RunReports() ;Collects the reports infromation
Row ++
set autodnld ON ;this will reset setting set earlier

Endfor

endproc

;*****************************************************************************
;* *
;* CapLogFile() *
;* This procedure will make a call to the Excell DB sheet3 to get the name *
;* for the capture file, and write all report information into the clients *
;* own file. *
;* *
;* Calls: ? *
;* Called by:MAIN *
;* Modifies globals: NONE *
;* *
;*****************************************************************************

Proc CapLogFile

Set Capture Path "C:\ReportFiles"
Set Capture Overwrite OFF ;* Don't Overwrite
Set Capture Query OFF ;* Don't Prompt for Capture Query

if not ddeinit LinkVar "excel" "sheet3" ; Set up link to spreadsheet.

errormsg "Couldn't establish DDE link to spreadsheet sheet3"
else
strfmt Pos "R%iC%i" Row Col ; Request cell to read
dderequest LinkVar Pos CapLog ; Extract cell to CapLog
ddeterminate LinkVar
ddeterminate SystemVar
strreplace CapLog "`r`n" "" ; Remove C/R from CapLog
Waitquiet 1
Set Capture File CapLog
Capture On
waitquiet 1
endif
Endproc

 

[knob]

Do you have a capture off statement in your script to close the current capture file before you open the next connection? I saw some capture off statements in earlier versions of your script, but didn't see it in the last couple messages you posted. I haven't tested this theory, but the lack of the capture off command may cause problems when you try to open a second capture file (or even set the name of the capture file).


aspect@aspectscripting.com

http://www.aspectscripting.com

 

[mailbox125]

I set the capture off at the end of the RunReports module and that seems to be working when it was in the Main the capture file was in part writen on the preceding capture file with it at the end of the reports this issue seems to be corrected. I still can't find the cause of the script not setting the modem to 7,e,1 after the first one is this something I should set as a global? Can I define that in the global variables??

 

[knob]

Do you want the modem to be set to E71 for all connections? If so, I would recommend setting this in Windows instead of having the script set it for each connection. I don't have a modem on my work machine so I can't give you the exact steps, but open the Modems applet in Control Panel, select your modem, and I think look under the Advanced tab.


aspect@aspectscripting.com

http://www.aspectscripting.com

 

[knob]

I forgot to mention in my last message, is the script now getting the right values from the spreadsheet or is it still having problems? If it's not working correctly, you can email me your current script and the spreadsheet (edit out whatever information you feel necessary) and I'll give it a look.


aspect@aspectscripting.com

http://www.aspectscripting.com