How can I setup my DDE destination to any open/active excel worksheet?

[raabd1]

I'm hoping to get some help with the language needed to change from a "named worksheet" destination to an "active worksheet" of any excel doc that is open. Is that possible?

Below is the code I want to edit. (where it says Send the data to XL "sheet1" I want it to be any active sheet of an open excel doc. Thanks everyone.


------------------------------------------------------------------------

Sub Per_Area_Sum_To_XL()
Dim Cell_Holder As Integer, My_File_Name As String * 255, Sum_PA(10) As Single
Dim Name_Cell As String, Data_Cell As String, Cell_Data As String

'******************** Set INI File to store Cell number ***************
IpIniFile(GETINT,"LastCell",Cell_Holder)
If Cell_Holder <3 Then
Cell_Holder = 3
IpIniFile(SETINT,"LastCell",Cell_Holder)
End If
'**********************************************************************

'******************* Get Data Points and set up cell locations ********
IpDocGetStr(INF_NAME, DOCSEL_ACTIVE, My_File_Name)
IpBlbGet(GETSTATS, 0, BLBM_PERAREA, Sum_PA(0))
Name_Cell = "R"&CStr(Cell_Holder)&"C3"
Data_Cell = "R"&CStr(Cell_Holder+1)&"C4"
'**********************************************************************

'******************* Send the data to XL ******************************
ret=IpDde(DDE_SET, "target", "C:\Program Files\Microsoft Office\Office11\EXCEL.EXE")
ret=IpDde(DDE_OPEN, "Excel","sheet1")
IpDde(DDE_PUT, Name_Cell, Trim(My_File_Name))
IpDde(DDE_PUT, Data_Cell, CStr(Sum_PA(5)))
IpDde(DDE_CLOSE,"","")
'*********************************************************************

'******************* Increment INI file ******************************
Cell_Holder = Cell_Holder +1
IpIniFile(SETINT,"LastCell",Cell_Holder)
'*********************************************************************

End Sub

 

[SkipVought]

Hi,

You may be making this much too complex...

dim wb as workbook

for each wb in workbooks
   with wb
      if .name <> thisworkbook.name then
         'now we have the REFERENCE of another open workbook
         with .activesheet
            'this is a reference to the activesheet in the referenced workbook
         end with
      end if
   end with
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]