Need help acessing info in excel

[sonic67]

I apologize but I'm new at writing scripts, can some one help or just point me at least in the right direction?

I'm trying to make a script that will pull information from more than one cell in excel and some how insert them as a variable and repeat until it hits a blank cell and then end. Did I also mention that the excel spread sheet name will change from time to time.

The reason I'm trying to automate this is because it can be hundreds values at a time and I'm really not that fast at typing please help.

Any help will be appreciated
Thanks,

Sample of what I'm trying to do:

proc main
transmit "ed-t0::cot-6-1-4::::,,,0,,,,,,,,,,,,,0 ;^M";Retrieve a variable from excel and insert it for example 6-1-4.

transmit "dlt-t0::cot-6-1-4;" ; Retrieve a variable from excel and insert it for example 6-1-4.

transmit "ed-t0::cot-6-1-4::::,,,1124,,,,,,,,,,,,,2;^M" ; Retrieve a variable from excel and insert it for example 6-1-4 and 1124.

transmit "ed-t0::cot-6-1-4;"; Retrieve a variable from excel and insert it for example 6-1-4.
;repeat until a blank cell in spreadsheet.
endproc

 

[knob]

I have a few scripts on the samples page of my site that show how to use DDE to communicate with Excel that should at least get you started.

http://www.aspectscripting.com

 

[kodr]

Here is a modified bit of code. The DDE stuff is directly cut and pasted from samples on Knob's site. I can't test this myself, but here's what I put together.

Excel has to be open, and your values have to be in the first column only.

The last value on your list needs to be "end" (no quotes.)

;**************************************
; DDE code derived from samples at
; [URL unfurl="true"]www.aspectscripting.com[/URL]
;**************************************

;**************************************
; Declare DDE variables.
;**************************************

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

;**************************************
; Main procedure below
;**************************************

proc main

string s1st, s2nd, s3rd, s4th, s5th, s6th, s7th, s8th

s1st = "ed-t0::cot-"
s2nd = "::::,,,0,,,,,,,,,,,,,0 ;^M"

s3rd = "dlt-t0::cot-"
s4th = ";^M"

s5th = "ed-t0::cot-"
s6th = "::::,,,1124,,,,,,,,,,,,,2;^M"

s7th = "ed-t0::cot-"
s8th = "";^M"



;**************************************
; Code here for DDE input
;**************************************

   iCol1 = 1                                    ;Column one
   iRow = 1                                     ;Start reading with row 1
   
   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  ;Format request for data from current row, column 1
            dderequest LinkVar sRowCol szText   ;Read data from spreadsheet, current row, column 1
   	strreplace szText "`n" "" 
   	strreplace szText "`r" "" 
            if strcmp szText "end"	        ;If cell contains the string exit
               exitwhile                        ;Exit the while loop.
            else
               strcat s1st szText
               strcat s1st s2nd
               transmit s1st
               
               strcat s3rd szText
               strcat s3rd s4th
               transmit s3rd
               
               strcat s5th szText
               strcat s5th s6th
               transmit s5th
               
               strcat s7th szText
               strcat s7th s8th
               transmit s7th
               
            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


;**************************************
; End DDE code
;**************************************


endproc

 

[sonic67]

Thank you so much Kodr I haven’t tried it yet but it looks like it will work well. I hope this isn’t too much to ask but is their a way to make the DDE code pull information from a second column in excel for the line s6th = "::::,,,1124,,,,,,,,,,,,,2;^M" and replace the 1124 with the information from the second column?

Thanks
Greg

 

[kodr]

Yeah, shouldn't be too difficult.

Look at modifying this variable:

iCol1 = 1

strfmt sRowCol "R%dC%d" iRow iCol1  
dderequest LinkVar sRowCol szText   
strreplace szText "`n" ""
strreplace szText "`r" "" 

strfmt sRowCol "R%dC%d" iRow iCol2
dderequest LinkVar sRowCol szText2
strreplace szText2 "`n" ""
strreplace szText2 "`r" ""

In the above code I added a new variable iCol2, which you would assign your column number (in your case iCol2 = 2), and the value of which would be assigned to the variable szText2. You can then manipulate it anyway you want.

 

[sonic67]

Kodr:

Sorry for being a pain. When I try to compile the script it comes with a token error I think this is due to the second column variable that is not right. Can you take a look and tell me what I'm doing wrong?

Error message :
Error C023 Line 56: Invalid expression token: ICOL2
Error C023 Line 57: Invalid expression token: SZTEXT2
Error C022 Line 57: Missing token
Error C023 Line 58: Invalid expression token: SZTEXT2
Error C163 Line 58: Invalid string variable
Error C022 Line 58: Missing token
Error C023 Line 59: Invalid expression token: SZTEXT2
Error C163 Line 59: Invalid string variable
Error C022 Line 59: Missing token

Code:
;**************************************
; DDE code derived from samples at
;

http://www.aspectscripting.com

;**************************************

;**************************************
; Declare DDE variables.
;**************************************

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

;**************************************
; Main procedure below
;**************************************

proc main

string s1st, s2nd, s3rd, s4th, s5th, s6th, s7th, s8th, s9th

s1st = "ed-t0::cot-"
s2nd = "::::,,,0,,,,,,,,,,,,,0 ;^M"

s3rd = "dlt-t0::cot-"
s4th = ";^M"

s5th = "ed-t0::cot-"
s6th = "::::,,," ;Value from second column
s7th = ",,,,,,,,,,,,,2;^M"

s8th = "ed-t0::cot-"
s9th = "";^M"



;**************************************
; Code here for DDE input
;**************************************

iCol1 = 1 ;Column one
iRow = 1 ;Start reading with row 1

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 ;Format request for data from current row, column 1
dderequest LinkVar sRowCol szText ;Read data from spreadsheet, current row, column 1
strreplace szText "`n" ""
strreplace szText "`r" ""

strfmt sRowCol "R%dC%d" iRow iCol2 ;Format request for data from current row, column 2.
dderequest LinkVar sRowCol szText2 ;Read data from spreadsheet, current row, column 2
strreplace szText2 "`n" ""
strreplace szText2 "`r" ""
if strcmp szText "end" ;If cell contains the string exit
exitwhile ;Exit the while loop.
else
strcat s1st szText
strcat s1st s2nd
transmit s1st

strcat s3rd szText
strcat s3rd s4th
transmit s3rd

strcat s5th szText
strcat s5th s6th
transmit s5th

strcat s7th szText
strcat s7th s8th
transmit s7th

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

;**************************************
; End DDE code
;**************************************


endproc
Once again thank you.
Greg

 

[knob]

Hi Greg, you need to define a string variable named szText2 and an integer variable named iCol2 at the beginning of your script and that will clear up the compile errors.

http://www.aspectscripting.com

 

[kodr]

Sorry about that, I wasn't clear on my answer.

Is everything working now?

 

[sonic67]

Thank you for asking, I played with for a wile but I'm still having problems.
I guess I don't understand what I'm doing wrong because still getting the same error code that I described before.

 

[knob]

Did you define the two new variables at the begining of your script? That should take care of the compile errors.

http://www.aspectscripting.com