Copy from excel to Word

[Kentae]

Hi
I need a simple way to copy a value /string from a cell i ecxel to a
bookmark in a worddocumnet.
Tanks in advance for all support

 

[Fumei2 via OfficeKB.com]

What have you tried so far? What have you looked up/researched? This is a
very common thing.

Also, you do not state if you are working from Excel (getting its own
information and putting that into Word), or from Word (getting information
FROM Excel, and putting it into its own bookmark).

Working FROM Excel, with an existing Word document

Dim appWord As Word.Application
Dim wrdDoc As Word.Document

Set appWord = CreateObject("Word.Application")
Set wrdDoc = appWord.Documents.Open _
(FileName:="c:\zzz\Test\ExistingFile.doc")

wrdDoc.Bookmarks("PutDataThere").Range.Text = Worksheets("Sheet1").Range("A1")
.Value

The above is not full code (by any means) but the method is basically the way
you do it.

1. you make an instance of the application
2. you use that instance

 

[Dave Williams]

I too am trying to utilize data in an Excel sheet. My vba runs from the Word
doc... I need to open a MS Excel file (using filedialog if possible) then
retrieve the data from well over 200 cells within the worksheet, then
transfer the information to the Word document. I will be adding text and/or
updating check boxes using bookmarks to locate the appropriate document
point....

I have a couple issues....

First, once I open the xls I can get the data ok and paste into the Word
doc.... but how do I close the xls (when finished) so that I can open later
without an error that the file is locked for editing by myself...

Also I need direction on how to use the filedialog function to allow users
to use a standard windows type open file dialog to search their pc for the
appropriate xls to open... do I actually open the file or can I just
reference the file (getting the name from the filedialog)? The information
is on the same sheet (name is same) no matter what xls the user will open...

Anything you can do to get me on the right track would be great!

Later perhaps you can give me some insight on what might be the best
programming approach since I need to pull so much from the xls...

Thanks!

Dave

 

[Doug Robbins - Word MVP]

To see how to properly access and then release Excel, see the article
"Control Excel from Word†at:

http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

To allow the user to browse to and select the workbook, use

Dim fd As FileDialog
Dim strWorkbook As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = "Select the Excel Workbook to use."
.InitialFileName = "*.xls?"
.AllowMultiSelect = False
If .Show = -1 Then
strWorkbook = .SelectedItems(1)
Else
MsgBox "No WorkBook Selecte."
Exit Sub
End If
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Dave Williams]

Doug:

I really appreciate your recommendations. The one for controlling Excel
works great and so does the filedialog...

I updated the code utilizing the above and added some code for the meat of
what I need to do. Problem is my code is not working now... Specifically if
I retrieve data from Excel and attempt to paste into Word it doesn't work..

An example of what I mean is the following code:

'retrieve value of cell D2 in chosen workbook and place in active word
document at bookmark NovStudyCode_D2
ActiveDocument.Bookmarks("NovStudyCode_D2").Range = Sheets("Novartis IRT
Decision Grid").Range("D2").Value

This exact code worked before....

If I go to the immediate window (while pausing the code) and hover over ?
Range("D2").value I get an error: Method Range of object _ global Failed

Where am I going wrong? Thanks in advance for all your assistance I really
appreciate your direction...

My full code is:

Sub CreatecURS()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

Dim fd As FileDialog
Dim strWorkbook As String
Set fd = Application.FileDialog(msoFileDialogFilePicker) 'set object as a
file dialog
With fd
.Title = "Select the Excel Workbook to use."
.InitialFileName = "*.xls?"
.AllowMultiSelect = False
If .Show = -1 Then
strWorkbook = .SelectedItems(1) 'Store name of workbook selected
Else
MsgBox "No WorkBook Selected."
Exit Sub
End If
End With

'specify the workbook to work on
WorkbookToWorkOn = strWorkbook 'identify workbook to use for data

'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

'retrieve value of cell D2 in chosen workbook and place in active word
document at bookmark NovStudyCode_D2
ActiveDocument.Bookmarks("NovStudyCode_D2").Range = Sheets("Novartis IRT
Decision Grid").Range("D2").Value

'retrieve value of cell I286 in chosen workbook and place in active word
document at bookmark SystemSetupType_I286
ActiveDocument.Bookmarks("SystemSetupType_I286").Range = Sheets("Novartis
IRT Decision Grid").Range("I286").Value

'check value of F36 and if yes, update checkbox DistrRegHub_F36 value to true
If Sheets("Novartis IRT Decision Grid").Range("F36").Value = "yes" Then
ActiveDocument.FormFields("DistrRegHub_F36").CheckBox.Value = True
End If
'check value of F37 and if yes, update checkbox DistrCPO_F37 value to true
If Sheets("Novartis IRT Decision Grid").Range("F37").Value = "yes" Then
ActiveDocument.FormFields("DistrCPO_F37").CheckBox.Value = True
End If
'check value of F37 and if yes, update checkbox DistrBoth_F38 value to true
If Sheets("Novartis IRT Decision Grid").Range("F38").Value = "yes" Then
ActiveDocument.FormFields("DistrBoth_F38").CheckBox.Value = True
End If

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

 

[Doug Robbins - Word MVP]

You have not opened the workbook.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com