Force Word to recognize/see that Excel is open?

[hb64099]

Hello;

At work, we have some specialized internally-built software that records job data and packages it for long-term storage. We don't always trust that this software will record all the correct info, though, so there is a trusty spreadsheet open at all times alongside it, where we manually enter and adjust numbers. Once a 'job' is done, the software will generate a docx report, but here is where my question lies....

To get all the proper numbers from the spreadsheet, we run a Word macro that copies/pastes/replaces everything we need. However, sometimes the macro will not run unless we save the docx, close it and reopen it. The spreadsheet is always open.

As troubleshooting, we've written little snippets that confirm that sometimes Word just simply doesn't recognize that a spreadsheet is open, and I'm not sure on the items I'd need to use to make it re-recognize that a spreadsheet is indeed open.

We make use of GetObject to start the process and then start assigning things after that...


Set ExcelApp = GetObject(, "Excel.Application")

...

Set item1 = ExcelApp.Worksheets("Sheet1").Range("D1:D10")
Set item2 = ExcelApp.Worksheets("Sheet2").Range("E5:E30")

etc.

Is there a way to force Word to recognize any open spreadsheet, any time the macro is run? Regardless of whether the docx has been closed first or not? If it helps, the spreadsheets will always have the word "Engineering" in their name, but if it's easier to just look for any open sheet, that works as well.

The versions of Word/Excel in use are either 2016 or O365, so we aren't working with anything that's too old. "Microsoft Excel 16.0 Object Library" references are always enabled on the v2016 machines.

Thanks in advance for your help and if there are other examples/samples needed, just let me know.

[gmaxey]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oXL As Object
Dim oWB As Object
Set oXL = GetObject(, "Excel.Application")
For Each oWB In oXL.Workbooks
Debug.Print oWB.Name
Next
lbl_Exit:
Exit Sub
End Sub

[hb64099]

Hello Greg.

When I try to call that sub, it seems to hang up on the GetObject row with a Run-time error '429', ActiveX component can't create object. Is that due to placement in my original macro/where I'm making the call?

[gmaxey]

That would usually mean that Excel is not in fact running. You could use this, but then if this starts Excel there is not going to be any open workbooks:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oXL As Object
Dim oWB As Object
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err.Nubmer <> 0 Then Set oXL = CreateObject("Excel.Application")
For Each oWB In oXL.Workbooks
Debug.Print oWB.Name
Next
lbl_Exit:
Exit Sub
End Sub

[hb64099]

I think this is why it's been such a perplexing issue. Excel is indeed open, but it seems like the way our software launches/opens the generated docx is done in some way that's different from just doubleclicking on an existing docx to open it, which is what's causing the hiccups. I'll keep trying with both versions and see if i can figure out a magic trick to get it to work.

[gmaxey]

Well, .docx extension documents don't have code so nothing is going to run from it regardless how you open it.