topic Re: How to display text object content in Header(MS Word) using Macro in QlikView

How to display text object content in Header(MS Word) using Macro

Wed, 25 Nov 2020 16:16:04 GMT

I am using Macro to export the tables(in a word document).

Here is the macro

sub exportTable

on error resume next

set objWord = CreateObject("Word.Application")

objWord.visible = true

path = ActiveDocument.Evaluate("'$(QvWorkPath)\'")

'msgbox(path + "Templates\Trame BS.docx")

set objDoc=objWord.Documents.Open(path + "Templates\Sample.docx",true,false)

for i = 1 to 11

ActiveDocument.GetSheetObject("BC"& i ).CopyTableToClipboard (true)

Set rng = objDoc.Content

rng.Find.execute("<BS" & i & ">")

If rng.Find.Found = True Then

'msgbox("found")

rng.select

objWord.Selection.Paste

end if

next

objDoc.SaveAs path + "Templates\BS_TMP.docx"

'objDoc.Close

set objDoc=nothing

'objWord.quit

set objWord=nothing

end sub

I need to display one text object content in Headers of all pages in Word document.

If anyone has written VB script for Headers, please share it.

Thanks

Re: How to display text object content in Header(MS Word) using Macro

Anil_Babu_Samineni — Thu, 01 Dec 2016 02:54:07 GMT

Can you try this

sub exportTable

on error resume next

set objWord = CreateObject("Word.Application")

objWord.visible = true

path = ActiveDocument.Evaluate("'$(QvWorkPath)\'")

'msgbox(path + "Templates\Trame BS.docx")

set objDoc=objWord.Documents.Open(path + "Templates\Sample.docx",true,false)

for i = 1 to 11

ActiveDocument.GetSheetObject("BC"& i ).CopyTableTextToClipboard (true)

Set rng = objDoc.Content

rng.Find.execute("<BS" & i & ">")

If rng.Find.Found = True Then

'msgbox("found")

rng.select

objWord.Selection.Paste

end if

next

objDoc.SaveAs path + "Templates\BS_TMP.docx"

'objDoc.Close

set objDoc=nothing

'objWord.quit

set objWord=nothing

end sub

Re: How to display text object content in Header(MS Word) using Macro

Thu, 01 Dec 2016 07:13:19 GMT

Thank you for your reply. I have tried CopyTextToClipboard. I got content of the text object.

How to use it in Headers(VB script) in Word?

Re: How to display text object content in Header(MS Word) using Macro

Anil_Babu_Samineni — Thu, 01 Dec 2016 07:25:50 GMT

Yes, Good to hear. Now check this to get headers

Const ForReading = 1

Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("DriveReport.txt", ForReading)

strContents = objFile.ReadAll

objFile.Close

strFirstLine = "Server" & vbTab & vbtab & "Drive" & vbtab & "Size" & vbtab & "Used" & vbtab & "Free" & vbtab & "Free(%)"

strNewContents = strFirstLine & vbCrLf & strContents

Set objFile = objFSO.OpenTextFile("DriveReport.txt", ForWriting)

objFile.WriteLine strNewContents

objFile.Close

And then club for text and headers then see, How seems work and let us know. We may can help you for final script

Re: How to display text object content in Header(MS Word) using Macro

Anil_Babu_Samineni — Thu, 01 Dec 2016 07:26:48 GMT

Don't worry about VBTAB (It's act as Chr(9) in Qlikview)

Re: How to display text object content in Header(MS Word) using Macro

Thu, 01 Dec 2016 08:47:08 GMT

Thanks Rahul.

I have attached a image for your reference(you can see two text objects ID in the header)

This is the script to paste text object content in Word. I want to paste it in Headers (every page) of Word Document

for i = 1 to 20

ActiveDocument.GetSheetObject("BDU_"& 2).CopyTextToClipboard 'true

Set rng = objDoc.Content

rng.Find.execute("<BDU_" & 2 & ">")

If rng.Find.Found = True Then

'msgbox("found")

rng.select

objWord.Selection.Paste

end if

For reference : HeadersFooters Object (Word)

Re: How to display text object content in Header(MS Word) using Macro

Anil_Babu_Samineni — Thu, 01 Dec 2016 10:47:04 GMT

What you done with these 2 objects? Can you elaborate more. Would you get a chance to send that content to Word. If so, What was the next forward. Would you describe more your intend to show headers as well

BTW - I am the Anil

Re: How to display text object content in Header(MS Word) using Macro

Thu, 01 Dec 2016 13:29:39 GMT

Thanks Anil. I am ajay.

I am working on a report. I change the object id in pivot table and I use a loop to display the table in the word.

In the word document, I have to display the year selected and region inside the Headers. The tag for year is <BDU_2> and for region is <BDU_3>. When i click the button in App, macro will generate word doc.

I used headers in Word to display year and region name. With the above macro, Its not working for header section.