?
Solved

Flip between word & excel

Posted on 2003-03-04
25
Medium Priority
?
337 Views
Last Modified: 2008-02-20
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
Comment
Question by:gixxer1020
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 5
  • +1
25 Comments
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8066723
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
 
LVL 3

Expert Comment

by:Moliere
ID: 8073419
What application are you working in? Excel, Word, Access, etc.?
0
 

Author Comment

by:gixxer1020
ID: 8076668
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 3

Accepted Solution

by:
Moliere earned 200 total points
ID: 8081954
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
 

Author Comment

by:gixxer1020
ID: 8089526
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8090735
It's the same:

Application.ActivateMicrosoftApp xlMicrosoftExcel

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

Paulo
0
 
LVL 2

Expert Comment

by:RascalBird
ID: 8108570
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
 

Author Comment

by:gixxer1020
ID: 8133156
Moliere,

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

thanx
Edwin
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8135087
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
 

Author Comment

by:gixxer1020
ID: 8143921
OK...now I need to ask how to Excel Reference Library in Word and reference the Word Reference Library in Excel.

edwin
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8144016
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
 

Author Comment

by:gixxer1020
ID: 8163553
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8165298
The code I provided does that. What was there about it that you didn't like? What other functionality do you want?
0
 
LVL 3

Expert Comment

by:Moliere
ID: 8166866
Can you post the VBA codes you are using?
0
 

Author Comment

by:gixxer1020
ID: 8169542
Moliere,

Im using the code exactly as you wrote above.
0
 

Author Comment

by:gixxer1020
ID: 8169596
Moliere,

Im using the code exactly as you wrote above.
0
 
LVL 3

Expert Comment

by:Moliere
ID: 8174248
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8174335
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8177080
If you make the code look like

appWord.visible=true
appWord.Activate

I think it will work as you wish it to.
0
 

Author Comment

by:gixxer1020
ID: 8227453
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8228282
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
 

Author Comment

by:gixxer1020
ID: 8229040
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8229952
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
 
LVL 3

Expert Comment

by:Moliere
ID: 8231811
Paulo,

You are a gentleman and a scholar.
0
 

Author Comment

by:gixxer1020
ID: 8234267
Let it be so

thanx
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question