DDERequest Bad Channel - script worked fien under XP, crashes woth w7

[m.jepsen@bakker.biz]

Got a problem wiht my new PC running windows7.

Reading an excel file with the DDERequest command but keeep geeting bad channel. The script worked fine in my old PC with XP.
the command that crashes: rowcol_val = DDERequest(channel, (pos_no))


Anyone got an idea?

The code:

;-------------------------------- Sofy Data Pump ---------------------------------------
; Start Sofy and select in the menu tree the program you would like to run
; Each program has to be started in its own new window
;

;-------------------------------- Basic settings ----------------------------------------  AppTitle =("Sofy Data Pump")
  row_no = 0
  col_no = 0
; ------------------------- Needed information about Worksheet --------------------------
; This is where you enter the path and Excel file to open, wich row to begin at and
; how many columns including data
  path = "G:\Share\winbatch\notPickedUpParcels\"                 ; The path to where the Excel book is stored
  book = "SENonpickedup20140310.xls"            ; The name of the Excel book that includes the data to upload
  worksheet = "ToWinbatch"                       ; The name of the actual sheet in the Excel book that includes the data to upload
  last_col = 2                                ; Last Column to read in
  start_row = 2                                  ; Row to begin with
 
; ------------------------------- Start Excel -------------------------------------------
  a=ArrDimension(last_col+2)
  current_row = start_row
  currentwin =""
  currentwin = WinGetActive( )
  workbook = StrCat(path,book)

  Run(workbook,"")
  currentwin =""
  channel = DDEInitiate("Excel", worksheet)
 
  Winwaitexist("~Microsoft Excel",-1)
 
  WinIconize("~Microsoft Excel")                 ; Minimize Excel

; -----------------------Access Sofy and start program ---------------------------------
; Start and Select correct country

   activewin = "~Sofy32"
   Winwaitexist(activewin,-1)                      ; Waits for "Sofy"
   Winactivate(activewin)
   screen = "~Search Account"
   Winwaitexist(screen,-1)                         ; Waits for "Search Account"
   sendkeysto(screen,"{tab}")      ; Cursor starts Country first time, tab to Project
   sendkeysto(screen,"{tab}")      ; tab to AccountNo

 
 
;-------------------------------- Data Collection ---------------------------------------
:DataCollection
 
  for current_col = 0 to last_col

  row_no = strcat("R",(current_row))
  col_no = strcat("C" ,(current_col+1))
  pos_no = strcat(row_no,col_no)

  rowcol_val = DDERequest(channel, (pos_no))

[Deana]

Difficult to say what could be going on without having the exact error and the version of WinBatch you are running.

I recommend adding code to confirm the .xls file exists. See FileExist. Also please add DebugTrace(@on,DirScript():'trace.txt') to the beginning of the script and post the resulting trace file here.

[m.jepsen@bakker.biz]

Hi Deana,

Thanks for the quick respone.

The excelfile opens fine, so that's in order. The tracefile, trace.txt, where is it stored?

gr
Mats

[m.jepsen@bakker.biz]

Found it ( I had to do a little thinking...).

I have tryed the script in 2010 (for which I have the license), 11 and 14, none is working.

Here's the trace...


************************************************************

*** Debug Initialized ***

==============================
Tue 2014-03-11 15:22:59
WinBatch Studio 2014A
WIL DLL 6.14ana
G:\Share\WinBatch\Mechanized CCS-jobs\Winbatch scriptfiler\NotPickedUpParcels\NotPickedUpParcelsOnly122Letters.wbt
Windows platform: NT, version: 6.1, build: 7601 (Service Pack 1)
ErrorMode: @CANCEL
Valid Code Signature: Yes
UAC Manifest Settings: level="highestAvailable" uiAccess="true"
UAC Elevation Level: Elevated
==============================

row_no = 0
(62) VALUE INT => 0

col_no = 0
(78) VALUE INT => 0

path = "G:\Share\winbatch\notPickedUpParcels\"                 ; The path to where the Excel book is stored
(78) VALUE STRING => "G:\Share\winbatch\notPickedUpParcels\"

