Dde (1 Viewer)

[Angielah]

I have managed to run an Excel macro from within Access using the following code

XCL = DDEInitiate("Excel", "System")
DDEExecute XCL, "[Run(""pmac220502.xls!Auto"")]"
DDETerminateAll

However when I get to the DDEExecute line, my macro in Excel runs fine but an error message appears in Access stating that the DDE channel has timed out.
My macro runs for approx 20 minutes.
I have tried to alter the DDE Time Out settings using Tools.Options.Advanced but this only allows the channel to run with no response for 300 secs.

Please help, have aged 10 years
Angie

 

[Fornatian]

from your post it appears that the excel macro is fine and you only want to prevent display of the error message. if this is true you need error handling code in your procedure to deal with the specific error number.

Some thing like:

On error goto MyErrHand

'your code


Exit Sub:
End Sub


MyErrHand:

If Err.Number <> WhateverItIs Then
Msgbox "System error:" & vbcrlf &
Err.Number & vbCrlf
& Err.Description
Resume Exit Sub
End if

The WhatEverItIs can be found using a simple Msgbox Err.Description in the first instance.

 

[Angielah]

Thanks so much for this.. However there is still a problem....

My code now looks like this

On Error GoTo Main_error

Set excelworksheet = GetObject("Myfile.xls")
excelworksheet.Application.Visible = True

'More code

XCL = DDEInitiate("Excel", "System")
DDEExecute XCL, "[Run(""Myfile.xls!Auto"")]"
1 DDETerminateAll

exit sub

Main_error:

If Err.Number = 286 Then
Resume 1
End If

end sub

this above code is being called from another part of access, which is fine, all chugs along quite nicely until Access is finished and then....the code in Excel (which is running simultaneously)hangs.......