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...
etc.Set ExcelApp = GetObject(, "Excel.Application") ... Set item1 = ExcelApp.Worksheets("Sheet1").Range("D1:D10") Set item2 = ExcelApp.Worksheets("Sheet2").Range("E5:E30")
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.