Consulting

Results 1 to 6 of 6

Thread: Force Word to recognize/see that Excel is open?

  1. #1
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    4
    Location

    Force Word to recognize/see that Excel is open?

    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.

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    4
    Location
    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?

  4. #4
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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
    Greg

    Visit my website: http://gregmaxey.com

  5. #5
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    4
    Location
    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.

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Well, .docx extension documents don't have code so nothing is going to run from it regardless how you open it.
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •