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

 

[rconnolly]

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.

Hi Ruddles,

looks promising. Bear with me while I try to grok and apply this. I'll let you know how it works out.

Cheers,
Ray

 

[rconnolly]

Ruddles,

your solution looks promising. Bear with me as I grok it and attempt to apply it to my situation. I'll let you know how it goes.

Cheers,
Ray