Excel to Word

[wtnelso]

I currently have a code that exports the selected items to Word, but I want to modify the code so that it will give a message box that will ask if I want to use the currently opened sheet, or open a new one? How could I modify this code to do so? What could be put into the Cases below to accomplish that? Thank you!

Sub Excel_to_Word()
    Dim appWord As Word.Application
     
    Set appWord = New Word.Application
     
    If appWord.Visible = False Then
        appWord.Visible = True
    ElseIf appWord.Visible = True Then
        Response = MsgBox("New Sheet?", vbQuestion + vbYesNo)
        Select Case Response
        Case Is = vbYes
    
        Case Is = vbNo
        
        End Select
    End If
    Selection.Copy
     
    appWord.Documents.Add.Content.Paste
     
End Sub
     
End Sub

 

[patel45]

If you open a new workbook or sheet how can you select items to copy ?

 

[EdNerd]

Your code doesn't make sense in several areas. It almost looks like you grabbed snippets of two or three code examples and stuck them together without understanding how they fit. Forgive me, please, if my assessment is incorrect.

But Word doesn't have a Sheet object. And setting a new application instance is almost never visible, so your If is at least odd and may not give you the response you want.

Speaking of Response, you never Dim that variable. I've never seen a Yes/No MsgBox handled like that - although I can see where you might desire that construction. And you have two End Sub lines - buy no Excel varibles, like the range you want to copy??

What are you trying to do? Where are you copying code from? How much VBA understanding do you have? This will help us help you.

Ed

 

[EdNerd]

~~Delete duplicate post~~
Ed

 

[wtnelso]

Thanks for the responses. You're right, I did pull some other codes and try to fit them together. I have a decent understanding of VBA (solely in Excel), and have been able to use it for work related stuff fine, but once I move outside my knowledge area, then I'm quite lost. What I would like my code to do is export my selected range to a Word sheet. I found a code that works great, but every time I use it, it opens a new Word Document (something I don't necessarily want). I want to have an IF or Select Case True option in my code so that, if Word is currently open, it has a userform that will ask if I want to use the sheet that is currently open, or if I want a new one. This way hopefully I can copy and paste multiple things to a Word sheet without always opening a new one when I use the macro. Does that make sense?

 

[patel45]

If you have already opened the word document you know his path + name ? can we insert it in the code ?

 

[EdNerd]

Makes perfect sense! I've done the same thing lotsa times.
Unfortunately, I'm at home and all my macros are at work!
I can't pull out some working examples until Monday.
In the meantime, take a look through some of the examples on this site and in here:
http://word.mvps.org/FAQs/OfficeInterdev.htm

A couple of other thoughts:
-- The main Word objects you'll use are Document (not "sheet" - that's Excel) and Range.
-- You'll likely be using Range objects in both Word and Excel. So when you Dim them, make sure both you and the code can tell them apart:
"Dim rngWord As Word.Range"
"Dim rngXL As Excel.Range"
-- At the very top of your code module, put Option Explicit. That will cause an error for every undefined variable, which can help when you're copying in code from various sources.
-- Think through your entire process from start to finish. I write it out step by step. It helps me ask questions and deal with potential decisions and error traps before I get there. For instance:
*** Will it always be the same range in every Excel sheet? Or does the user need to select a range some times or every time?
*** How will you paste it into Word? As a table? Separate paragraphs?
*** Will you need to do any formatting to the data after you get it into Word?
*** Do you need to add in extra text or empty paragraphs? Every time? Only in certain instances?
*** How are you opening the Excel files and selecting the correct sheet and range?

Just a few things to think about. :8>)
Ed

 

[wtnelso]

Thanks Ed! Any samples that you could send would be greatly appreciated!

 

[EdNerd]

Won't be able to get back to this until Monday.
In the meantime, read through some of the info on those links.
And plot out your project.
Generic questions get generic answers that generally don't work.
But if you know where you're going with this and what it should look like, we can give direct help.

Ed

 

[EdNerd]

Well, with nothing more specific to go on, here's what I've got:

Sub CheckMyWordDoc()
Dim appWD As Word.Application
Dim docWD As Word.Document
Dim rngWD As Word.Range
Dim strDoc As String
Dim bolWD As Boolean
bolWD = False
On Error Resume Next
  Set appWD = GetObject(, "Word.Application")
  If Err.Number = 0 Then bolWD = True
  Err.Clear
On Error GoTo 0
If bolWD = True Then
  Set docWD = appWD.Documents(1)
  
  If MsgBox("You currently have the following document open:" & vbCrLf & _
            docWD.FullName & vbCrLf & vbCrLf & _
            "Do you want to paste into this document?", _
            vbCritical + vbYesNo, "Use This Document?") = vbNo Then
    Set docWD = appWD.Documents.Add
  End If
Else
  Set appWD = CreateObject("Word.Application")
  Set docWD = appWD.Documents.Add
  appWD.Visible = True
End If
Set docWD = Nothing
If bolWD = False Then appWD.Quit
Set appWD = Nothing
End Sub

Make sure you set a reference to Word in your VBA.