Opening excel from word, no add-ins

[rbwallis]

Hello experts! I have a macro in Word that is supposed to open Excel and paste a previous selection from Word into a new spreadsheet. It all works very well, except when Excel starts up programmatically, my custom add-in does not load. It loads fine if I start Excel by itself, so it must be something with opening programmatically.
Here is the code from Word:

Sub Exportwordtoexcel(control As IRibbonControl)
    Dim wordDoc As Object
    Dim oXL As Excel.Application
    Dim DocTarget As Word.Document
    Dim Target As Excel.Workbook
    Dim tSheet As Excel.Worksheet
    
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
    QuestionToMessageBox = "Do you want Excel to open and paste your selection?"
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "QuikBots for Word")
    If YesOrNoAnswerToMessageBox = vbYes Then
    
Set wordDoc = GetObject(, "word.application")
Selection.Copy

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
      Set oXL = New Excel.Application
End If

oXL.Visible = True

Set Target = oXL.Workbooks.Add
Set tSheet = Target.Sheets(1)
tSheet.Paste
 
    Else
    End If

End Sub

Can you help?

Thanks, Robert

 

[Domenic]

There may be a better way, but here's a workaround. You can loop through each addin within the AddIns collection, and check if installed. If so, first set it to False, and then re-set it to True. So, for example, you can replace...

If Err Then
      Set oXL = New Excel.Application
End If

with

If Err Then
    Set oXL = New Excel.Application
    For Each oAddIn In oXL.AddIns
        With oAddIn
            If .Installed Then
                .Installed = False
                .Installed = True
            End If
        End With
    Next oAddIn
End If

And, you would add the following declaration...

Dim oAddIn As Excel.AddIn

Hope this helps!

 

[AXBean]

Hi Domenic, this works great!...except:
Running the macro opens two sessions of Excel, and pastes the data info both windows. I'm guessing it has something to do with the checking to see if Excel is already open, but I'm still a novice at this. Any ideas on how to make Excel open only one time?

Thanks for all your help,
Rob

Revised Code below:

Sub Exportwordtoexcel(control As IRibbonControl)
    Dim wordDoc As Object
    Dim oXL As Excel.Application
    Dim DocTarget As Word.Document
    Dim Target As Excel.Workbook
    Dim tSheet As Excel.Worksheet
    Dim oAddIn As Excel.AddIn
    
Dim YesOrNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
    QuestionToMessageBox = "Do you want Excel to open and paste your selection?"
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "QuikBots for Word")
    If YesOrNoAnswerToMessageBox = vbYes Then
    
Set wordDoc = GetObject(, "word.application")
Selection.Copy

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
    Set oXL = New Excel.Application
    For Each oAddIn In oXL.AddIns
        With oAddIn
            If .Installed Then
                .Installed = False
                .Installed = True
            End If
        End With
    Next oAddIn
End If

oXL.Visible = True

Set Target = oXL.Workbooks.Add
Set tSheet = Target.Sheets(1)
tSheet.Paste
 
    Else
    End If

End Sub

 

[Domenic]

Hi Domenic, this works great!...except:
Running the macro opens two sessions of Excel, and pastes the data info both windows. I'm guessing it has something to do with the checking to see if Excel is already open, but I'm still a novice at this. Any ideas on how to make Excel open only one time?

Thanks for all your help,
Rob

Hi Rob, it shouldn't open a second session. It first checks whether Excel is already running. If so, it creates a new workbook within the existing application. Otherwise, it starts Excel and then creates a new workbook. So with the former, if the existing instance of Excel already has a workbook open, you'll end up with a second one when it creates the new workbook. So you would have two workbooks open, but not two instances of Excel.

 

[AXBean]

I agree, that shouldn't happen. But that is what is happening. I'll look into it some more and report back.

Thanks,
Rob

 

[AXBean]

OK, tried everything I could think of. Still opening two seions of Excel. Any help? Thanks.

 

[Domenic]

Rob, does it still happen when you remove part of the code that re-installs/enables the add-ins?

 

[RoryA]

Which version of Excel are you using? If it's later than 2010, Excel is SDI so may appear to be two applications but actually they're in the same instance. (or are you checking the number of excel.exe processes in Task Manager?)

 

[AXBean]

There is only one instance of Excel running in Task Manager, but two open windows. I tried a Workbook.Close command, but it closed both of them.

 

[AXBean]

No, only the one instance, just no add-ins.