Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Flip between word & excel

I'm trying to write a macro that will switch me from a word document window to an excel workbook window & vice versa...both of which are already open.
0
gixxer1020
Asked:
gixxer1020
  • 10
  • 9
  • 5
  • +1
1 Solution
 
pauloaguiaCommented:
You mean something like

Application.ActivateMicrosoftApp xlMicrosoftWord

?
This activates the application of the specified type. If already open it will be activated if not then a new one will be opened. Then just activate the document you want.

You'll need two macros: one in Word and another in Excel, I guess. Unless I'm missing something... maybe you can explain a little better?

Would you like more detail with what I said in the beggining?

Hope this helps

Paulo
0
 
MoliereCommented:
What application are you working in? Excel, Word, Access, etc.?
0
 
gixxer1020Author Commented:
I have a mail merge document with Word & Excel open and need a macro in Word to flip to Excel and one in Excel to flip to Word. Just in case your wondering why....I'm setting this up for peeps who are absolutely computer stupid.

thanx
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
MoliereCommented:
In the Excel Code, use the following:
sub Switch2Word
dim appWord as Word.Application
on error resume next
     Set appWord = GetObject(, "Word.Application")
        If appWord Is Nothing Then Set appWord = New Word.Application
     appWord.visible=true
on error goto 0
end sub

In the Word Code, use the following:
sub Switch2Word
dim appXL as Excel.Application
on error resume next
     Set appXL = GetObject(, "Excel.Application")
        If appXL Is Nothing Then Set appXL = New Excel.Application
     appXL.visible=true
on error goto 0
end sub

Once the app is open, you can use VBA commands in the other app by prefixing appXL/appWord to the command.

Remember to reference the Excel Reference Library in Word and reference the Word Reference Library in Excel.
0
 
gixxer1020Author Commented:
the code that pauloaguia works and is nice and simple, if anyone can suggest a simple one to go from Word to Excel I'd appreciate it.

thanx
edwin
0
 
pauloaguiaCommented:
It's the same:

Application.ActivateMicrosoftApp xlMicrosoftExcel

But I'm not sure what happens if you have more than one document open...

Paulo
0
 
RascalBirdCommented:
Why insert a macro?

Why not just insert a hyperlink that will take you to the specific file? You can assign a hyperlink to a toolbar button so you could make it specific to the files if you wanted to.

Just a thought.
0
 
gixxer1020Author Commented:
Moliere,

I tried your code exactly as written by you but all I receive is Compile Errors.....HELP

thanx
Edwin
0
 
pauloaguiaCommented:
Have you done the last line in the comment?
>>Remember to reference the Excel Reference Library in Word and reference the Word Reference Library in Excel.
0
 
gixxer1020Author Commented:
OK...now I need to ask how to Excel Reference Library in Word and reference the Word Reference Library in Excel.

edwin
0
 
pauloaguiaCommented:
In the VBA editor in word, under Tools->References find the Microsoft Excel Object Library and check it in.

Likewise in Excel with the Microsoft Word Object Library.
0
 
gixxer1020Author Commented:
OK I got it to not return any error messages, but it just does nothing. If I dont have a Word window open it opens one but it does not flip between the open Excel window & open Word window.
0
 
pauloaguiaCommented:
The code I provided does that. What was there about it that you didn't like? What other functionality do you want?
0
 
MoliereCommented:
Can you post the VBA codes you are using?
0
 
gixxer1020Author Commented:
Moliere,

Im using the code exactly as you wrote above.
0
 
gixxer1020Author Commented:
Moliere,

Im using the code exactly as you wrote above.
0
 
MoliereCommented:
I ran the code and compiled it. The only way I received an error such as "User-defined type not defined" was when I did not reference the Microsoft Word 9.0 Object Library in Tools|References...

Are you sure you are referencing the Word/Excel libraries?
0
 
pauloaguiaCommented:
Moliere, you're a few comments behind :)
That problem was already related as solved. The problem now is that apparently the code does nothing. I'd reread the thread if I were you. I'll also take another look at it tonight when I'm off work.

Paulo
0
 
pauloaguiaCommented:
If you make the code look like

appWord.visible=true
appWord.Activate

I think it will work as you wish it to.
0
 
gixxer1020Author Commented:
Each of these solutions seem to get me to the correct window (from Word to Excel and from Excel to Word), but it does not seem to flip between the two specific files that are mail merged. In other words, if other Office documents are open it can flip to any of them randomly.
0
 
pauloaguiaCommented:
I think this will finally do it:

Sub Switch2Excel()
Dim appXL As Excel.Application
Dim wbk As Workbook
    Set appXL = GetObject(, "Excel.Application")
       If appXL Is Nothing Then Set appXL = New Excel.Application
    appXL.Visible = True
    appXL.Activate
    On Error Resume Next    'since the workbook may not exist
    Set wbk = appXL.Workbooks("Book1")
    On Error GoTo 0
    If wbk Is Nothing Then  'if not open then open it
        Set wbk = appXL.Workbooks.Open("C:\Book1.xls")
    End If
    wbk.Activate
End Sub

Sub Switch2Word()
Dim appWord As Word.Application
Dim doc As Word.Document
    Set appWord = GetObject(, "Word.Application")
       If appWord Is Nothing Then Set appWord = New Word.Application
    appWord.Visible = True
    appWord.Activate
    On Error Resume Next    'since the document may not exist
    Set doc = appWord.Documents("Document1")
    On Error GoTo 0
    If doc Is Nothing Then  'if not open then open it
        Set doc = appWord.Documents.Open("C:\Document.doc")
    End If
    doc.Activate
End Sub


Finally decided to pick up on Moliere's code and add to it to give you the effect you want.

Hope this helps

Paulo
0
 
gixxer1020Author Commented:
Paulo,
Since you picked up on Moliere's code, how can I split the points between you 2, and is that an appropriate thing to do??

edwin
0
 
pauloaguiaCommented:
Whenever you want to split points, close or delete questions, etc, you can post a 0 point question in Community Support Topic Area, with a link to the question, stating your request and a moderator will help you with it.

Considering I picked up on Moliere's code he should get the points. Weather I get some of them or not I leave it up to you.

Paulo
0
 
MoliereCommented:
Paulo,

You are a gentleman and a scholar.
0
 
gixxer1020Author Commented:
Let it be so

thanx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 9
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now