Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IMPORT DATA FROM EXCEL???

Status
Not open for further replies.
mailbox125

mailbox125

Technical User
Apr 1, 2003
44
US
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

 
Sort by date Sort by votes
The following example should work (untested). NOTE: This example assumes that the Excel spreadsheet is currently open and the sheet you are refering to is named sheet1.
Code: 
string Passw, Pos
integer Row, Col
long SystemVar, LinkVar

proc Getpass
   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 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
         Col=1, Row=1, Passw = " "              ; Set Start Pos and Password
         while not nullstr Passw                ; Loop till cell is blank
            strfmt Pos "R%iC%i" Row Col         ; Request cell to read
            dderequest LinkVar Pos Passw        ; Extract cell to Passw
            Waitquiet 1
            Transmit Passw
            Waitfor "@"
            Transmit "L F^M"
            Waitfor "@"
            Row++                               ; Increment the row
         endwhile         
      endif
   else
      errormsg "Couldn't establish DDE link to Excel!"
      halt
   endif
endproc
PS: The extracted cell contents will always have a carriage return at the end of it. Therefore, you do not need to transmits a "^M" after transmitting Passw. If you need to strip the C/R, you could use the command;
Code: 
strreplace Passw "`r`n" ""
 
Upvote 0 Downvote
Sorry, a bug in the last script. You will need to strip the C/R from the extracted cell contents to check if cell is blank. See the ammended code below.

Code: 
string Passw, Pos
integer Row, Col
long SystemVar, LinkVar

proc Getpass
   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 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
         Col=1, Row=1, Passw = " "              ; Set Start Pos and Password
         while not nullstr Passw                ; Loop till cell is blank
            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"
            Waitfor "@"
            Transmit "L F^M"
            Waitfor "@"
            Row++                               ; Increment the row
         endwhile         
      endif
   else
      errormsg "Couldn't establish DDE link to Excel!"
      halt
   endif
endproc
 
Upvote 0 Downvote
Thanks alot I should be able to complete this now again thanks for your time.
 
Upvote 0 Downvote
Thanks again for your help.

I tried to copy the example that you gave me and that part worked great but I need to also now set the capture file. I thought that I could use the same module to then grab the capture name and setit as well however I'm finding that when I complile it complains about duplicate strings???

If you have any suggestions I would sure like the help.



;PWHostsXfer.WAS v1.0

;* *
;* 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 1 ;put number of places to call here
#DEFINE ENTRY1 "9,624-9727,,,,,,###5" ;holds first number
#DEFINE ENTRY2 "9,727-3199,,,,,,###5" ;holds second number
#DEFINE ENTRY3 "9,903-8224,,,,,,###5" ;holds third number
#DEFINE TIMEINTERVAL 1 ;can be 1 or 2 hours


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

string NumberToDial ;holds number to dial


;*****************************************************************************
;* *
;* 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(CurCall) ;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
endfor
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
param integer CurCall

switch CurCall ;retrieves the info on number to call
case 1 ;first number
NumberToDial = Entry1 ;set the number to dial
endcase
case 2 ;second number
NumberToDial = Entry2 ;set the number to dial
endcase
case 3 ;third number
NumberToDial = Entry3 ;set the number to dial
endcase
endswitch

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 *
;* *
;*****************************************************************************

string Passw, Pos
integer Row, Col
long SystemVar, LinkVar

proc Getpass
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
Row++ ; Increment the row


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

Waitfor "@PASSWORD:"
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
Col=1, Row=1, Passw = " " ; Set Start Pos and Password
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"
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 *
;* *
;*****************************************************************************

string CapLog, Pos
integer Row, Col
long SystemVar, LinkVar

Proc CapLogFile
Clear
Capture Off
Row++ ; Increment the row


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

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 sheet1"
else
Col=1, Row=1, Passw = " " ; Set Start Pos and Password
strfmt Pos "R%iC%i" Row Col ; Request cell to read
dderequest LinkVar Pos CapLog ; Extract cell to Passw
strreplace CapLog "`r`n" "" ; Remove C/R from Passw
Waitquiet 1
Set Capture to = CapLog
Transmit "^M"
endif
else
errormsg "Couldn't establish DDE link to Excel!"
halt
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"
Waitfor "ENTER DIGIT PATTERN. :"
Transmit "ALL^M"
Waitquiet 10
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l r 6^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l r 4^M"
Waitfor "ENTER DIGIT PATTERN. :"
Transmit "ALL^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l r 5^M"
Waitquiet 10
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l p^M"

Endproc
 
