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)
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>
|
|
|
|
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)
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
|
|
|
|
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)
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.
|
|
|
|
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)
|
|
|
|