Solved: Copying data from excel to word

[Birch81]

Hey Again,

I how figured out how to delete my bookmarks.

[VBA]If ActiveWorkbook.Worksheets(1).Range("F22").Value = "" Then

appDoc.Bookmarks("txtFremtidNyInst").Delete
appDoc.Bookmarks("txtFremtidNyInstPris").Delete

End If[/VBA]

But in my word documents I have listed alot of bookmarks and I have made a space between them so that they don´t stand in the same place and can´t be moved. But when I delete the bookmark I also want to delete the space I have made.
How is this possible?

[velohead]

Perhaps your "spaces" also need to be bookmarks, then these too can be deleted by excel.


That's my first advice on this board, LOL.
Advice just given was serious.

[Birch81]

How do you do that?

[velohead]

The first question is....when you place your text/values/variables into the word document, is the word document 100% blank/empty , OR , does the word document already contain some text/values.

I am constructing a similiar project....
see....http://www.vbaexpress.com/forum/showthread.php?t=33669
In brief, I populate an excel spreadsheet, and press the macro button.
This then opens specific word doc, find/replace using data from excel, then delete specific paragraphs, then save word doc as word_doc_2.
The basis of my find/replace and delete paragraphs is wholly based upon pre-existing bookmarks.

(NB - not at work til Monday, so cant access my script til then)


If you can answer my question (at top of this post), this will determine how you tackle this.

[Birch81]

The document is clean when I start transfering data to it. It has alot of text and bookmarks.

I was thinking of some kind of backspace. I just don´t know how to get i working. There is a function called TypeBackspace.

Can anyone help me on how to get this function working?

[Birch81]

Anyone ?

[Kenneth Hobs]

I don't work in MSWord that often so there is probably a shorter way but here is one.

I attached a sample doc to play with. I just set a current sheets A2 and B2 to be empty to test. A1:B3 had values otherwise. I named the bookmarks with an excel prefix and the cell name to make iteration easy.

I prefer formfields over bookmarks when doing this sort of thing myself.

[vba]Sub ExportCellsToWordBookMarks()
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim bm As Word.Bookmark, bmName As String, s As String
Dim r As Excel.Range
Dim doc As String

doc = "x:\msword\ExportCellsToWordBookMarks.docx"
If Dir(doc) = "" Then
MsgBox "Error, file does not exist." & vbLf & doc, vbCritical, "File is Missing"
Exit Sub
End If

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo errorHandler

Set myDoc = wdApp.Documents.Add(Template:=doc)
wdApp.Visible = True

For Each bm In myDoc.Bookmarks
bmName = bm.Name
If Left(bmName, 5) = "excel" Then
Set r = Excel.Range(Right(bmName, Len(bmName) - 5))
If r.Value = "" Then
bm.Select
wdApp.Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
s = wdApp.Selection.Text
While Left(s, 1) = " "
wdApp.Selection.TypeBackspace
wdApp.Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
s = wdApp.Selection.Text
Wend
wdApp.Selection.MoveRight
bm.Delete
Else
bm.Range.Text = r.Value
End If
End If
Next bm

Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
Exit Sub

errorHandler:
MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
Resume errorExit
End Sub

[/vba]