Issue referencing a newly created workbook

[rconnolly]

Hello all,

I have the following issue that I would really appreciate some help on:

I have a third party application (AB) that generates an output that can be exported to an excel workbook. The workbook is automatically named "Book1.xlsx". I would like to manipulate "Book1.xlsx" from a macro in another workbook "Study_Control.xlsm". So AB creates "Book1.xlsx", its open and has not yet been saved. It is at this point in the process, I'll call it [1], that I would like to run a macro in "Study_Control.xlsm" to manipulate "Book1.xlsx". My problem is this: "Book1.xlsx" is not added to the Workbooks collection until after it is saved, closed and then reopened. I have confirmed that this is the case by iterating the Workbooks collection before and after "Book1.xlsx" is saved, closed and then reopened. Before saving, closing and reopening I get a "subscript out of range" error, after saving, closing and reopening "Book1.xlsx" it is a member of the collection an can therefore be manipulated.

Is there any way for me to reference "Book1.xlsx" at [1] as defined above? Manual manipulation of "Book1.xlsx" is not an option as this process needs to be repeated many thousands of times.

Thanks for your help,
Regards Ray

 

[Ruddles]

Apparently you can use DDE to access the open workbook - Google using excel as a dde server.

I say 'apparently' because I've never done it, although I've used Access as a DDE server.

I'm going to give it a go now...

Let me know if you beat me to it?

 

[Ruddles]

Forget that stuff about DDE! Can't you just reference the workbook/sheet/cell directly if it's open?

Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = Workbooks("Book1")
Set wks = wkb.Sheets("Sheet1")

MsgBox wks.Cells(3, 4)

 

[rconnolly]

Forget that stuff about DDE! Can't you just reference the workbook/sheet/cell directly if it's open?

Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = Workbooks("Book1")
Set wks = wkb.Sheets("Sheet1")

MsgBox wks.Cells(3, 4)

No none of those references work until such time as the workbook is a member of the workbooks collection as far as I've been able to determine through my experimentation. I guess I may have to start digging into the windows API and see there is a solution going that route or I may take the brute force easy way out and use a windows automation product like Macro Scheduler...I was hoping for a simple native excel solution though.

Ray

 

[Ruddles]

Ah! It does work when both workbooks are open in the same instance of Excel but not when they're in distinct instances.

Back to DDE then... I'll let you know if I get it working. (I've got it working using Access as the DDE server.)

 

[rconnolly]

Ah! It does work when both workbooks are open in the same instance of Excel but not when they're in distinct instances.

Back to DDE then... I'll let you know if I get it working. (I've got it working using Access as the DDE server.)

Thx R, I appreciate the effort...I will start digging into the DDE approach.

Ray

 

[Ruddles]

Ah! It does work when both workbooks are open in the same instance of Excel but not when they're in distinct instances.

I've drawn a blank on the DDE approach: there seems to be very little on the Web about it other than "it's possible" and only one code snippet (which I can't get to work).

How about switching to the application window where Book1.xlsx is open and opening Study_Control.xlsm in the same window? It should be able to see it then.

 

[Ruddles]

Right, I can put stuff into the newly-created Book1 (or Book1.xlsx):-

Option Explicit
Public Sub Testing()
 
  Dim iChan As Integer
 
  iChan = DDEInitiate("Excel", "[[COLOR=red][B]Book1[/B][/COLOR]]Sheet1")
  DDEPoke iChan, "R1C1:R20C1", ThisWorkbook.ActiveSheet.Range("C1:C20")
  DDETerminate iChan
 
End Sub

Now to see if I can get stuff out... I can't believe there's so little stuff on the Web about this!

 

[Ruddles]

And this is how to get stuff out of the other workbook. (I don't know whether any of this helps you!)

Option Explicit
 
Public Sub GetViaDDE()
'
' get a one-dimensional range
'
  Dim iChan As Integer
  Dim msg As Variant
  Dim i As Integer
 
  iChan = DDEInitiate("Excel", "[Book1]Sheet1")
 
  msg = DDERequest(iChan, "R1C3:R1C7")
 
 ' If IsArray(msg) Then
 '   For i = LBound(msg) To UBound(msg)
 '     MsgBox msg(i)
 '   Next i
 ' End If
 
  ThisWorkbook.ActiveSheet.Range("E13:I13") = msg
 
  ThisWorkbook.ActiveSheet.Range("E16:E20") = Application.Transpose(msg)
 
  DDETerminate iChan
 
End Sub
 
Public Sub GetViaDDE_2D()
'
' get a one-dimensional range
'
  Dim iChan As Integer
  Dim msg As Variant
  Dim i As Integer
  Dim j As Integer
 
  iChan = DDEInitiate("Excel", "[Book1]Sheet1")
 
  msg = DDERequest(iChan, "R1C3:R10C7")
 
 ' If IsArray(msg) Then
 '   For i = LBound(msg, 1) To UBound(msg, 1)
 '     For j = LBound(msg, 2) To UBound(msg, 2)
 '       MsgBox msg(i, j)
 '     Next j
 '   Next i
 ' End If
 
  ThisWorkbook.ActiveSheet.Cells(1, 5).Resize(UBound(msg, 1), UBound(msg, 2)).Value = msg
 
  DDETerminate iChan
 
End Sub

This gives some more info - run it in an empty workbook as it used columns A-C:-

Option Explicit
 
Public Sub DDE_Info()
 
  Dim iChan As Integer
  Dim i As Integer
  Dim RequestItems As Variant
 
  iChan = DDEInitiate("Excel", "System")
 
  Columns("A:C").ClearContents
 
  RequestItems = DDERequest(iChan, "SysItems")
  Cells(1, 1) = "SysItems"
  For i = LBound(RequestItems) To UBound(RequestItems)
    Cells(i + 1, 1) = RequestItems(i)
  Next i
 
  RequestItems = DDERequest(iChan, "Formats")
  Cells(1, 2) = "Formats"
  For i = LBound(RequestItems) To UBound(RequestItems)
    Cells(i + 1, 2) = RequestItems(i)
  Next i
 
  RequestItems = DDERequest(iChan, "Topics")
  Cells(1, 3) = "Topics"
  For i = LBound(RequestItems) To UBound(RequestItems)
    Cells(i + 1, 3) = RequestItems(i)
  Next i
 
  DDETerminate iChan
 
End Sub

There's supposed to be a "Help" topic but I can't coax anything out of it.

 

[GTO]

Hi Ruddles,

By chance and due to wanting to communicate with an emulator, I've recently been through some pain with DDE. While Excel supports the commands, it seems to botch results. I do not have examples handy, but thus far, I found it FAR better to set a reference to WORD and run the DDE thru it.

@ Ray:

"I have a third party application (AB) that generates an output that can be exported to an excel workbook. The workbook is automatically named "Book1.xlsx". I would like to manipulate "Book1.xlsx" from a macro in another workbook "Study_Control.xlsm". So AB creates "Book1.xlsx", its open and has not yet been saved. "

Unfortunately, I'm probably logging out in a few, but "cat-killin' " curiousity has the best of my tired self. If the new wb shows a name of Whatever.xlsx, it would seem to me that it is saved. Am I missing something?

I am also (probably just missed this) wondering if you look under Window in the new wb, is it the only one listed? Is it the 3rd party software that is creating the extra instance?

Mark