Switching between applications within an Excel macro

[sagain2k]

Can I run a VBA macro in Excel which lets me switch over to Word (or other Office application) to run a macro in Word and then switch back to where I left off in the Excel macro?

This would be very useful when you have to take advantage of particular applications strengths but want the user to stay within the origial application that started the macro. This sort of integration should definitely be a part of the MS Office design. Is this possible and if so what's the best technique? Thanks!

 

[Colo]

Hi,

I this case, I think calling a macro in Word from Excel is the better way to go.
It depends what the Word macro would do.

If you have a procedure like this in Word,

Sub Macro1()
'word macro
    Selection.TypeText Text:="Hi there!"
End Sub

The following code would open Word application and run Macro1 in Word application.

Sub Macro1()
    Dim oWrd As Object
    Set oWrd = CreateObject("Word.Application")
    oWrd.Visible = True
    oWrd.documents.Add
    oWrd.Application.Run MacroName:="Macro1"
    oWrd.ActiveDocument.SaveAs Filename:="Hi ther1.doc"
    oWrd.Application.Quit
    MsgBox "now back to Excel"
End Sub

 

[sagain2k]

Colo---thanks very much! This just what I was looking for! I've got most of it working the way I want to, with a few things to still figure out.

Any suggestions on how best to be sure the Excel macro keeps running and not stay stuck in Word without having to manually click on Excel again?

Being able to switch to Word and allow you to edit until hitting a specific key would also be very useful. I'd like to be able to always control how/when it switches back and forth between apps to specific documents, being able to have it run a Word macros, switch back to Excel when done and continue with the Excel macro.

Any other sample code (or corrections to the code I was testing) is most welcome! Thanks again, this VERY useful!

Here's the code I was using. I adapted it to open a specific document as well (I've commented out some of the unused code I was testing

Sub SwitchtoWordTest()

Dim oWrd As Object
Dim oWrdDoc As Object
Dim Docname

Docname = "d:\data98\TEST DOCUMENT.doc"

'Starts MS Word
Set oWrd = CreateObject("Word.Application")

'Opens document specified in Docname
Set oWrdDoc = oWrd.Documents.Open(Docname)

oWrd.Visible = True 'Makes Word visible

'oWrd.Documents(Docname).Activate '
'oWrd.documents.Add 'this opens new blank document
'Docname = oWrd.activedocument.Name 'assigns active document name

'oWrd.Application.Run MacroName:="pastetext" 'runs a macro in Word
'oWrd.ActiveDocument.SaveAs Filename:="testname.doc" 'saves

'MsgBox "now back to Excel"
'Worksheets("Sheet1").Activate 'switches back to Excel sheet1

'This example activates Testxl.xls. If it has multiple windows,
' the example activates the first window, Testxl.xls:1.

Workbooks("Testxl.xls").Activate

AppActivate "Microsoft Word" 'Works to switch to currently active Word document!
' But stays within Word, waits for editing etc...only after click into Excel
'does the macro continue on...

Workbooks("Testxl.xls").Activate 'trying to activate the Excel file

'oWrd.Application.Quit 'quits the Word application

'AppActivate "Microsoft Excel" 'activates Excel

'Application.ActivateMicrosoftApp xlMicrosoftWord 'activates Word with Document2

'oWrd.documents(Docname).Activate 'activates this document...works!
'but doesn't switch to word..stays in Excel
'oWrd.Application.Quit 'quits the Word application

End Sub

 

[Copyt]

@ Colo

Thank you very much. I am also looking for this kind of code. Work like a charm!

 

[svaidy]

Thanks guys, this is brilliant. I am a macro newbie and it has taken me 4 hours to get here!

I have used the above code to try and automate my own stuff but need a little bit of help.

This is the background:
I have data in an excel file with a formula for each cell (x1250) {The new formula goes into ActiveCell.Offset(1,1)}. The formula is the same except that the cell numbers have to be changed for each (1,1) cell. So, I have stored the formula in a word document, where I have another macro that helps me find/replace/copy.

Thank you guys, it works brilliantly so far! (toggling between excel and word and back!)

However, I want this formula to loop for say Range("B=258"). How do I do this?

Here is my code (Thanks to Colo and Sagain2K again )

Sub TestMacro()'
' TestMacro Macro
'


'
ActiveCell.Offset(1, 1).Select
Dim oWrd As Object
Dim oWrdDoc As Object
Dim Docname


Docname = "C:\Test\Test.docm"

Set oWrd = CreateObject("Word.Application")
Set oWrdDoc = oWrd.Documents.Open(Docname)
oWrd.Visible = True 'Makes Word visible
oWrd.Documents(Docname).Activate '
oWrd.Application.Run MacroName:="Macro1" 'runs a macro in Word

MsgBox "now back to Excel"
Worksheets("Sheet1").Activate 'switches back to Excel sheet Sheet1


'This pastes the new formula from the word document on to the offset new cell
ActiveSheet.Paste


'Now, here, I want the whole thing to run again, BUT, the word application is already running - I don't know how to correct it. I have now found that there is a Do...Until, Loop l command but am not able to exactly make it work.




End Sub

Thanks kindly for your help,
Kind regards,
Vaidy