Solved: Copying data from excel to word

[Birch81]

Hey,

I have a question that involves moveing data from excel to word.
how do I do this the best way?

The way it has to work is that depending on which cell that has a value, the cells with a value in has to be moves to Word where there has to be inserted some text to.

I know that I can do this with bookmarks but is this a good way of doing it?

The values are put after each others depending on which cell have values in.

hope this make sense?

Thanks in advance.

Birch81

[Bob Phillips]

Bookmark is the best way IMO, and just select the data from VBA before copying.

[Birch81]

Okay I might need a little help here.

How do I copy a specific cell value to a specific word bookmark?

I need a hint.

Birch81

[Bob Phillips]

Here is a simple example

[vba]

Sub CopyToWord()
Dim appWord As Object
Dim appDoc As Object

Set appWord = CreateObject("Word.Application")
Set appDoc = appWord.documents.New("C:\MyDoc.doc")
appDoc.Bookmarks("MyBookmark").Range.Text = Worksheets(1).Range("A26").Value
appWord.Visible = True
End Sub
[/vba]

[Tinbendr]

Hey,

I have a question that involves moving data from excel to word.
how do I do this the best way?

I like early binging, but it has drawbacks, too.

The way it has to work is that depending on which cell that has a value, the cells with a value in has to be moves to Word where there has to be inserted some text to.

You'll have to get Xld to help with conditional macro running.

I know that I can do this with bookmarks but is this a good way of doing it?

Depends on what you are doing in Word.

[vba]Sub ControlWordFromXL()
'First set a reference to Word (in the VB Editor, select Tools + References).
Dim oWord As Word.Application
Dim WordWasNotRunning As Boolean
Dim oDoc As Word.Document

'Get existing instance of Word if it's open; otherwise create a new one

On Error Resume Next

Set oWord = GetObject(, "Word.Application")
If Err Then
Set oWord = New Word.Application
WordWasNotRunning = True
End If

On Error GoTo Err_Handler

Set oDoc as oWord.Activedocument

oWord.Visible = False

On Error GoTo Err_Handler

oDoc.Bookmarks("My BookMark").Range.Text = _
activeworkbook.WorkSheets(1).Range("A1").Value

oDoc.Close savechanges:=wdDoNotSaveChanges

If WordWasNotRunning Then
oWord.Quit
End If

'Make sure you release object references.

Set oWord = Nothing
Set oDoc = Nothing
Set myDialog = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox "Word caused a problem. " & Err.Description, vbCritical, "Error: " _
& Err.Number
If WordWasNotRunning Then
oWord.Quit
End If

End Sub
[/vba]

[Bob Phillips]

I like early binging, but it has drawbacks, too.

I much prefer late binging, crashing out afterwards works better for me.

[Birch81]

Thanks for the quick replys.

I´ll try working with my documents.

Thank you.

Birch81

[Birch81]

Double post.

[Birch81]

Hey again.

I have tryed this but I get an error saying
"Runtime error "424"
"Objerct required"

[vba]Sub Transfer_to_Word()
Set appwd = CreateObject("word.application")
appwd.Visible = True
appwd.Documents.Open Filename:="H:/tilbudsskabelon_macro1.docm"

appDoc.Bookmarks("SamletExMoms").Range.Text = ActiveWorkbook.Worksheets(1).Range("F24").Value
End Sub
[/vba]

In my word document I have a bookmark called SamletExMoms.

Birch81

[Bob Phillips]

Can you post the Excel and Word docs for us to try it.

[Birch81]

Sure.

[Birch81]

Here´s the docm file

[Bob Phillips]

This worked fine for me

[vba]

Sub Transfer_to_Word()
Dim appWord As Object
Dim appDoc As Object

Set appWord = CreateObject("Word.Application")

Set appDoc = appWord.Documents.Open(Filename:="H:/tilbudsskabelon_macro1.docm")

appWord.Visible = True
appDoc.Bookmarks("SamletExMoms").Range.Text = ActiveWorkbook.Worksheets(1).Range("F24").Value

End Sub
[/vba]

[Birch81]

Works here to

Thank you very much.

[Birch81]

Hey again,

Can you help med underline some text?

[vba]If ActiveWorkbook.Worksheets(1).Range("F24").Value <> "" Then

appDoc.Bookmarks("txtTotalNyInst").Range.Text = "Ialt" & vbTab & "kr." & vbTab & _
Format(ActiveWorkbook.Worksheets(1).Range("F24").Value, "##,##0.00") & vbCrLf & vbTab

End If[/vba]

I haft to underline this part
"kr." & vbTab & Format(ActiveWorkbook.Worksheets(1).Range("F24").Value, "##,##0.00")
but how do I do that? Can I do it inside a bookmark?

[Bob Phillips]

[vba]

Sub Transfer_to_Word()
Const wdUnderlineSingle As Long = 1
Const WORD_FILE As String = "H:/tilbudsskabelon_macro1.docm"
Const BOOKMARK As String = "SamletExMoms"
Dim appWord As Object
Dim appDoc As Object

Set appWord = CreateObject("Word.Application")

Set appDoc = appWord.Documents.Open(Filename:=WORD_FILE)

appWord.Visible = True
With appDoc.Bookmarks(BOOKMARK).Range

.Text = ActiveWorkbook.Worksheets(1).Range("F24").Value
.Font.Underline = wdUnderlineSingle
End With

Set appDoc = Nothing
Set appWord = Nothing
End Sub
[/vba]

[Birch81]

Thanks again Xld

I´ll try working with it.

[Bob Phillips]

If you want to test if word is already started, try this

[vba]

Public Sub Transfer_to_Word()
Const wdCharacter As Long = 1
Const wdUnderlineSingle As Long = 1
Const WORD_FILE As String = "H:/tilbudsskabelon_macro1.docm"
Const BOOKMARK As String = "SamletExMoms"
Dim appWord As Object
Dim appDoc As Object

On Error Resume Next
Set appWord = GetObject(, "Word.Application")
On Error GoTo 0
If appWord Is Nothing Then

Set appWord = CreateObject("Word.Application")
appWord.Visible = True
End If

On Error Resume Next
Set appDoc = appWord.Documents(WORD_FILE)
On Error GoTo 0
If appDoc Is Nothing Then

Set appDoc = appWord.Documents.Open(Filename:=WORD_FILE)
End If

With appDoc.Bookmarks(BOOKMARK).Range

.Text = ActiveWorkbook.Worksheets(1).Range("F24").Value
.Font.Underline = wdUnderlineSingle
End With

Set appDoc = Nothing
Set appWord = Nothing
End Sub
[/vba]

[Birch81]

Hey xld,

Thank you for all the help you have assisted me with. I have come a long way with my document now but I still need a little help

Is there a way to delete bookmarks if I can see it is not used?

That is when I press my button in excel and I want to transfer my data to Word. Then when I can see that a bookmark is not used I want to delete it. Is this Possible?

[Birch81]

Ahh I belive I found a solution on my problem.