book = "SENonpickedup20140310.xls"            ; The name of the Excel book that includes the data to upload
(93) VALUE STRING => "SENonpickedup20140310.xls"

worksheet = "ToWinbatch"                       ; The name of the actual sheet in the Excel book that includes the data to upload
(93) VALUE STRING => "ToWinbatch"

last_col = 2                                ; Last Column to read in
(93) VALUE INT => 2

start_row = 2                                  ; Row to begin with
(109) VALUE INT => 2

a=ArrDimension(last_col+2)
(109) VALUE ARRAY => <array>

current_row = start_row
(125) VALUE INT => 2

currentwin =""
(125) VALUE STRING => ""

currentwin = WinGetActive( )
(140) VALUE UNICODE => WinBatch Studio - Unlicensed

workbook = StrCat(path,book)
(140) VALUE STRING => "G:\Share\winbatch\notPickedUpParcels\SENonpickedup20140310.xls"

Run(workbook,"")
(135611) VALUE INT => 0

TERMINAL WIL ERROR=>1932 (WinExec: Function Failed)

;;;END OF JOB;;;

---------- Begin structure stack dump ----------
  1 call                1 Call("G:\Share\WinBatch\Mecha... NotPickedUpParcelsOn
-------- End structure stack dump --------

---------- Begin WWWBATCH.INI dump ----------
[WBDNA44I]
LastError=2 (RegDeleteKey)

----------- End WWWBATCH.INI dump -----------


************************************************************

*** Debug Initialized ***

==============================
Tue 2014-03-11 15:25:19
WinBatch Studio 2014A
WIL DLL 6.14ana
G:\Share\WinBatch\Mechanized CCS-jobs\Winbatch scriptfiler\NotPickedUpParcels\NotPickedUpParcelsOnly122Letters.wbt
Windows platform: NT, version: 6.1, build: 7601 (Service Pack 1)
ErrorMode: @CANCEL
Valid Code Signature: Yes
UAC Manifest Settings: level="highestAvailable" uiAccess="true"
UAC Elevation Level: Elevated
==============================

row_no = 0
(47) VALUE INT => 0

col_no = 0
(63) VALUE INT => 0

path = "G:\Share\winbatch\notPickedUpParcels\"                 ; The path to where the Excel book is stored
(63) VALUE STRING => "G:\Share\winbatch\notPickedUpParcels\"

book = "SENonpickedup20140310.xls"            ; The name of the Excel book that includes the data to upload
(78) VALUE STRING => "SENonpickedup20140310.xls"

worksheet = "ToWinbatch"                       ; The name of the actual sheet in the Excel book that includes the data to upload
(78) VALUE STRING => "ToWinbatch"

last_col = 2                                ; Last Column to read in
(78) VALUE INT => 2

start_row = 2                                  ; Row to begin with
(94) VALUE INT => 2

a=ArrDimension(last_col+2)
(94) VALUE ARRAY => <array>

current_row = start_row
(110) VALUE INT => 2

currentwin =""
(110) VALUE STRING => ""

currentwin = WinGetActive( )
(125) VALUE UNICODE => WinBatch Studio - Unlicensed

workbook = StrCat(path,book)
(125) VALUE STRING => "G:\Share\winbatch\notPickedUpParcels\SENonpickedup20140310.xls"

Run(workbook,"")
(500) VALUE INT => 1

currentwin =""
(515) VALUE STRING => ""

channel = DDEInitiate("Excel", "worksheet") 
(593) VALUE INT => 0

Winwaitexist("~Microsoft Excel",-1)
(1108) VALUE INT => 1

WinIconize("~Microsoft Excel")                 ; Minimize Excel
(1373) VALUE INT => 1

activewin = "~Sofy32"
(1389) VALUE STRING => "~Sofy32"

Winwaitexist(activewin,-1)                      ; Waits for "Sofy"
(1904) VALUE INT => 1

Winactivate(activewin)
(1997) VALUE INT => 1

screen = "~Search Account"
(1997) VALUE STRING => "~Search Account"

Winwaitexist(screen,-1)                         ; Waits for "Search Account"
(2528) VALUE INT => 1

sendkeysto(screen,"{tab}")      ; Cursor starts Country first time, tab to Project 
(2621) VALUE INT => 0

sendkeysto(screen,"{tab}")      ; tab to AccountNo
(2715) VALUE INT => 0

for current_col = 0 to last_col
(2715) FOR TRUE==>0

row_no = strcat("R",(current_row))
(2730) VALUE STRING => "R2"

col_no = strcat("C" ,(current_col+1))
(2730) VALUE STRING => "C1"

pos_no = strcat(row_no,col_no)
(2746) VALUE STRING => "R2C1"

DDERequest(channel, (pos_no))
(1427862) VALUE INT => 0

TERMINAL WIL ERROR=>3152 (DDEReq: Bad channel number)

;;;END OF JOB;;;

---------- Begin structure stack dump ----------
  1 call                1 Call("G:\Share\WinBatch\Mecha... NotPickedUpParcelsOn
  2 for                49 for current_col = 0 to last_col  NotPickedUpParcelsOn
-------- End structure stack dump --------

---------- Begin WWWBATCH.INI dump ----------
[WBDNA44I]
LastError=2 (RegDeleteKey)

----------- End WWWBATCH.INI dump -----------

[m.jepsen@bakker.biz]

Here's a trace from version 2010.


************************************************************

*** Debug Initialized ***

==============================
Tue 2014-03-11 17:15:44
WinBatch Studio 2010A
WIL DLL 6.10aja
G:\Share\WinBatch\Mechanized CCS-jobs\Winbatch scriptfiler\NotPickedUpParcels\NotPickedUpParcels_JustTheReturn.wbt
Windows platform: NT, version: 6.1, build: 7601 (Service Pack 1)
ErrorMode: @CANCEL
==============================

row_no = 0
(31) VALUE INT => 0

col_no = 0
(46) VALUE INT => 0

path = "G:\Share\WinBatch\NotPickedUpParcels\"                 ; The path to where the Excel book is stored
(62) VALUE STRING => "G:\Share\WinBatch\NotPickedUpParcels\"

book = "SENonpickedup20140310.xls"            ; The name of the Excel book that includes the data to upload
(62) VALUE STRING => "SENonpickedup20140310.xls"

WORKSHEET = "ToWinbatch"                       ; The name of the actual sheet in the Excel book that includes the data to upload
(78) VALUE STRING => "ToWinbatch"

last_col = 2                               ; Last Column to read in
(78) VALUE INT => 2

start_row = 2                                  ; Row to begin with
(93) VALUE INT => 2

a=ArrDimension(last_col+2)
(109) VALUE ARRAY => <array>

current_row = start_row
(109) VALUE INT => 2

currentwin =""
(124) VALUE STRING => ""

currentwin = WinGetActive( )
(140) VALUE UNICODE => WinBatch Studio

workbook = StrCat(path,book)
(140) VALUE STRING => "G:\Share\WinBatch\NotPickedUpParcels\SENonpickedup20140310.xls"

Run(workbook,"")
(1076) VALUE INT => 1

currentwin =""
(1092) VALUE STRING => ""

channel = DDEInitiate("Excel", WORKSHEET)
(2028) VALUE INT => 0

Winwaitexist("~Microsoft Excel",-1)
(2542) VALUE INT => 1

WinIconize("~Microsoft Excel")                 ; Minimize Excel
(2808) VALUE INT => 1

activewin = "~Sofy32"
(2823) VALUE STRING => "~Sofy32"

Winwaitexist(activewin,-1)                      ; Waits for "Sofy"
(3338) VALUE INT => 1

Winactivate(activewin)
(3432) VALUE INT => 1

screen = "~Search Account"
(3447) VALUE STRING => "~Search Account"

Winwaitexist(screen,-1)                         ; Waits for "Search Account"
(3962) VALUE INT => 1

sendkeysto(screen,"{tab}")      ; Cursor starts Country first time, tab to Project 
(4056) VALUE INT => 0

sendkeysto(screen,"{tab}")      ; tab to AccountNo
(4149) VALUE INT => 0

for current_col = 0 to last_col
(4165) FOR TRUE==>0

row_no = strcat("R",(current_row))
(4165) VALUE STRING => "R2"

col_no = strcat("C" ,(current_col+1))
(4180) VALUE STRING => "C1"

pos_no = strcat(row_no,col_no)
(4180) VALUE STRING => "R2C1"

rowcol_val = DDERequest(channel,(pos_no))
(31137) VALUE INT => 0

TERMINAL WIL ERROR=>3152 (DDEReq: Bad channel number)

;;;END OF JOB;;;

---------- Begin structure stack dump ----------
  1 call                1 Call("G:\Share\WinBatch\Mecha... NotPickedUpParcels_J
  2 for                50 for current_col = 0 to last_col  NotPickedUpParcels_J
-------- End structure stack dump --------

---------- Begin WWWBATCH.INI dump ----------
[WBDNA44I]
LastError=2 (RegDeleteKey)
[WBDJA44I]
LastError=32 (CreateFile)
[Error Reporting]
3052=apptitle
[WBDKB44I]
LastError=32 (CreateFile)

----------- End WWWBATCH.INI dump -----------

[Deana]

All of the trace files you posted contain the line: channel = DDEInitiate("Excel", WORKSHEET)  and they all result in ZERO. This indicated that the DDEInitiate Failed.

If the communications channel cannot be opened as requested, DDEInitiate returns a channel number of 0.

Recommendations:

• Add code to check that .xls exists before launching
• if G:\ is a mapped drive, modify code to use UNC path instead.
• Add code to handle DDEInitiate Failure ( returning 0 )
• Try using ShellExecute instead of Run.
• Change script Manifest from HighestAvilable to AsInvoker. One way to accomplish this is to rename the script with the file extension .WBT_IT. See 'Manifests' topic in the WIL help file.
  

[m.jepsen@bakker.biz]

Hi Deana,

I tested what you proposed, except for checking if the excefile is there - i didn't know howto and it's opens.

I have changed the code as follows

path = "\\semaldc01\data\Share\winbatch\notPickedUpParcels\"                 ; The path to where the Excel book is stored
  book = "SENonpickedup20140310.xls"            ; The name of the Excel book that includes the data to upload
  worksheet = "ToWinbatch"                       ; The name of the actual sheet in the Excel book that includes the data to upload
  last_col = 2                                ; Last Column to read in
  start_row = 2                                  ; Row to begin with
 
; ------------------------------- Start Excel -------------------------------------------
  a=ArrDimension(last_col+2)
  current_row = start_row
  currentwin =""
  currentwin = WinGetActive( )
  workbook = StrCat(path,book)

  ShellExecute(workbook,"","",@normal,"")
  currentwin =""
  channel = DDEInitiate("Excel", "worksheet")
     If channel == 0 ;If channel = 0 DDE NOTOK stop
      Pause('','DDEIntiate NOT OK')
      Exit
   EndIf


I'm using the full UNC-path, using ShellExecute, save the file with the extension WBT-IT. I still not getting any proper DDEIntiate.

Here's the tracefile

************************************************************

*** Debug Initialized ***

==============================
Wed 2014-03-12 09:48:51
WinBatch Studio 2014A
WIL DLL 6.14ana
G:\Share\WinBatch\Mechanized CCS-jobs\Winbatch scriptfiler\NotPickedUpParcels\NotPickedUpParcelsOnly122Letters.wbt_it
Windows platform: NT, version: 6.1, build: 7601 (Service Pack 1)
ErrorMode: @CANCEL
Valid Code Signature: Yes
UAC Manifest Settings: level="highestAvailable" uiAccess="true"
UAC Elevation Level: Elevated
==============================

row_no = 0
(266) VALUE INT => 0

col_no = 0
(266) VALUE INT => 0

path = "\\semaldc01\data\Share\winbatch\notPickedUpParcels\"                 ; The path to where the Excel book is stored
(281) VALUE STRING => "\\semaldc01\data\Share\winbatch\notPickedUpParcels\"

book = "SENonpickedup20140310.xls"            ; The name of the Excel book that includes the data to upload
(281) VALUE STRING => "SENonpickedup20140310.xls"

worksheet = "ToWinbatch"                       ; The name of the actual sheet in the Excel book that includes the data to upload
(297) VALUE STRING => "ToWinbatch"

last_col = 2                                ; Last Column to read in
(297) VALUE INT => 2

start_row = 2                                  ; Row to begin with
(297) VALUE INT => 2

a=ArrDimension(last_col+2)
(312) VALUE ARRAY => <array>

current_row = start_row
(312) VALUE INT => 2

currentwin =""
(328) VALUE STRING => ""

currentwin = WinGetActive( )
(328) VALUE UNICODE => WinBatch Studio - Unlicensed

workbook = StrCat(path,book)
(344) VALUE STRING => "\\semaldc01\data\Share\winbatch\notPickedUpParcels\SENonpickedup20140310.xls"

ShellExecute(workbook,"","",@normal,"")
(905) VALUE INT => 1

currentwin =""
(905) VALUE STRING => ""

channel = DDEInitiate("Excel", "worksheet") 
(952) VALUE INT => 0

If channel == 0 ;If channel = 0 DDE NOTOK stop
(952) IF DO==>TRUE

Pause('','DDEIntiate NOT OK')
(21981) VALUE INT => 1

Exit
(22121) VALUE INT => 0

--- Normal termination ---

;;;END OF JOB;;;


I have tested both in 2014 and 2010 with the same negative result. Any new Ideas?

gr
Mats

[Deana]

Based on the trace file, I see you ran in WinBatch studio, which ignored the Manifest extension.

First open the properly manifested version of the WBStudio: C:\Program Files (x86)\WinBatch\System\WBStudio_IT.exe
Open NotPickedUpParcelsOnly122Letters.wbt_it  and run it.
Post the resulting trace file.

IMPORTANT: You are currently running WinBatch as a 32-bit process. Do you have the 32-bit version of Excel installed?

[m.jepsen@bakker.biz]

Hi Deana,

Yes I'm running 32bit excel 2003.

Here's the tracing file.

I done some research on internet, it seems to be some problems with DDE and win7, there is some stockbrokers retrieving data from NYSE and Nasdaq via DDE, and they are not happy... quite irritated on Microsoft doing nothing.

OLE - could that be the solution for me? Not that I know how to do it but it's better to spend time on that than wasting it on the DDE, what do you think?


gr
MAts








************************************************************

*** Debug Initialized ***

==============================
Wed 2014-03-12 17:27:40
WinBatch Studio 2010A
WIL DLL 6.10aja
G:\Share\WinBatch\Mechanized CCS-jobs\Winbatch scriptfiler\NotPickedUpParcels\NotPickedUpParcelsOnly122Letters.wbt_it
Windows platform: NT, version: 6.1, build: 7601 (Service Pack 1)
ErrorMode: @CANCEL
==============================

row_no = 0
(250) VALUE INT => 0

col_no = 0
(250) VALUE INT => 0

path = "\\semaldc01\data\Share\winbatch\notPickedUpParcels\"                 ; The path to where the Excel book is stored
(266) VALUE STRING => "\\semaldc01\data\Share\winbatch\notPickedUpParcels\"

book = "SENonpickedup20140310.xls"            ; The name of the Excel book that includes the data to upload
(281) VALUE STRING => "SENonpickedup20140310.xls"

worksheet = "ToWinbatch"                       ; The name of the actual sheet in the Excel book that includes the data to upload
(281) VALUE STRING => "ToWinbatch"

last_col = 2                                ; Last Column to read in
(297) VALUE INT => 2

start_row = 2                                  ; Row to begin with
(297) VALUE INT => 2

a=ArrDimension(last_col+2)
(312) VALUE ARRAY => <array>

current_row = start_row
(328) VALUE INT => 2

currentwin =""
(328) VALUE STRING => ""

currentwin = WinGetActive( )
(344) VALUE UNICODE => WinBatch Studio

workbook = StrCat(path,book)
(359) VALUE STRING => "\\semaldc01\data\Share\winbatch\notPickedUpParcels\SENonpickedup20140310.xls"

ShellExecute(workbook,"","",@normal,"")
(1794) VALUE INT => 1

currentwin =""
(1794) VALUE STRING => ""

channel = DDEInitiate("Excel", "worksheet") 
(2528) VALUE INT => 0

If channel == 0 ;If channel = 0 DDE NOTOK stop
(2543) IF DO==>TRUE

Pause('','DDEIntiate NOT OK')
(6443) VALUE INT => 1

Exit
(6459) VALUE INT => 0

--- Normal termination ---

;;;END OF JOB;;;

[Deana]

Difficult to say what could be going on.

After your ShellExecute you might add code to wait for the Excel window to appear. See WinWaitExist.

I can confirm that I successfully tested DDE and Excel on Windows 7.

Details of my test:

• Win7 64-bit - Admin login
• WinBatch 2014A
• Office 2010 64-bit

Notes: Doesn't seem to matter which Manifest is used. Or the bitness of the WinBatch.exe.


Code:

Code (winbatch) Select Expand


retvalue = 1
DDETimeout(50000)
;***************************************************************************
;**
;**   Test DDE on Windows 7
;**
;***************************************************************************
if RegExistValue(@RegClasses,"excelworksheet\protocol\stdfileediting\server")
   xlpath=RegQueryValue(@RegClasses,"excelworksheet\protocol\stdfileediting\server")
   ;Run(xlpath, "/E")
ShellExecute(xlpath, "/E", "", @NORMAL, "")
   ;;;;;;;;; if WinState("Microsoft Excel")!=@NORMAL then WinShow("Microsoft Excel")
WinWaitExist(`Microsoft Excel`, 30)
   WinPlace(517,540,997,996,`Microsoft Excel`)
   p_channel = DDEInitiate("excel", "System")
   If p_channel
      DDEEXECUTE(p_channel,"[NEW(1)]")
      DDETerminate(p_channel)

      p_channel2 = DDEInitiate("excel" , "sheet1")
      item = "R1C1"
      DDEPoke(p_channel2 , "R1C1" , "AARGH")
      DDEExecute(p_channel2,"[SELECT(""%item%"")][CLEAR(1)][FORMULA(""Abraham"")]")
      output1 = StrReplace(DDERequest(p_channel2,"R1C1"), @crlf , "")
      DDEPoke(p_channel2 , "R1C2" , "Siegfried")
      DDEPoke(p_channel2 , "R1C3" , "Zippo")
      output2 = StrReplace(DDERequest(p_channel2,"R1C2"), @crlf , "")
      item= "R1C3"
      DDEExecute ( p_channel2, "[SELECT(""%item%"",""%item%"")]" )
      DDEExecute(p_channel2,"[FORMULA(""Siegfried"" )]")
      output3 = StrReplace(DDERequest(p_channel2 ,"R1C3"), @crlf , "" )
      DDEPoke(p_channel2 , "R1C3" , " and Roy")
      output4 = StrReplace ( DDERequest ( p_channel2 , "R1C3" ) , @crlf , "")
      output5 = StrCat( output3 , output4 )
      DDETerminate(p_channel2)

      p_channel3 = DDEInitiate("excel", "System")
      If p_channel3
         DDEEXECUTE(p_channel3,"[FILE.CLOSE(""FALSE"")][QUIT()]")
         ; Quit breaks DDE Conversation DDETerminate(p_channel)
         Timedelay(1)
         DDETerminate(p_channel3)

         If output1 != "Abraham" Then retvalue = 0
         If output2 != "Siegfried" Then retvalue = 0
         If output3 != "Siegfried" Then retvalue = 0
         If output5 != "Siegfried and Roy" Then retvalue = 0
      else
         retvalue = 0
      endif
   else
      retvalue = 0
   endif
endif

If WinExist("~Microsoft Excel") Then WinClose("~Microsoft Excel")

Pause('Result',retvalue)


[m.jepsen@bakker.biz]

Hello Deana

Been extremly busy for some days, but this afternoon I spent some hours on my case - and I did it! At leats until I got the error, 3138: DDEInitiate: Too many DDE conversations.

Is there a parameter or so to solve this?

gr
Mats

[Deana]

You should always close channels with DDETerminate when you are finished using them. After multiple script failures and no DDETerminate call, you could leave open DDE channels, a reboot can resolve this. In the future you may want to add code to capture errors ( see IntControl 73) after a successfully opened channel, and then call DDETerminate in the error handler.