Controlling NEW xlapp when Excel was already open

[WhatTheFrick]

Using Access 2007, Excel 2007, database and Excel files are 2003 versions (compatibility mode).



Everything I'm doing works great as long as Excel was not previously open when running this code. For some reason, if Excel is open before I run my code, and even though I set xlapp to be a NEW Excel application (which works: if I set xlapp to visible, I can see BOTH Excel windows), I get two versions of the workbook I opened, one is Read Only, and the ImportRange is correct in one but offset by one row in the other, and Access is importing the named range from the workbook where the range is off by one.

I create a new Excel object, open a workbook, find a value, use that location to set my named range, then I import the named range into an Access table:

public xlapp as excel.application
Public wbCurrent As excel.Workbook
Public shtData As worksheet
Set xlApp = CreateObject("Excel.Application") 'I've also tried using  = New excel.Application - they both work
With xlApp
    Set wbCurrent = .Workbooks.Open(ExcelPathAndFile, , False, , pw)
    'wbCurrent.Activate - tried this, didn't work
    Set shtData = wbCurrent.Sheets(DataSheet)
    
    Set ACell = shtData.Cells.Find("StartValue", LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0)
    Set BCell = ACell.End(xlToRight)
    Set CCell = shtData.Cells(65000, ACell.Column).End(xlUp)
    
    wbCurrent.Names.Add Name:="ImportRange", RefersTo:=shtData.Range(ACell.Address, shtData.Cells(CCell.row, BCell.Column).Address)
End With
    DoCmd.TransferSpreadsheet acImport, 8, "NewTable", ExcelPathAndFile, True, "ImportRange"

Like I said, this works perfectly if I don't have Excel open before I run the code (not the file itself; if I just have Excel open period). If Excel is open, I don't get any errors or anything, but my workbook is somehow open in BOTH instances of Excel, as Read Only in the already-open instance, and the "ImportRange" was set in both versions, but is one row off in one, and it's THIS one that Access importing.

How can I make this work if Excel was open before running code? Why is the workbook getting opened twice? Why is Access importing from the wrong one? Why would the named range be offset by one row in one version of the opened workbook?

I don't need answers to ALL these questions, I just need to make this thing work if Excel happened to be open already. I'm really confused as to why what's happening is happening.

I appreciate any help, and thanks for reading.

[dr91075]

I had the same issue. What worked for me was setting the workbook variable (wbCurrent) AFTER opening the workbook.

.Workbooks.Open(ExcelPathAndFile, , False, , pw)
set wbCurrent=xlApp.activeworkbook