Upvote 0 Downvote
Hello,

Use the following. It might help.

Set Capture Path "C:\Files"
Set Capture File "Caplog.txt"
Set Capture Overwrite OFF ;* Don't Overwrite
Set Capture Query OFF ;* Don't Prompt for Capture Query

Capture On
......
Capture Off

HAnk
 
Upvote 0 Downvote
The problem line is:

Set Capture to = CapLog

This likely needs to be changed to:

Set Capture CapLog

You will need to make sure that you assign the string CapLog the path and name of the file you want the information saved to (it did not look like this was done in your script).
aspect@aspectscripting.com
 
Upvote 0 Downvote
You are getting the duplicate strings errors because you have assigned a string variable Pos, integer variables of Row & Col and long variables of SystemVar & LinkVar just before your Getpass procedure and then again before your CapLogFole procedure. These should only be assigned once and probably best to assign them at the beginning of the script (somewhere above your main procedure).

Your other problem is as knob suggested.
[ol]Set Capture to = CapLog[/ol]
should be changed to
[ol]Set Capture File CapLog[/ol]

One last thing, one thing I forgot with the first example I gave you. You should terminate both the ddeexecute and ddeinit command once completed in order to drop the dde links established to the worksheet and Excel.
ie ddeterminate LinkVar & ddeterminate SystemVar

Because you have the 2 procedures accessing Excel, you should't have to establish the system link to Excel the second time (in the CapLogFile proc). You should have your Getpass procedure establish the system link to Excel (ddeexecute SystemVar "[FULL(TRUE)]") and to sheet1 (ddeinit LinkVar "excel" "sheet1"), perform your tasks and then terminate the link to sheet1 (ddeterminate LinkVar). Then in your CapLogFile procedure, establish the link to sheet2 (ddeinit LinkVar "excel" "sheet2"), perform your tasks, terminate link to sheet2 (ddeterminate LinkVar) and then finally terminate the link to Excel (ddeterminate SystemVar).
 
Upvote 0 Downvote
Thanks I'll try that I need to make one more of these links because I currently have the system numbers hard coded in the script I'm going to need to move them also to an Excel sheet. As long as all the data I need is in the same Excel doc I should be able to refrance shhet1 sheet 2 and sheet 3?? Unfortunatly I have to go out of town for a whiel but I will work on this when I get back THANK you as you can probably tell I'm new to Aspect and I'm have'n a hard time with the syntax. THANKS agian for your help, I'm sure I'll have more Q's.
 
Upvote 0 Downvote
I tried moving the strings for string Passw, Pos integer Row, Col &long SystemVar, LinkVar into the global Variables and out of the CapLog module but now I'm getting several errors DIAL-REPORT-1.WAS:
Error C023 Line 183: Invalid expression token: LINK
Error C023 Line 183: Invalid expression token: VAR
Error C156 Line 183: Invalid long
Error C023 Line 214: Invalid expression token: LINK
Error C023 Line 214: Invalid expression token: VAR
Error C157 Line 214: Invalid long variable
Error C022 Line 214: Missing token
Error C023 Line 221: Invalid expression token: CAPLOG
Error C102 Line 221: Missing operand
Error C110 Line 221: Invalid operand
Error C023 Line 223: Invalid expression token: CAPLOG
Error C022 Line 223: Missing token
Error C023 Line 224: Invalid expression token: CAPLOG
Error C163 Line 224: Invalid string variable
Error C022 Line 224: Missing token
Error C023 Line 226: Invalid expression token: CAPLOG
Error C166 Line 226: Invalid number or string
Error C165 Line 226: Invalid integer or string
Error C023 Line 233: Invalid expression token: LINK
Error C023 Line 233: Invalid expression token: VAR

have I missed something duh!

one more Q did I use the dde commands that you suggested correctly?

Thanks for all your help.











;PWHostsXfer.WAS v1.0

;*****************************************************************************
;* *
;* 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 1 ;put number of places to call here
#DEFINE ENTRY1 "9,624-9727,,,,,,###5" ;holds first number
#DEFINE ENTRY2 "9,727-3199,,,,,,###5" ;holds second number
#DEFINE ENTRY3 "9,903-8224,,,,,,###5" ;holds third number
#DEFINE TIMEINTERVAL 1 ;can be 1 or 2 hours


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

string NumberToDial ;holds number to dial

string Passw, Pos
integer Row, Col
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(CurCall) ;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
endfor
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
param integer CurCall

