How to copy text from an excel file into a newly generated Word document's header?

[Devagorgon]

I have to write a macro in Excel VBA to convert a given Excel Document into a word file.
So far I have managed most of the normal copying starting like this

Dim oConvWB As Workbook
Set oConvWB = Workbooks.Open(TextBox_ExcelPath.Text)
Dim AppWord As Object
Set AppWord = CreateObject("Word.Application")
Set AppWdDoc = AppWord.Documents.Add
AppWord.Visible = True

I copy the normal content via

oConvWB.Sheets("S1 Deckblat").Select
oConvWB.Sheets("S1 Deckblat").Range(Cells(18, 1), Cells(43, 8)).Copy
AppWord.Selection.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=True

The Problem ist, that I have some selected content, I need to write into Header and Footnotes (different for first and later pages) and I cannot find any working (paste- or accessing-) commands.
Using commands like
Dim HdrRange As Range
Set HdrRange = AppWdDoc.Sections.Item(1).Headers(wdHeaderFooterPrimary).Range
will crash at the Set command

Even just manipulating the Header Text via
AppWord.Sections(1).Headers(wdHeaderFooterPrimary).Range.Text = "Text"
fails with an error.

If someone could tell me, how I can insert text (or even better, how I can paste my selected Cells from Excel), I'd be grateful.

 

[Macropod]

Since you're using late binding, you should use:
Dim HdrRange As Object
Even then your use of wdHeaderFooterPrimary is likely to cause an error; you should use its numeric value (i.e. 1) instead.

And, even with early binding, you would not use:
Dim HdrRange As Range
as the unqualified Range reference tells Excel it's an Excel range.

 

[Devagorgon]

Thank you, this works now.

Two last questions:
1: Is there anywhere on the net, where I can look up these numeric values or is it just try and error?
2: This may be better suited for an new thread, but maybe you know that, too...
When I try to Paste an Excel diagram into Word, I cannot use the RTF:=True Part. Thus, the Diagramm will have a big left indent and a smaller right indent on the page. Do you know of a way to shift it afterwards or scale it automatically while pasting?

The second question I may circumvent by changing the diagram dimensions in Excel, so it's just a "nice to know" issue.

Thanks again. I was really in a bind there.

 

[Macropod]

Re 1: You don't need to look on the Net; Although the details are available on MSDN, you can also simply look in the Word VBA help file. There you can find the enumerations of all Word constants, just as for can do for Excel constants in the Excel VBA help file.

Re 2: Your latest comment suggests what you're copying/pasting is not a table, as such, but a chart. Correct? If so, you should be copying the chart, not the range it spans, then pasting it into Word as an Office Graphic Object. The code for the paste would be:
HdrRange.PasteSpecial False, 23, 0, False

 

[Devagorgon]

@2: Yes, it's a chart (I forgot the english name...). I didn't really try the Pastespecial, because
https://docs.microsoft.com/de-de/office/vba/api/word.range.pastespecial
gave me no descrition for the keywords. I will try that.

@1:The problem is, I cannot find a help file. All I get, when selecting "?"->"Microsoft Visual Basic fpr Applications-Help" is the following website
https://docs.microsoft.com/de-de/office/client-developer/excel/excel-home
and you cannot search for the constants names (like "WdOLEPlacement") there (the description and handling is crappy as hell), though I have just now found out, that you can search for "constants" and then select "WdOLEPlacement" manually from a list.

(It's office 16, by the way.)

Well, thanks again. I think, I can manage most of it by now. (I should have thought about not just searching the net, but posting in a forum myself, sooner.)

 

[Macropod]

@1:The problem is, I cannot find a help file. All I get, when selecting "?"->"Microsoft Visual Basic fpr Applications-Help" is the following website

If, for example, you open Word's VBE and paste:
Sections(1).Headers(wdHeaderFooterPrimary)
into a code module, then select Headers and press F1, the VBA help file should open with the Section.Headers Property help displayed. Clicking on the HeadersFooters link will take you to another page where you'll find that the possible headers & footers have a WdHeaderFooterIndex constant, which is what you're actually after. Input that into the search box and you'll see a link to WdHeaderFooterIndex Enumeration, which will give you the value that relates to each constant.

 

[Devagorgon]

I'll try that.

Another question that has occured:
Since I have a picture inside the header tables, I am using
AppWdDoc.Sections(1).Headers(2).Range.PasteSpecial Link:=False, Placement:=1, DisplayAsIcon:=False, DataType:=9
and
AppWdDoc.Sections(1).Headers(1).Range.PasteSpecial Link:=False, Placement:=1, DisplayAsIcon:=False, DataType:=9
where
Dim AppWord As Object
Set AppWord = CreateObject("Word.Application")
Dim AppWdDoc As Object
Set AppWdDoc = AppWord.Documents.Add
to Paste my content into the headers (for some reason, he will not post the header to the second page, unless it already exists, but the Footer, where I use PasteExcelTable works without fail).

Now each copied table has to have a page number in cell(3,6) of the copied table (in Excel I have it all on one page). Is there a way to insert the pagename later on (accessing said cell and then inserting the page variable) OR can I write anything in the excel file, that will be converted correctly upon pasting?

 

[Devagorgon]

Don't be surprised, if I won't answer anymore. I need to stop working on this and can resume by next Tuesday.

 

[Macropod]

each copied table has to have a page number in cell(3,6) of the copied table (in Excel I have it all on one page).

Presumably, for that you're using:
AppWdDoc.Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True
Since that creates a table in the Word document, you can indeed add the page # afterwards. If the page # is the Word document's page, that is best done by adding a PAGE field to the relevant cell, which avoids having to paste something different into each table and has the benefit of auto-updating if the page# gets changed for some reason.

 

[Devagorgon]

I'm back.

Well, using PasteExcelTabel was a bit troublesome, because I had to Post a picture of the Company Logo in the first cell.
I've now circumvented this by using first
PasteExcelTable and then
AppWdDoc.Sections(1).Headers(1).Range.PasteSpecial Link:=False, Placement:=1, DisplayAsIcon:=False, DataType:=9
to paste the Logo.

I've looked up, how to insert a PageNumber in Excel. It seems you have to either activate Footnotes directly in Exel (then you cannot do any complicated Table Stuff in the Header, so I am copying Text from Excel Body to Word Header and Footer)
or you have to insert in a cell using a VBA-Makro (I didn't try that, because I assume a fixed number is inserted and I want to copy different worksheets together and have the correct pagenumbers in Word).

That leaves me with the option of writing the page number after copying and with two issues:

Issue 1: How to write into my inserted Table? I will try some more, but things like
AppWdDoc.Sections(1).Headers(1).Range(Cells(1, 1), Cells(1, 1)).Text = "sdgsdgdhbs"
won't work after the PasteToExcel Command (After inserting the Pagenumbers, I will have to jump back to the first character and use the PasteSpecial to insert the picture, so I may need a command for that, too...).

Issue 2: How to insert the Page number via code? I've read, that you can enter it manually by pressing Control+F, then inserting Page into the resulting brackets and then pressing Alt+F9, but for that I need to emulate the button presses, thus I'm not sure this works.