Excel data transferred to Word via VBA

[Dawber]

Thanks to the assistance of this Forum, I have reached a point where I am opening a word document and inserting text, at various bookmarks, derived from an Excel Sheet.

Dim psc As Variant
xxx = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
Dim asm As Variant
zzz = ActiveCell.Value
ActiveCell.Offset(1, -2).Range("A1").Select

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("C:\Documents and Settings\gbgda\Desktop\New Test Folder\test quote.doc")

wrdDoc.Bookmarks("XXX").Range.Text = xxx
wrdDoc.Bookmarks("ZZZ").Range.Text = zzz

The data referenced "XXX" / xxx is inserted, though the macro crashes at zzz, without giving an explanatory message. If anybody could offer some advice, it would be greatly appreciated.

 

[Bong]

I'm not entirely certain, but I think you're confusing active and selected, yet another reason to follow the prevailing wisdom in this forum and avoid both of these.

That said, what do you mean crashed? Can you insert some debug.print --- statements to see that the values for the bookmark ranges and the text are what you're expecting?

_________________
Bob Rashkin

 

[SkipVought]

Hi,

Is this macro relative to where you select each time, ie would you select ANY other range to begin with?

I avoid Activate and Select. Use fully quallified references...

Dim psc As Variant
xxx = Worksheet("WhatSheetAreYouOn???").Range("WhatCellIsSelected").Value
Dim asm As Variant
zzz = Worksheet("WhatSheetAreYouOn???").Range("WhatCellIsSelected").offset(0,1).Value
yyyy = Worksheet("WhatSheetAreYouOn???").Range("WhatCellIsSelected").offset(0,2).value
...

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Dawber]

Bong / Skip, thanks for responding. The row reference is relative, the column absolute.

If I use a (')quotation on the zzz line, the macro operates entirely as expected. When the (') is removed and the zzz line becomes active in the VBA, the macro terminates with a blnk error message (ie. no identification of the reason for the error).

One peculiar thing that I have noticed is that when the macro fails, Word is only operating in the background, only accessible through task manager as a process not an application.

 

[SkipVought]

"When the (') is removed ..."

A single quote in Excel means that the VALUE is converted to TEXT if its a NUMBER. You probably have a NUMBER that, when the {'} is removed, is not being converted to TEXT, as it MUST to be inserted into a Word document.

"The row reference is relative, the column absolute."
You're in VBA. Relative and absolute is SHEET reference terminology. What is your point?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Dawber]

Sorry Skip, you asked whether this macro is relative to where you select each time, hence my comment that the row is indeed relative, whereas the column is not.

With the ('), I am refering to making a line of VBA code into a comment/note. I am just identifying that the first line of VBA operates as I want/expect, the second does not.

 

[SkipVought]

So in a specific column, you might start your macro in row 1 or row 9999?

Try this...

Dim psc As Variant
xxx = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
Dim asm As Variant
zzz = ActiveCell.Value
ActiveCell.Offset(1, -[red][b]1[/b][/red]).Range("A1").Select

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Dawber]

Skip,

Thanks for the input but that is not the area where I require assistance. I am definitely gathering the data correctly (despite the typo that you found), however, I cannot input the second data item into my Word document.

It is this line, from my original post:

wrdDoc.Bookmarks("ZZZ").Range.Text = zzz

....that will not perform the required task.

 

[PHV]

What happens if you swap the 2 lines ?
wrdDoc.Bookmarks("ZZZ").Range.Text = zzz
wrdDoc.Bookmarks("XXX").Range.Text = xxx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dawber]

PHV, thanks for responding.

Works fine with just "ZZZ" but fails as soon as "XXX" is introduced.

 

[PHV]

What about this ?
wrdDoc.Application.Visible = True
wrdDoc.Bookmarks(...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dawber]

PHV, nearly there.

Both pieces of data are now transfered, however, the subsequent task (saving the Word doc) now fails. Do I need to somehow return the focus to Excel?

 

[PHV]

What about posting the WHOLE code, as I don't see in your previous posts any code saving any doc ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dawber]

Sub TestWordDoc()

Dim site As Variant
site = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
Dim project As Variant
project = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
Dim psc As Variant
psc = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
Dim asm As Variant
asm = ActiveCell.Value

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("C:\New Test Folder\test letter.doc")
    
wrdDoc.Application.Visible = True

wrdDoc.Bookmarks("project").Range.Text = project
wrdDoc.Bookmarks("site").Range.Text = site
wrdDoc.Bookmarks("psc").Range.Text = psc
wrdDoc.Bookmarks("asm").Range.Text = asm

ChangeFileOpenDirectory "C:\New Test Folder\"

ActiveDocument.SaveAs Filename:=site & " - " & project & " letter.doc", FileFormat:=wdFormatDocument,_ LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False,SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:=False

    wrdApp.Quit
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

 

[PHV]

...
[!]wrdApp.[/!]ChangeFileOpenDirectory "C:\New Test Folder\"
[!]wrdApp.[/!]ActiveDocument.SaveAs ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dawber]

PHV,

Brilliant, your assistance has been much appreciated.

 

[fumei]

I am not sure why you are using ChangeOpenFileDirectory. Note that this is the folder Word will use to open files.

Why are you using that in order to SAVE a file?

const SaveFolder As String = "C:\New Test Folder\"

' yadda yadda
wrdDoc.SaveAs Filename:=SaveFolder & site & " - " & project & " letter.doc"

There is nothing wrong with changing the open files directory, but it is not required at all.

faq219-2884

Gerry
My paintings and sculpture

 

[Dawber]

fumei,

Good point, it must have been left in there from an earlier incarnation and I have managed to overlook it since. I will remove it at once, thanks for the tip and for taking the time to respond.