switch CurCall ;retrieves the info on number to call
case 1 ;first number
NumberToDial = Entry1 ;set the number to dial
endcase
case 2 ;second number
NumberToDial = Entry2 ;set the number to dial
endcase
case 3 ;third number
NumberToDial = Entry3 ;set the number to dial
endcase
endswitch

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
Clear
Capture Off
Row++ ; Increment the row


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

Waitfor "@PASSWORD:"
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
Col=1, Row=1, Passw = " " ; Set Start Pos and Password
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"
endif
else
errormsg "Couldn't establish DDE link to Excel!"
halt
endif
Ddeterminate Link Var
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
Clear
Capture Off


Set Capture Path "C:\Files"
Set Capture File "Caplog.txt"
Set Capture Overwrite OFF ;* Don't Overwrite
Set Capture Query OFF ;* Don't Prompt for Capture Query

Row++ ;* Increment the row

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

Ddeinit Link Var "excel" "sheet2"
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
Col=1, Row=1, CapLog = " " ; Set Start Pos and CapLog
strfmt Pos "R%iC%i" Row Col ; Request cell to read
dderequest LinkVar Pos CapLog ; Extract cell to CapLog
strreplace CapLog "`r`n" "" ; Remove C/R from CapLog
Waitquiet 1
Set Capture File CapLog
Transmit "^M"
endif
else
errormsg "Couldn't establish DDE link to Excel!"
halt
endif
Ddeterminate Link Var
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"
Waitfor "ENTER DIGIT PATTERN. :"
Transmit "ALL^M"
Waitquiet 10
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l r 6^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l r 4^M"
Waitfor "ENTER DIGIT PATTERN. :"
Transmit "ALL^M"
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l r 5^M"
Waitquiet 10
Waitfor "READY PRINTER, THEN PRESS RETURN..."
Transmit "^M"
Waitfor "@"
Transmit "l p^M"

Endproc
 
Upvote 0 Downvote
The errors on line 183, 214, and 233 are due to the script having Link Var as the name of the long variable instead of Linkvar (no spaces). The other errors are due to the string CapLog and integers Row and Col not being defined in that procedure or elsewhere in the script.


aspect@aspectscripting.com
 
Upvote 0 Downvote
Thanks for your help I have gotten this to work sort of it does open a file in c:\reportfile with the name it received from sheet 2 of the excel however it doesn't wright anything to it.

Any suggestions?????


;*****************************************************************************
;* *
;* 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
Clear
Capture Off



Set Capture Path "C:\ReportFiles"


Set Capture Overwrite OFF ;* Don't Overwrite
Set Capture Query OFF ;* Don't Prompt for Capture Query

Row++ ;* Increment the row

Ddeinit LinkVar "excel" "system"
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
Row=1, Col=1, CapLog = " " ; Set Start Pos and CapLog
strfmt Pos "R%iC%i" Row Col ; Request cell to read
dderequest LinkVar Pos CapLog ; Extract cell to CapLog
strreplace CapLog "`r`n" "" ; Remove C/R from CapLog
Waitquiet 1
Set Capture File CapLog
Transmit "^M"
endif
else
errormsg "Couldn't establish DDE link to Excel!"
halt
endif
Ddeterminate LinkVar
Endproc
 
Upvote 0 Downvote
I haven't had a chance to look this over yet, but there are a couple DDE/Excel samples on my site that Hank sent me.
It looks to me like your script is retrieving data instead of sending it to the file with the ddepoke command. I'll try to look this over in more depth Monday morning.


aspect@aspectscripting.com
 
Upvote 0 Downvote
It took me a while to try and figure out exactly what your problem is. I will explain how I have interpretted your question and tell me if I have mis-understood.

From what I could gather, you are trying to extract the capture file name from the spreadsheet. Then once it is set using Set Capture File CapLog, you transmit a C/R to try and capture the incoming data to that capture file.

If I have interpretted your question correctly :), then the answer is that you have set the capture file, but not turned it on. This may be rectified using someting the following code.

Code: 
Set Capture File CapLog
capture on
Transmit "^M"
waitquiet 3
capture off

