Paste Excel to word

Author Message
Chodie

  • Total Posts : 4
  • Scores: 0
  • Reward points : 0
  • Joined: 11/28/2011
  • Status: offline
Paste Excel to word Monday, November 28, 2011 7:37 AM (permalink)
0
[Helpful answer received] / [List Solutions Only]
Looking on google is almost impossible to solve this issue.  Google autocorrects vbscript for VBA and I'm lost in the mix up.  My goal is to take data from multiple cells and put them into a word template.

Do I have to do this 1 by 1?
I'm having problems sending my read Excel values to word.
The function I have for replacing words in Word works on its own but it can't seem to read the values read from Excel.

Here's what it looks like

dim BizName, objExcel, objSelection, objdoc
 
 
'The basic formula for everything happening.  Opening word then excel.  Taking excel info and pasting it into word.
openWord
openExcel
getValue
activateWord
pasteValue

Function openExcel
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("D...script.xls")
objExcel.Visible = True
End Function

Function getValue
BizName=objExcel.Cells(2,3).Value
End function

Function openWord
Const wdReplaceAll  = 2
Set objWord = CreateObject("Word.Application")

objWord.Visible = True
Set objDoc = objWord.Documents.Open("D:\Documents and Settings\cdwils5\Desktop\WScript.doc")
Set objSelection = objWord.Selection
End Function

Function activateWord
objWord.Activate
End Function

Function pasteValue
'BUSINESS NAME
objSelection.Find.Text = "Business Name"
objSelection.Find.Forward = TRUE
objSelection.Find.MatchWholeWord = TRUE
objSelection.Find.Replacement.text = BizName
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll
End Function


I can't change the order in which documents open either.
<message edited by Chodie on Monday, November 28, 2011 8:20 AM>
 
#1
    59cobalt

    • Total Posts : 979
    • Scores: 91
    • Reward points : 0
    • Joined: 7/17/2011
    • Status: offline
    Re:Paste Excel to word Monday, November 28, 2011 9:18 AM (permalink)
    0
    [This post was marked as helpful]
    You define the constant wdReplaceAll inside the function openWord(), thus it isn't globally available. Also you should clear the formatting for the search and replace strings and define the .Wrap property. Try this:
    Const wdFindContinue = 1
    Const wdReplaceAll   = 2
    
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    Set objDoc = objWord.Documents.Open("D:\...\WScript.doc")
    
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Set objWorkbook = objExcel.Workbooks.Open("D:\...\script.xls")
    
    With objWord.Selection.Find
     .ClearFormatting
     .Text = "Business Name"
     .Replacement.ClearFormatting
     .Replacement.Text = objExcel.ActiveSheet.Cells(2, 3).Value
     .Forward = True
     .Wrap = wdFindContinue
     .Execute ,,,,,,,,,,wdReplaceAll
    End With

     
    #2
      Chodie

      • Total Posts : 4
      • Scores: 0
      • Reward points : 0
      • Joined: 11/28/2011
      • Status: offline
      Re:Paste Excel to word Monday, November 28, 2011 9:48 AM (permalink)
      0
      I just heard about VBScript on Saturday so I'm really happy you've helped me with this!  I was heading down the wrong path.
       
      Thanks so much.
       
      Only thing I changed was ".Replacement.Text = objExcel.Cells(2,3).Value" and it worked perfectly.
       
      #3
        59cobalt

        • Total Posts : 979
        • Scores: 91
        • Reward points : 0
        • Joined: 7/17/2011
        • Status: offline
        Re:Paste Excel to word Monday, November 28, 2011 11:21 AM (permalink)
        0
        You're welcome.
         
        #4

          Online Bookmarks Sharing: Share/Bookmark

          Jump to:

          Current active users

          There are 0 members and 1 guests.

          Icon Legend and Permission

          • New Messages
          • No New Messages
          • Hot Topic w/ New Messages
          • Hot Topic w/o New Messages
          • Locked w/ New Messages
          • Locked w/o New Messages
          • Read Message
          • Post New Thread
          • Reply to message
          • Post New Poll
          • Submit Vote
          • Post reward post
          • Delete my own posts
          • Delete my own threads
          • Rate post

          2000-2012 ASPPlayground.NET Forum Version 3.9