excel - Code error when called from another workbook

Question

I have written some code to link 3 workbooks that I use on a daily basis and automate some information transfer between the 3. The first contains information that is copied and pasted into the second in a relevant format by some code I have written. Within this second sheet there is some code, written by someone much more advanced than me who has now left, which reformats the information and pastes it into a third workbook. When run independently this macro in the second workbook works perfectly. However when I try to call it using my own code (stored in the first workbook) it doesn't work properly. This is where I think it is going wrong: (this is the codes in the second workbook which should create the third workbook)

Public Sub ExportOpenlinkDeals()

...

Set objWbk = ThisWorkbook
Set objWS = objWbk.Worksheets("SwapTrades")

Set objWbkOut = Application.Workbooks.Add
Set objWSOut = objWbkOut.Worksheets(1)

SetOpenLinkHeader objWSOut

If I stop my code before it calls this macro from the second workbook and press the button for the above macro. If I ask my code to run it it doesn't create a new workbook but instead overwrites the headers in the 2nd workbook with the ones that should go in the third (ie performs SetOpenlinkHeader on the workbook that contains the macro being called rather than in a new one, which it just doesn't seem to create).

I call the macro that includes the above code using Application.Run "Workbookname.xls!Subname".

In case it is relevant SetOpenlinkHeader is a Private Sub and the code I use to call this (called Hedge if that helps communication) is just declared as Sub not public or private.

The code for SetOpenlinkHeader is:

Private Sub SetOpenLinkHeader(objWS As Worksheet)
With objWS
    .Range("A1").Value2 = "Type"
    .Range("B1").Value2 = "Trader id"
    .Range("C1").Value2 = "Trader location"
    .Range("D1").Value2 = "Chain"
    .Range("E1").Value2 = "Buy/sell"
    .Range("F1").Value2 = "Grade"
    .Range("G1").Value2 = "Internal Legal entity"
    .Range("H1").Value2 = "Month"
    .Range("I1").Value2 = "Year"
    .Range("J1").Value2 = "Put/Call"
    .Range("K1").Value2 = "Strike"
    .Range("L1").Value2 = "Quantity Lots Required"
    .Range("M1").Value2 = "Order type"
    .Range("N1").Value2 = "Executing broker"
    .Range("O1").Value2 = "Clearing broker"
    .Range("P1").Value2 = "Quantity Lots Filled"
    .Range("Q1").Value2 = "Price"
    .Range("R1").Value2 = "Electronic market flag"
    .Range("S1").Value2 = "EFP Deal Reference"
    .Range("T1").Value2 = "External legal entity"
    .Range("U1").Value2 = "Cross Entity Flag"
    .Range("V1").Value2 = "Balmo Day"
    .Range("W1").Value2 = "Trad Date"
    .Range("X1").Value2 = "UTI"
End With
End Sub

I have tried making the private sub public, changing ThisWorkbook to the specific workbook name and changing the order of the syntax around creating the new workbook,none of which have changed the result - please help!

I can't put a copy of this anywhere for confidentiality reasons but will provide more info on request!

Answer 1

OK well here is what I can tell you based on my attempt to replicate your problem.

I created two files: workbook1.xlsm and workbook2.xlsm

In workbook1, I have the following code in a standard module, Module1

This code opens the workbook2 and calls the ExportOpenLinkDeals procedure from workbook2.

Sub Hedge()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("c:usersdavid_zemensdesktopworkbook2.xlsm")

Application.Run wb2.Name & "!ExportOpenLinkDeals"

End Sub

In workbook2, I have the following code in a standard module, Module1

This is the ExportOpenLinkDeals procedure called from workbook1. This procedure also calls your SetOpenLinkHeader which is also in workbook2.

Sub ExportOpenLinkDeals()
Dim objWbk As Workbook
Dim objWbkOut As Workbook
Dim objWS As Worksheet
Dim objWSOut As Worksheet

    Set objWbk = ThisWorkbook  '## Workbook2
    Set objWS = objWbk.Worksheets(3)

    Set objWbkOut = Application.Workbooks.Add
    Set objWSOut = objWbkOut.Worksheets(1)

    SetOpenLinkHeader objWSOut
End Sub
Private Sub SetOpenLinkHeader(objWS As Worksheet)
With objWS
    MsgBox IIF(.Parent.Name=ThisWorkbook.Name, "error!", "success!")
    .Range("A1").Value2 = "Type"
    .Range("B1").Value2 = "Trader id"
    .Range("C1").Value2 = "Trader location"
    .Range("D1").Value2 = "Chain"
    .Range("E1").Value2 = "Buy/sell"
    .Range("F1").Value2 = "Grade"
    .Range("G1").Value2 = "Internal Legal entity"
    .Range("H1").Value2 = "Month"
    .Range("I1").Value2 = "Year"
    .Range("J1").Value2 = "Put/Call"
    .Range("K1").Value2 = "Strike"
    .Range("L1").Value2 = "Quantity Lots Required"
    .Range("M1").Value2 = "Order type"
    .Range("N1").Value2 = "Executing broker"
    .Range("O1").Value2 = "Clearing broker"
    .Range("P1").Value2 = "Quantity Lots Filled"
    .Range("Q1").Value2 = "Price"
    .Range("R1").Value2 = "Electronic market flag"
    .Range("S1").Value2 = "EFP Deal Reference"
    .Range("T1").Value2 = "External legal entity"
    .Range("U1").Value2 = "Cross Entity Flag"
    .Range("V1").Value2 = "Balmo Day"
    .Range("W1").Value2 = "Trad Date"
    .Range("X1").Value2 = "UTI"
End With
End Sub

This code works as expected, and the MsgBox informs me that a new book has been added and the header row data has been written in that new workbook.

This code works. How does it differ from your implementation? Answer that, and you will find your problem.