If I have misinterpretted your question :(, you will need to give me more details about what your script does.
 
Upvote 0 Downvote
PS: As for the ddeterminate command, it wasn't used quite as I explained. I went over your code and found another couple of areas where you may experience problems. Below is your code re-written. Hopefully, it should suit your needs.

Code: 
;PWHostsXfer.WAS   v1.0
;*****************************************************************************
;*                                                                           *
;* 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 1                ;put number of places to call here
#DEFINE ENTRY1 "9,624-9727,,,,,,###5"    ;holds first number
#DEFINE ENTRY2 "9,727-3199,,,,,,###5"    ;holds second number
#DEFINE ENTRY3 "9,903-8224,,,,,,###5"    ;holds third number
#DEFINE TIMEINTERVAL 1                 ;can be 1 or 2 hours



;*****************************************************************************
;*   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(CurCall)               ;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
   endfor
   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
param integer CurCall
   switch CurCall                      ;retrieves the info on number to call
      case 1                           ;first number
         NumberToDial = Entry1         ;set the number to dial
      endcase
      case 2                           ;second number
         NumberToDial = Entry2         ;set the number to dial
      endcase
      case 3                           ;third number
         NumberToDial = Entry3         ;set the number to dial
      endcase
   endswitch
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
    Clear
    Row++                                       ; Increment the row
     
;************* Load Text File *****************************************

    Waitfor "@PASSWORD:"
    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 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
    Clear
    Set Capture Path "C:\Files"
    Set Capture Overwrite OFF               ;* Don't Overwrite
    Set Capture Query OFF                   ;* Don't Prompt for Capture Query

;   Commented out the following as row has already 
;   been increment by GetPass procedure
;   Row++                                   ;* Increment the row                         
     
;************* Load Text File *****************************************

    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 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 3
        capture off
    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"
    Waitfor  "ENTER DIGIT PATTERN.   :"
    Transmit "ALL^M"
    Waitquiet 10
    Waitfor  "READY PRINTER, THEN PRESS RETURN..."
    Transmit "^M"
    Waitfor  "@"
    Transmit "l r 6^M"
    Waitfor  "READY PRINTER, THEN PRESS RETURN..."
    Transmit "^M"
    Waitfor  "@"
    Transmit "l r 4^M"
    Waitfor  "ENTER DIGIT PATTERN.   :"
    Transmit "ALL^M"
    Waitfor  "READY PRINTER, THEN PRESS RETURN..."
    Transmit "^M"
    Waitfor  "@"
    Transmit "l r 5^M"
    Waitquiet 10
    Waitfor  "READY PRINTER, THEN PRESS RETURN..."
    Transmit "^M"
    Waitfor  "@"
    Transmit "l p^M"
Endproc
 
Upvote 0 Downvote
Thanks guy's I'll try these changes and see if they resolve the issues I've been having. I do have one other Q that is how do I get the script to wait longer in the reports section the issue is that some of the reports can take up to an hour to run and report back to the screen and the srcipt seems to time out and stop. How do I get the script to wait that long for a respnoce?
 
Upvote 0 Downvote
Probably what is happening is that you are running into the default 30-second timeout of the waitfor command. You can specify a different timeout value by placing a value in seconds after the string value (i.e. waitfor "string" 60 for a one-minute timeout). You could also use FOREVER instead of a timeout value to have the waitfor command wait until the specified string appears. This may work the best for your environment.


aspect@aspectscripting.com
 
Upvote 0 Downvote
If I use the FOREVER and the system I'm logged onto doesn't return the preprogrammed responce?? the script will just hang? If I define the time as 1000 sec and the system returns data before? the script will wait the defined 1000 sec?
 
Upvote 0 Downvote
If the system does not transmit the string you are waiting for when using the FOREVER flag, then yes the script would pause forever. If you specified a timeout of 1000 seconds and the string appeared after say 500 seconds, then the waitfor command would complete and execution would continue to the next command.

One other command you may want to look into is the when target command. This command looks for a particular text string, and calls an associated procedure whenever that string is received. You can have several when target commands active at one time - you just define them at the beginning of your script and they each execute when the assigned string appears. This may or may not work better than the waitfor, it just depends on your individual situation.


aspect@aspectscripting.com
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TNieland
  • Locked
  • Question
Procomm capture script stopped capturing data
Replies
0
Views
184
TNieland
TNieland
Zafferg
  • Locked
  • Question
Excel 2007 DDE command send to PW5 Procomm
Replies
1
Views
201
knob
knob
kayl99
  • Locked
  • Question
Updating Cisco routers. Need script to read from a separate list. 2
Replies
4
Views
154
knob
knob
esoare
  • Locked
  • Question
In a Aspect Script, can you "Find" in Excel and then use the info from that Row?
Replies
2
Views
128
Firebird Scrambler
Firebird Scrambler
Solinus
  • Locked
  • Question
Help writing to file - Pass data from dialogue box
Replies
4
Views
110
knob
knob

Part and Inventory Search

Sponsor

Back
Top