Pivot > selected values > WORD.doc

[#1]

Hello QVers,

at the moment I'm doing three steps to transfer selected values of a pivot table into WORD:

1. Step:
Via header button of a pivot table I'm selecting values into clipboard.
2.Step
Via QV push button I'm opening a Word.doc [based on a dot].
3. Step
Via Word macro I'm pasting and formatting the clipboard selection into the Word.doc.

I like to combine steps to have only one [or 2] to do this with a QV push button.

Please, has anybody an idea/example? Thank You.

Greetings. dj_skbs

[Anonymous]

Hi dj_skbs

I solved this bij making one button with two actions:

Action 1: Start application: C:\Program Files\Microsoft Office\Office12\WINWORD.EXE (or whatever application you want)

Action 2: Run Macro : CopyText

Paste this code into the Macro module editor:

sub CopyText
ActiveDocument.GetSheetObject("CH01").CopyTextToClipboard 'change CH01 into the right Object ID
end sub

Now just press the button, MS Word will open and just use Ctrl+v to paste your values.

Extra options:

If you want to copy it as a bitmap you can use:

sub CopyBitmap
ActiveDocument.GetSheetObject("CH01").CopyBitmapToClipboard 'change CH01 into the right Object ID
end sub

And if you want to copy the whole table (including colour and lay-out) you can use:

sub CopyTable

set obj1 = ActiveDocument.getsheetobject("CH01")

'change CH01 into the right Object ID

obj1.CopyTableToClipboard true

end sub

Let me know if this works for you ok?

[Anonymous]

Check my profile for an example: "Copy to Word.qvw"

(somehow I get an error when I try to upload it here)

[#4]

Hello Dennis,
thank You.
In the meantime I succeeded in doing that with 2 steps:
Pivot table subtotal results with the same format I will send to each department.

1.) QV-Button does these actions:
- Clear ALL
- Activate Object > CH24
- Select in Field > Field_1
- Run Macro >
"Sub clipboard
ActiveDocument.GetSheetObject("CH24").CopyTableToClipboard true
end sub"
- Launch > C:\...\word.dot */opens a word.doc with prompt

2.) WORD-Button running a macro which is pasting that from 1.) at word prompt:
"Sub QVResult()
Selection.Paste
ActiveDocument.Tables(1).Columns(4).Select
Selection.Cut
With Selection.Tables(1).Rows
.LeftIndent = 0
.Alignment = wdAlignRowCenter
End With
End Sub"

My special wish is to have 2.) integrated in 1.). My feeling says "no"; perhaps cutting the column but not the alignment.

Any idea will be appreciated. Thanks.