0

I use the following all the time to manipulate Excel data from Access.

Set xl = CreateObject("Excel.Object")

However, this method creates a new Excel instance and only applies formatting, for example, to it.

xl.Range(...).Interior.Color = blah, blah, blah

Will only refer to that instance of an Excel object that was create with the first line of code.

The problem I am having is that I already have an instance of Excel open and there is VBA in Access that I want to use to apply formatting to the already open file to prep it for importing. I guess I could create the new instance of Excel and just refer to the workbook I am working on with xl.Workbook(blah).Activate but that seems kinda messy.

Yea, I could move the code to Excel, but that defeats the purpose of manipulating Excel from Access and having my VBA in central location accessible to users.

Community
  • 1
  • 1
cjones
  • 8,384
  • 17
  • 81
  • 175
  • Look into `GetObject()` As long as there's only a single instance of Excel already open, it should do what you want. – Tim Williams Jun 30 '16 at 22:00
  • Maybe this can be of help: http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel – Ralph Jun 30 '16 at 22:13
  • Does Set xlApp = GetObject(, "Excel.Application") fit the bill? This gets excel if it's open. – Cody Ｇ Jun 30 '16 at 23:33
  • If you know the full name of the workbook, use that with `GetObject` and then it won't matter how many instances of Excel are already open. – Rory Jul 01 '16 at 08:48

1 Answers1

0

Try Using:

set xl = GetObject(,"Excel.Application")
Mikku
  • 6,538
  • 3
  • 15
  • 38