Hi All
I need an Excel macro that can open header in Word. The macro I have now can open a Word document and copy some of the cells from Excel to Word. But I need that cells are paste into the header of the Word document. I have tried to record a macro in Word that open the header and then copy that into the Excel macro. But Excel don't accept that code.
Hope someone can help me.
Regards
Alring
Re: macro that open header
Hi,
This code should get you started. It creates an instance of Word and then adds a new document. Setting the header and footer text.[vba]Sub x()
Dim appWord As Word.Application
Dim docTemp As Word.Document
Dim hdrTemp As Word.HeaderFooter
Set appWord = CreateObject("Word.Application")
appWord.Visible = True
Set docTemp = appWord.Documents.Add
With docTemp.Sections(1)
.Headers(wdHeaderFooterPrimary).Range.Text = "Header"
.Footers(wdHeaderFooterPrimary).Range.Text = "Footer"
End With
End Sub[/vba]
Re: macro that open header
Hi Andy
Thank you for your reply. It seems that I can't use that. Meaning that I can't get it to work.
The macro I have now look like this.
[vba]Sub Button1_Click()
Range("A1:D4").Select
Selection.Copy
intPrintJobs = Range("A24")
aNameAndPath = Range("C26")
Range("A7").Select
Set appWD = CreateObject("Word.Application")
appWD.Visible = True
appWD.documents.Open Filename:=aNameAndPath
appWD.Selection.Paste
Application.CutCopyMode = False
appWD.ActiveDocument.PrintOut , Copies:=intPrintJobs
End Sub[/vba]
("A24") is the numbers of print I want.
("C26") is the document I want to open.
Range ("A1:D4") is what i want to copy into the header of the Word document.
I have tried what you said, but I get it to work. Maybe I write it wrong. maybe you can change my code so it works.
Regards
Mads
Re: macro that open header
This incorporates the copy and paste into my test example.[vba]Sub x()
Dim appWord As Word.Application
Dim docTemp As Word.Document
Dim hdrTemp As Word.HeaderFooter
Range("A1:D4").Copy
Set appWord = CreateObject("Word.Application")
appWord.Visible = True
Set docTemp = appWord.Documents.Add
With docTemp.Sections(1)
.Headers(wdHeaderFooterPrimary).Range.Paste
End With
End Sub[/vba]
Re: macro that open header
Hi Andy
There is something wrong here. Maybe it's me. The change for it is very BIG.
When I run your code in Excel then I get the message "User-defined type not defined". I get that in the line with "Dim appWord As Word.Application"
When I then delete the first 3 lines in your code then I get to the line with ".Headers(wdHeaderFooterPrimary).Range.Paste" and get a new message saying "The requested member of the collection does not exist".
Am I doing something wrong?? Again here is the change very BIG.
Alring
Re: macro that open header
I take it then you have not got a reference to the Word library in your excel project.
In VBE use the menus Tools > References. Check the Word object library.
If you intend to use late binding then replace the Word constants with values and declare the variables as Objects.[vba]Sub x()
Dim appWord As Object 'Word.Application
Dim docTemp As Object 'Word.Document
Dim hdrTemp As Object 'Word.HeaderFooter
Range("A1:D4").Select
Selection.Copy
Set appWord = CreateObject("Word.Application")
appWord.Visible = True
Set docTemp = appWord.Documents.Add
With docTemp.Sections(1)
.Headers(1).Range.Paste
End With
End Sub[/vba]
Re: macro that open header
Hi Andy
I knew that I was doing something wrong. Because now it works as it should.
I thank you so mush for your help.
:thanx:
Regards
Alring
Re: macro that open header
The only problem I have now is that the code paste the cells from Excel into the document that it's open and not to the document mensioned in cell ("C26").
Alring
Re: macro that open header
Have you incorporated my code into yours?
[vba] appwd.documents.Open Filename:=aNameAndPath
With appwd.documents(1).Sections(1)
.Headers(1).Range.Paste
End With[/vba]
Re: macro that open header
Hi Andy
Yes i did. But in a wrong way. With the last code change for you it works.
Ones again. Thank you so mush for your help. What would I do without you?? I know: Be very lost. :rock:
:thanx:
Alring
Don’t have an account yet? Register yourself now and be a part of our community!
