Unintentional Hijacking of Foreground XL Session

[hatman]

I am doing automated data extraction that involves populating several worksheets, then performing JOINS of the data. The final distribution will be in VB 6.0, automating a NEW background instance of Excel. During some of my testing, I am getting an error message upon execution of the SECOND join when there is already a session of Excel running on the machine. Apparently, when the Connection is opened in the first join procedure (code below), the "foreground" Excel session (if one exists) gets used to establish the connection, which essentially causes the file to be opened from it's network location in ReadOnly mode (since it's already open for editting in the background session). The result is that the changes made to the file by the background session are not visible to the foreground session, so when the second join tries to use the new worksheet populated by the first join, it can't find the worksheet at all.

This data update is slated to run automatically at midnight, using a randomly selected user's machine, which may or may not have an Excel session running when they leave for the day. Because of the complexity of some of the operations, I did NOT intend to use a foreground session. I tried setting IgnoreRemoteRequests of the foreground session to TRUE before establishing the DB connection... but that had no effect. Unless someone has advice on controlling this, I think I am stuck with intentionally using the foreground session, if it exists, and only creating a new background session if there is no foreground session...

Advice welcome.

Sub Join_PR_to_PO(WB As Excel.Workbook)
    Dim shtDest As Excel.Worksheet
    Dim shtPR_RFQ As Excel.Worksheet
    Dim shtPO As Excel.Worksheet
    Dim shtPR As Excel.Worksheet
    
    Dim objConnection As ADODB.Connection
    Dim objRecordset As ADODB.Recordset
    Dim cnt As Long
    Dim SQL As String
    
    Set objConnection = New ADODB.Connection
    Set objRecordset = New ADODB.Recordset
    
    Set shtDest = WB.Worksheets(4)
    Set shtPO = WB.Worksheets(3)
    Set shtPR = WB.Worksheets(1)
    
    shtDest.Name = "PR_JOIN_PO"
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & WB.FullName & _
            ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
   
    SQL = "SELECT * FROM [" & shtPR.Name & "$] a RIGHT JOIN [" & shtPO.Name & "$] b ON " & _
        "a.PR_Purchase_Order = b.PO_Purchasing_Document " & _
        "AND a.PR_Purchase_Order_Item = b.PO_Item"
        
    SQL = SQL & " UNION " & "SELECT * FROM [" & shtPR.Name & "$] c LEFT JOIN [" & shtPO.Name & "$] d ON " & _
        "c.PR_Purchase_Order = d.PO_Purchasing_Document " & _
        "AND c.PR_Purchase_Order_Item = d.PO_Item WHERE c.PR_Processing_status = 'Not Editted' " & _
        "OR c.PR_Processing_status = 'RFQ Created'"
          
    objRecordset.Open SQL, objConnection, adOpenStatic, adLockOptimistic, adCmdText
        
        
    For cnt = 1 To objRecordset.Fields.Count
    
        shtDest.Cells(1, cnt).Value = objRecordset.Fields(cnt - 1).Name
    
    Next cnt
        
    shtDest.Range("A2").CopyFromRecordset objRecordset
        
    objRecordset.Close
    Set objRecordset = Nothing
    
    objConnection.Close
    Set objConnection = Nothing
    
    Set shtDest = Nothing
    Set shtPR_RFQ = Nothing
    Set shtPO = Nothing
    Set shtPR = Nothing

End Sub

 

[mole999]

how about

    SQL = "SELECT * FROM [" & shtPR.Name & "$] a RIGHT JOIN [" & shtPO.Name & "$] b ON " & _
        "a.PR_Purchase_Order = b.PO_Purchasing_Document " & _
        "AND a.PR_Purchase_Order_Item = b.PO_Item" & _
" UNION " & "SELECT * FROM [" & shtPR.Name & "$] c LEFT JOIN [" & shtPO.Name & "$] d ON " & _
        "c.PR_Purchase_Order = d.PO_Purchasing_Document " & _
        "AND c.PR_Purchase_Order_Item = d.PO_Item WHERE c.PR_Processing_status = 'Not Editted' " & _
        "OR c.PR_Processing_status = 'RFQ Created'"

 

[hatman]

Problem is not in the SQL. The XL file gets opened in the foreground session when the connection is opened (not when the recordset is opened). This all works fine if there is no foreground session in existence, but unfortunately, I realized that BY DEFINITION there will always be an XL session running (the .exe gets called by the Shell() function in a (core) .xlsm file).

After some trial-and-error testing, it looks like the service that opens the connection looks for a session of Excel in which to open the file. Interestingly, if it does not find one, it does NOT spawn one... instead the code errors out when it tries to open the recordset. If there are multiple sessions, the service attaches to the one that was instantiated first. If it finds the file already open in the session, it does not try to re-open it. I was hoping that it would attach to the visible session first before looking for invisible sessions, but swapping the visible properties of the two sessions made no difference... and I don't see another way of tricking the service into attaching to the background session instead...

I wonder what happens if I run this locally in a .xlsm file... chances are that the service would use the local session to open the connection. I think I see an architecture that is plausible: the core .xlsm file calls the .exe file, which spawns a new background Excel session, and opens the .xlsm file that contains the batch data-load code, then uses the excelApp.Run method to begin that execution... when the data-load is complete, then the .exe kills the background session of Excel. This solution lacks elegance, and poses some other complications, but I think it's workable... unless someone has a better idea.